474 lines
17 KiB
C#
474 lines
17 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 sqcgDal
|
|
{
|
|
public List<sqcgModel> GetAllList(string id)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql = "select * from sqcg where 1=1";
|
|
if (!string.IsNullOrEmpty(id))
|
|
{
|
|
sql += " and id=@id";
|
|
}
|
|
return conn.Query<sqcgModel>(sql, new { @id = id }).ToList();
|
|
}
|
|
}
|
|
|
|
public List<sqcgModel> GetAllList(string id, string zt)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql = "select * from sqcg where 1=1";
|
|
if (!string.IsNullOrEmpty(id))
|
|
{
|
|
sql += " and id=@id";
|
|
}
|
|
if (!string.IsNullOrEmpty(zt))
|
|
{
|
|
sql += " and zt=@zt";
|
|
}
|
|
return conn.Query<sqcgModel>(sql, new { @id = id, @zt = zt }).ToList();
|
|
}
|
|
}
|
|
|
|
public object save(sqcgModel model)
|
|
{
|
|
string sql = "";
|
|
if (model.id == 0)
|
|
{
|
|
sql = @"INSERT INTO [dbo].[sqcg]
|
|
([sqdh]
|
|
,[sqsj]
|
|
,[sqrid]
|
|
,[sqr]
|
|
,[ksid]
|
|
,[ksmc]
|
|
,[je]
|
|
,[bz]
|
|
,[fyly]
|
|
,[fydh]
|
|
,[mx]
|
|
,[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].[sqcg]
|
|
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<sqcgModel> getListBydh(string dh)
|
|
{
|
|
string sql = "select * from sqcg where sqdh in (@dh)";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<sqcgModel>(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 bz)
|
|
{
|
|
string errmsg = "";
|
|
string sqdh = getckkdh(out errmsg);
|
|
if (errmsg != "")
|
|
return new { State = 0, Message = "false" };
|
|
sqcgModel model = new sqcgModel();
|
|
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.bz = bz;
|
|
model.fyly = Convert.ToInt32(fyly);
|
|
model.fydh = fydh;
|
|
model.zt = 1;
|
|
|
|
string sql = @"INSERT INTO [dbo].[sqcg]
|
|
([sqdh],[sqsj],[sqrid],[sqr],[ksid],[ksmc],[je],[bz],[fyly],[fydh],[zt])
|
|
VALUES
|
|
(@sqdh,@sqsj,@sqrid,@sqr,@ksid,@ksmc,@je,@bz,@fyly,@fydh,@zt)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<sqcgmxModel> models = new List<sqcgmxModel>();
|
|
sqcgmxModel m;
|
|
foreach (string item in items.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
|
|
{
|
|
string[] itemss = item.Split('|');
|
|
if (itemss.Length == 4)
|
|
{
|
|
m = new sqcgmxModel();
|
|
m.sqdid = sqdid;
|
|
m.title = itemss[0];
|
|
m.content = itemss[1];
|
|
m.sl = Convert.ToDecimal(itemss[2]);
|
|
m.je = Convert.ToDecimal(itemss[3]);
|
|
m.czsj = DateTime.Now;
|
|
models.Add(m);
|
|
}
|
|
}
|
|
|
|
string sql2 = @"INSERT INTO [dbo].[sqcgmx]
|
|
([sqdid],[title],[content],[sl],[je],[czsj])
|
|
VALUES
|
|
(@sqdid,@title,@content,@sl,@je,@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 object opSave2(string id, int ksid, string ksmc, string items, int sqrid, string sqr, string je, string fyly, string fydh, string bz)
|
|
{
|
|
sqcgModel model = new sqcgModel();
|
|
model.id = Convert.ToInt32(id);
|
|
model.je = Convert.ToDecimal(je);
|
|
model.bz = bz;
|
|
model.fyly = Convert.ToInt32(fyly);
|
|
model.fydh = fydh;
|
|
model.zt = 1;
|
|
|
|
string sql = @"UPDATE [dbo].[sqcg]
|
|
SET [je] = @je,[bz] = @bz,[fyly] = @fyly,[fydh] = @fydh,[zt] = @zt
|
|
WHERE id=@id";
|
|
|
|
string sql3 = "delete from sqcgmx 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 = "false" };
|
|
}
|
|
}
|
|
|
|
List<sqcgmxModel> models = new List<sqcgmxModel>();
|
|
sqcgmxModel m;
|
|
foreach (string item in items.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
|
|
{
|
|
string[] itemss = item.Split('|');
|
|
if (itemss.Length == 4)
|
|
{
|
|
m = new sqcgmxModel();
|
|
m.sqdid = Convert.ToInt32(id);
|
|
m.title = itemss[0];
|
|
m.content = itemss[1];
|
|
m.sl = Convert.ToDecimal(itemss[2]);
|
|
m.je = Convert.ToDecimal(itemss[3]);
|
|
m.czsj = DateTime.Now;
|
|
models.Add(m);
|
|
}
|
|
}
|
|
|
|
string sql2 = @"INSERT INTO [dbo].[sqcgmx]
|
|
([sqdid],[title],[content],[sl],[je],[czsj])
|
|
VALUES
|
|
(@sqdid,@title,@content,@sl,@je,@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 object opSp(string id, string spr, string yj, bool ty)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql2 = "select * from sqcg where 1=1";
|
|
if (!string.IsNullOrEmpty(id))
|
|
{
|
|
sql2 += " and id=@id";
|
|
}
|
|
sqcgModel model = conn.Query<sqcgModel>(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 sqcg set spr1=@spr,sptime1=@sptime,spnr1=@spnr,zt=@zt where id=@id";
|
|
if (ty) { zt = 2; } else { zt = -1; }
|
|
break;
|
|
case 2:
|
|
sql = "update sqcg set spr2=@spr,sptime2=@sptime,spnr2=@spnr,zt=@zt where id=@id";
|
|
if (ty) { zt = 3; } else { zt = -2; }
|
|
break;
|
|
case 3:
|
|
sql = "update sqcg set spr3=@spr,sptime3=@sptime,spnr3=@spnr,zt=@zt where id=@id";
|
|
if (ty) { zt = 4; } else { zt = -3; }
|
|
break;
|
|
case 4:
|
|
sql = "update sqcg set spr4=@spr,sptime4=@sptime,spnr4=@spnr,zt=@zt where id=@id";
|
|
if (ty) { zt = 5; } else { zt = -4; }
|
|
break;
|
|
}
|
|
|
|
try
|
|
{
|
|
int result = conn.Execute(sql, new { @id = id, @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 };
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
//获取申请单号
|
|
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 sqcg 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 sqcg where id=@id";
|
|
string sql2 = "delete from sqcgmx 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.sqcg 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<sqcgModel> getPage(int page, int pagesize, string key)
|
|
{
|
|
string sql = "select *,row_number() over(order by id desc) as rownum from sqcg 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.sqcgModel>(sql, new { key = key }).ToList();
|
|
}
|
|
}
|
|
|
|
public int getCountLevel(string where)
|
|
{
|
|
string sql = "select count(1) from dbo.sqcg";
|
|
if (!string.IsNullOrEmpty(where))
|
|
{
|
|
sql += " where " + where;
|
|
}
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql);
|
|
}
|
|
}
|
|
|
|
public List<sqcgModel> getPageLevel(int page, int pagesize, string where)
|
|
{
|
|
string sql = "select *,row_number() over(order by id desc) as rownum from sqcg";
|
|
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.sqcgModel>(sql).ToList();
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|