/// <summary>
/// 取证书编号
/// LB
/// M01 GPS
/// M02 全站仪
/// </summary>
/// <param name="LB"></param>
/// <returns></returns>
private string GetZSBH(string LB, double ID)
//public dynamic GetZSBH(string LB, double ID)
{
string strSQL1 = @"
Update YQSF_SJMX
set jdzt='在检'
where ID=" + ID;
_sjmxDapperRepository.Execute(strSQL1);
StringBuilder strSQL = new StringBuilder(100);
strSQL.Append("declare @SQL VARCHAR(255)");
strSQL.Append("\r\n");
strSQL.Append("DECLARE @Year decimal");
strSQL.Append("\r\n");
strSQL.Append("DECLARE @Zsbh VARCHAR(12)");
strSQL.Append("\r\n");
strSQL.Append("set @Year = (select LEFT(Right(MAX(zsbh), 8), 4) from YQSF_SJMX where zsbh like '" + LB + "-%')");
strSQL.Append("\r\n");
strSQL.Append("if (year(getdate()) > @Year) ");
strSQL.Append("\r\n");
strSQL.Append("set @Zsbh = '" + LB + "-" + DateTime.Now.Year + "0001'");
strSQL.Append("\r\n");
strSQL.Append("else");
strSQL.Append("\r\n");
strSQL.Append("set @Zsbh = '" + LB + "-'+str((select Right(MAX(zsbh),8)+1 from YQSF_SJMX where zsbh like '" + LB + "-%'),8,0)");
strSQL.Append("\r\n");
strSQL.Append("update yqsf_sjmx set zsbh=@Zsbh where ID=" + ID + " and len(zsbh)=0");
strSQL.Append("\r\n");
strSQL.Append("select zsbh from YQSF_SJMX where ID=" + ID);
return(_sjmxDapperRepository.Query(strSQL.ToString()).FirstOrDefault().ZSBH);
}
public int SetJDWB(int ID)
{
string strSQL = @"update SJCL_CHYQ set JWRQ = @JWRQ,JDZT = 122 where ID=@ID";
var param = new
{
ID = ID,
JWRQ = DateTime.Now
};
return(_zshDapperRepository.Execute(strSQL, param));
}
public bool Create(Usuario user)
{
try
{
user.Password = Criptografia.Codifica(user.Password);
return(dapperRepository.Execute(" Insert into Usuario (UserName, Password, DateBirth) values ( @UserName, @Password, @DateBirth)", user));
}
catch (Exception)
{
return(false);
}
}
// public static List<int> GetMonthCount(int year) -> StatsAppServices.cs
//重新检定
public int ResetJdzt(string id)
{
string strSQL = "update YQSF_SJD set jdzt='在检' where ID = (select sjdid from YQSF_SJMX as b where b.id = " + id + ")";
_sjmxDapperRepository.Execute(strSQL);
strSQL = "Update YQSF_SJMX set jdzt='登记' where id=" + id;
_sjmxDapperRepository.Execute(strSQL);
strSQL = "Update SJCL_CHYQ set JDRQ=null,jwrq=null,JDYID=null,hyy=null,HYYID=null,PZRID=null,HYYJ='',PZYJ='',PZR='',jdzt=100 where id=" + id;
_sjmxDapperRepository.Execute(strSQL);
CZRZDto czrz = new CZRZDto();
czrz.CZNR = "重新检定：" + id;
_czrzAppServices.AddtoCZRZ(czrz);
return(1);
}
public void DoSomeStuff()
{
var people = _personDapperRepository.Query("select * from AppPersons");
_personDapperRepository.Execute("Insert into AppPersons (Id,Name,CreationTime) values ('" + Guid.NewGuid() + "','Name_" + Guid.NewGuid().ToString().Substring(10) + "','" + Clock.Now.ToString() + "')");
_personDapperRepository.Execute("UPDATE AppPersons set Name=@name where Id=@Id", new { name = "Name_" + Guid.NewGuid().ToString().Substring(10), Id = people.FirstOrDefault()?.Id });
var person = people.FirstOrDefault();
person.Name = "Yebin_" + Guid.NewGuid().ToString().Substring(15);
_personDapperRepository.Update(person);
person = _personDapperRepository.Single(people.Skip(1).Take(1).First().Id);
_personDapperRepository.Insert(new Person {
Name = "Yebin_" + Guid.NewGuid().ToString().Substring(15), Id = Guid.NewGuid(), CreationTime = Clock.Now
});
_personDapperRepository.Delete(people.Skip(2).Take(1).First().Id);
}
public void SetReadLastNoticeTime()
{
if (string.IsNullOrWhiteSpace(AbpSession.UserId.ToString()))
{
return;
}
long userID = (long)AbpSession.UserId;
Expression <Func <AbpUsersEx, bool> > predicate = p => (p.Id == userID);
if (!_userRepository.Count(predicate).Equals(1))
{
_userRepository.Execute("insert into AbpUsersEx(id,ClientType,ReadLastNoticeTime) values(" + userID + ",'App','" + Clock.Now + "')");
}
else
{
_userRepository.Execute("update AbpUsersEx set ReadLastNoticeTime = '" + Clock.Now + "' where Id = " + userID);
}
}
public int Add(int id, string jdy) //string jdy = (Int32.Parse(context.Request.Form["jdy"].ToString())-100000).ToString();
{
int ret = 0;
string strSQL = "select top 1 ID from YQSF_DPII_JDRQ where ID=" + id;
if (_jdrqDapperRepository.Query(strSQL).Count() == 0) //确保不存在再 插入
{
using (var unitOfWork = _unitOfWorkManager.Begin())
{
strSQL = "insert into YQSF_DPII_JDRQ(id,jdrq,jdzt,jdy) values(" + id + ",'" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "'," + 100 + ",'" + jdy + "')";
_jdrqDapperRepository.Execute(strSQL);
strSQL = "update YQSF_SJD set qzyjdzt = '正在检定' where ID = (select sjdid from YQSF_SJMX as b where b.id = " + id + ")";
_jdrqDapperRepository.Execute(strSQL);
unitOfWork.Complete();
ret = 1;
}
}
return(ret);
}
/// <summary>
/// 保存
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public AjaxResponse SaveFormSqlModel(dynamic model)
{
string sqlStr = BuildSql(model);
var row = _dapperRepositoryTableInfo.Execute(sqlStr);
return(new AjaxResponse {
Success = true
});
}
public int AdicionarPessoa(Pessoa pessoa)
{
var sql = @"
INSERT INTO [dbo].[Pessoa]
([Nome]
,[CpfCnpj]
,[Email])
VALUES
(@Nome
,@CpfCnpj
,@Email)" ;
return(_dapperRepository.Execute(sql, new
{
pessoa.Nome,
pessoa.CpfCnpj,
pessoa.Email
}));
}
public Task <UpdateObjectCategoryResponse> UpdateCategoryAsync(UpdateObjectCategory dto)
{
return(Task.Run(() =>
{
switch (dto.type)
{
case Contact.Enum.UpdateCategoryTypeEnum.Works:
var worksEntity = _worksRepository.TableNoTracking.Where(t => t.Id.Equals(dto.object_id)).SingleOrDefault();
if (worksEntity == null)
{
throw new RequestErrorException("该作品不存在!");
}
break;
case Contact.Enum.UpdateCategoryTypeEnum.Product:
var productEntity = _pruoductRepository.TableNoTracking.Where(t => t.Id.Equals(dto.object_id)).SingleOrDefault();
if (productEntity == null)
{
throw new RequestErrorException("该产品不存在!");
}
break;
case Contact.Enum.UpdateCategoryTypeEnum.News:
var newsEntity = _newsRepository.TableNoTracking.Where(t => t.Id.Equals(dto.object_id)).SingleOrDefault();
if (newsEntity == null)
{
throw new RequestErrorException("该新闻不存在!");
}
break;
default:
throw new RequestErrorException("更新的资源类型不匹配");
break;
}
var id = dto.object_id.ToString("D");
var deleteCount = _dapperRepository.Execute(DELETE_CATEGORY_RELATIONSHIPS, new { objectId = id });
var idList = dto.categorie_ids.Split(',');
List <CategoryRelationshipsEntity> relaList = new List <CategoryRelationshipsEntity>();
for (int i = 0; i < idList.Length; i++)
{
relaList.Add(new CategoryRelationshipsEntity()
{
CategoryId = Convert.ToInt32(idList[i]),
CreatedDate = DateTime.Now,
ObjectId = dto.object_id
});
}
_categoryRelationshipsRepository.Insert(relaList);
return new UpdateObjectCategoryResponse();
}));
}
public void AddtoCZRZ(CZRZDto czrz)
{
long jdyid = (long)AbpSession.UserId;
var param = new
{
userid = jdyid,
cznr = czrz.CZNR,
bzsm = czrz.BZSM
};
string strSQL = @"insert into SYS_CZRZ(userid,cznr,bzsm) values(@userid,@cznr,@bzsm)";
int ret = _czrzDapperRepository.Execute(strSQL, param);
}
private void SetLastLoginTime()
{
/*
* // Add LastLoginTime
* // 还需要重写 GetAll
* //var user = UserManager.FindByIdAsync(AbpSession.UserId.ToString());
* var builder = new DbContextOptionsBuilder<SMICDbContext>();
* var configuration = AppConfigurations.Get(WebContentDirectoryFinder.CalculateContentRootFolder());
* //SMICDbContextConfigurer.Configure(builder, configuration.GetConnectionString(SMICConsts.ConnectionStringName));
* SMICDbContextConfigurer.Configure(builder, "Server=DEEP-1704241155; Database=SMICDbVue3; Trusted_Connection=True;"); //
* var context = new SMICDbContext(builder.Options);
* SqlParameter[] parameters = new[]{
* new SqlParameter("Id", AbpSession.UserId ),
* new SqlParameter("LastLoginTime", Clock.Now)
* };
* context.Database.ExecuteSqlCommand("update AbpUsers set LastLoginTime=@LastLoginTime where Id=@Id", parameters);
* //Logger.Info(user.ToString());
*/
if (!string.IsNullOrWhiteSpace(AbpSession.UserId.ToString()))
{
_memberDapperRepository.Execute("update AbpUsers set LastLoginTime = '" + Clock.Now + "' where Id = " + AbpSession.UserId);
}
}
public void DoSomeStuff()
{
using (IUnitOfWorkCompleteHandle uow = UnitOfWorkManager.Begin())
{
using (StoveSession.Use(266))
{
_productDapperRepository.Insert(new Product("TShirt"));
int gomlekId = _productDapperRepository.InsertAndGetId(new Product("Gomlek"));
Product gomlekFromEf = _productRepository.FirstOrDefault(gomlekId);
_productDapperRepository.Execute("update products set Name = @name where Id = @id", new { name = "TShirt_From_ExecuteQuery", @id = gomlekId });
Product firstProduct = _productDapperRepository.Get(gomlekId);
_animalRepository.InsertAndGetId(new Animal("Bird"));
_animalDapperRepository.GetAll(x => x.Name == "Bird");
_productDapperRepository.GetAll(x => x.Id == 1 || x.Name == "Gomlek" || x.CreationTime == DateTime.Now);
_productDapperRepository.GetAll(x => (x.Id == 1 && x.Name == "Gomlek") || x.CreationTime == DateTime.Now);
_productDapperRepository.GetAll(x => ((x.Id == 1 || x.Name == "Gomlek") && x.CreationTime == DateTime.Now) ||
(x.Id == 1 || x.Name == "Gomlek") && x.CreationTime == DateTime.Now);
IEnumerable <Product> products = _productDapperRepository.GetAll();
firstProduct.Name = "Something";
_productDapperRepository.Update(firstProduct);
_mailDapperRepository.Insert(new Mail("New Product Added"));
Guid mailId = _mailDapperRepository.InsertAndGetId(new Mail("Second Product Added"));
IEnumerable <Mail> mails = _mailDapperRepository.GetAll();
Mail firstMail = mails.First();
firstMail.Subject = "Sorry wrong email!";
_mailDapperRepository.Update(firstMail);
}
uow.Complete();
}
}
public int AddJbcs(AddJbcsDto jbcs)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into JCXX_QZY_JDZB(");
strSql.Append("xhggbm,bcjda,bcjdb,cjjd,bcfw,axles");
strSql.Append(")");
strSql.Append(" values (");
strSql.Append(jbcs.ID + ",");
strSql.Append(jbcs.BCJDA + ",");
strSql.Append(jbcs.BCJDB + ",");
strSql.Append(jbcs.CJJD + ",");
strSql.Append(jbcs.BCFW + ",");
strSql.Append(jbcs.Axles);
strSql.Append(")");
//strSql.Append(";select @@IDENTITY");
return(_jbcsDapperRepository.Execute(strSql.ToString()));
}
public void Execute(string sql, object param = null)
{
_dapperRepository.Execute(sql, param);
}
/*
* 检定状态：
* 100 登记
* 111 在检 JDYID
* 122 检完确认 -> 待核验
* 200 核验完毕 -> 生成证书，待批准
* 222 批准完毕 -> 证书签名，检完
*/
public double[] GetHomeCountData() // 待处理总任务、待检、在检、待核验、待批准
{
if (!AbpSession.UserId.HasValue)
{
return new double[] { }
}
;
long id = (long)AbpSession.UserId;
string roles = GetJDRoles(id);
string strSQL = @"
UPDATE YQSF_SJD
SET yqjs = (
SELECT COUNT(*) FROM YQSF_SJMX WHERE SJDID = YQSF_SJD.id
)";
try
{
_cntDapperRepository.Execute(strSQL); // 超时
}
catch { }
//System.Diagnostics.Debug.WriteLine(id);
//System.Diagnostics.Debug.WriteLine(roles);
/*
* //int[] ints = new int[] { 1000, 1030 };
* string[] ints = new string[] { "1000", "1030" };
* //Expression<Func<VW_SJCL_100, bool>> predicate = p => p.JDZT == 100 && p.QJMCBM.IsIn(ints); //The method 'p.QJMCBM.IsIn(value(System.Int32[]))' is not supported
* Expression<Func<VW_SJCL_100, bool>> p1 = p => p.QJMCBM.Equals("1000");
* Expression<Func<VW_SJCL_100, bool>> p2 = p => p.QJMCBM.Equals("1030");
* Expression<Func<VW_SJCL_100, bool>> predicate = p1.Or(p2);
*
* //if(ints.Length>0)
* //predicate = predicate.And(p => p.QJMCBM == "1000"); //ints[0]
* //predicate = predicate.Or(p => p.QJMCBM.Equals("1030")); //ints[0]
* //for (int i=1;i<ints.Length;i++)
* //predicate = predicate.Or(p => p.QJMCBM == 1030);
* var totalCount = _vwsjclDapperRepository.Count(predicate); // 应该=8 实际=0 ！！！
* System.Diagnostics.Debug.WriteLine(totalCount);
*
*
* //predicate = p => p.JDZT == 100;
* //totalCount = _vwsjclDapperRepository.Count(predicate);
*
* // int 的 Equals 不能用！！！ 改为 string OK
* // 2019-12-11 Abp.Dapper 4.8.1 无效 升级到 4.9 OK
*
* Expression<Func<VW_SJCL_100, bool>> newExp;
* if (ints.Length > 0)
* {
* ParameterExpression parameter = Expression.Parameter(typeof(VW_SJCL_100), "p");
* MemberExpression member = Expression.PropertyOrField(parameter, "QJMCBM");
* MethodInfo method = typeof(string).GetMethod("Equals", new[] { typeof(string) });
* ConstantExpression constant = Expression.Constant(ints[0], typeof(string));
* newExp = Expression.Lambda<Func<VW_SJCL_100, bool>>(Expression.Call(member, method, constant), parameter);
* for (int i = 1; i < ints.Length; i++) {
* newExp = newExp.Or(Expression.Lambda<Func<VW_SJCL_100, bool>>(Expression.Call(member, method, Expression.Constant(ints[i], typeof(string))), parameter));
* }
* newExp = newExp.And(p => p.JDZT == 100);
* }
* else
* newExp = p => p.JDZT == 100;
*/
/*
* // 先 and 再 or
* Expression<Func<VW_SJCL_100, bool>> newExp = p => p.JDZT == 100;
* if (ints.Length > 0)
* {
* ParameterExpression parameter = Expression.Parameter(typeof(VW_SJCL_100), "p");
* MemberExpression member = Expression.PropertyOrField(parameter, "QJMCBM");
* MethodInfo method = typeof(string).GetMethod("Equals", new[] { typeof(string) });
* ConstantExpression constant = Expression.Constant(ints[0], typeof(string));
* newExp = newExp.And(Expression.Lambda<Func<VW_SJCL_100, bool>>(Expression.Call(member, method, constant), parameter));
* for (int i = 1; i < ints.Length; i++)
* {
* newExp = newExp.Or(Expression.Lambda<Func<VW_SJCL_100, bool>>(Expression.Call(member, method, Expression.Constant(ints[i], typeof(string))), parameter));
* }
* }
* var totalCount2 = _vwsjclDapperRepository.Count(newExp);
* System.Diagnostics.Debug.WriteLine(totalCount2);
*/
double cnt1 = 0;
double cnt2 = 0;
double cnt3 = 0;
double cnt4 = 0;
double cnt5 = 0;
double cnt6 = 0;
double cnt7 = 0; // 我的待检
double cnt8 = 0; // 我的待核验
// 字符串拼接
// string userIds = "1,2,3,4";
// strSQL = string.Format("select * from Users(nolock) where UserID in({0})", userIds);
//string strSQL = @"select count(0) as count from VW_SJCL_100 where jdzt = 122 and qjmcbm in (@roles) and jdyid !=@jdyid";
//var param = new
//{
// roles = roles,
//};
// cnt7 = _cntDapperRepository.Query(strSQL, param).FirstOrDefault().count; // where in 不能用此法
// 参数化执行 where in
string strSQL7 = @"
declare @Temp_Variable varchar(max)
create table #Temp_Table(Item varchar(max))
while(LEN(@Temp_Array) > 0)
begin
if(CHARINDEX(',',@Temp_Array) = 0)
begin
set @Temp_Variable = @Temp_Array
set @Temp_Array = ''
end
else
begin
set @Temp_Variable = LEFT(@Temp_Array,CHARINDEX(',',@Temp_Array)-1)
set @Temp_Array = RIGHT(@Temp_Array,LEN(@Temp_Array)-LEN(@Temp_Variable)-1)
end
insert into #Temp_Table(Item) values(@Temp_Variable)
end
select count(0) as count from VW_SJCL_100(nolock) where exists(select 1 from #Temp_Table(nolock) where VW_SJCL_100.jdzt<122 and #Temp_Table.Item=VW_SJCL_100.qjmcbm)
drop table #Temp_Table";
string strSQL8 = @"
declare @Temp_Variable varchar(max)
create table #Temp_Table(Item varchar(max))
while(LEN(@Temp_Array) > 0)
begin
if(CHARINDEX(',',@Temp_Array) = 0)
begin
set @Temp_Variable = @Temp_Array
set @Temp_Array = ''
end
else
begin
set @Temp_Variable = LEFT(@Temp_Array,CHARINDEX(',',@Temp_Array)-1)
set @Temp_Array = RIGHT(@Temp_Array,LEN(@Temp_Array)-LEN(@Temp_Variable)-1)
end
insert into #Temp_Table(Item) values(@Temp_Variable)
end
select count(0) as count from VW_SJCL_100(nolock) where exists(select 1 from #Temp_Table(nolock) where VW_SJCL_100.jdzt=@JDZT and VW_SJCL_100.jdyid!=@JDYID and #Temp_Table.Item=VW_SJCL_100.qjmcbm)
drop table #Temp_Table";
try
{
var param7 = new
{
Temp_Array = roles
};
var param8 = new
{
Temp_Array = roles,
JDZT = 122,
JDYID = id
};
cnt7 = _cntDapperRepository.Query(strSQL7, param7).FirstOrDefault().count; //我的待检
cnt8 = _cntDapperRepository.Query(strSQL8, param8).FirstOrDefault().count; //我的待核验
cnt1 = _cntDapperRepository.Query("select count(0) as count from VW_SJCL_100 where jdzt = 100").FirstOrDefault().count; //所有待检
cnt2 = _cntDapperRepository.Query("select count(0) as count from VW_SJCL_100 where jdzt = 111").FirstOrDefault().count; //所有在检
cnt3 = _cntDapperRepository.Query("select count(0) as count from VW_SJCL_100 where jdzt = 122").FirstOrDefault().count; //所有待核验
cnt4 = _cntDapperRepository.Query("select count(0) as count from VW_SJCL_100 where jdzt = 200").FirstOrDefault().count; //所有待批准
cnt5 = _cntDapperRepository.Query("select count(0) as count from VW_SJMX where year([送检日期])= year(getdate())").FirstOrDefault().count; //今年
cnt6 = _cntDapperRepository.Query("select count(0) as count from VW_SJMX where year([送检日期])= year(getdate())-1").FirstOrDefault().count; //去年
}
catch
{
return(new double[] { });
}
double[] Datas = new double[] { cnt1 + cnt2 + cnt3 + cnt4, cnt1, cnt2, cnt3, cnt4, cnt5, cnt6, cnt7, cnt8 };
return(Datas);
}