/// <summary>
/// 获取正式知识库的列表信息
/// </summary>
/// <param name="searchParam">搜索条件</param>
/// <param name="para">分页信息</param>
/// <returns></returns>
public JsonResultModel <V_Business_KnowledgeBase_Information> GetKnowledgeListBySearch(Business_KnowledgeBase_Information searchParam, GridParams para)
{
using (var db = SugarDao_MsSql.GetInstance())
{
JsonResultModel <V_Business_KnowledgeBase_Information> jsonResult = new JsonResultModel <V_Business_KnowledgeBase_Information>();
var query = db.Queryable <V_Business_KnowledgeBase_Information>().Where(i => i.Status == "2");
if (!string.IsNullOrEmpty(searchParam.Title))
{
query.Where(i => i.Title.Contains(searchParam.Title));
}
if (!string.IsNullOrEmpty(searchParam.Remark))
{
query.Where(i => i.Remark.Contains(searchParam.Remark));
}
if (!string.IsNullOrEmpty(searchParam.Type))
{
query.Where(i => i.Type.Contains(searchParam.Type));
}
if (searchParam.CreatedDate != DateTime.MinValue)
{
query.Where(i => i.CreatedDate >= searchParam.CreatedDate);
}
query.OrderBy(para.sortdatafield + " " + para.sortorder);
jsonResult.TotalRows = query.Count();
jsonResult.Rows = query.ToPageList(para.pagenum, para.pagesize);
//存入操作日志表
string logData = JsonHelper.ModelToJson(jsonResult);
_ll.SaveLog(3, 43, CurrentUser.GetCurrentUser().LoginName, "正式列表", logData);
return(jsonResult);
}
}
/// <summary>
/// 保存部门信息
/// </summary>
/// <param name="organizationModel"></param>
/// <param name="isEdit"></param>
/// <returns></returns>
public bool Save(Master_Organization organizationModel, bool isEdit)
{
using (SqlSugarClient dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
{
bool result = false;
if (isEdit)//编辑
{
var model = new
{
OrganizationName = organizationModel.OrganizationName,
//OrganizationCode = organizationModel.OrganizationCode,
Description = organizationModel.Description,
ChangeDate = organizationModel.ChangeDate,
ChangeUser = organizationModel.ChangeUser
};
result = dbMsSql.Update <Master_Organization>(model, i => i.Vguid == organizationModel.Vguid);
//存入操作日志表
string logData = JsonHelper.ModelToJson(organizationModel);
_ll.SaveLog(4, 3, CurrentUser.GetCurrentUser().LoginName, organizationModel.OrganizationName, logData);
}
else//新增
{
result = dbMsSql.Insert(organizationModel, false) != DBNull.Value;
//存入操作日志表
string logData = JsonHelper.ModelToJson(organizationModel);
_ll.SaveLog(1, 2, CurrentUser.GetCurrentUser().LoginName, organizationModel.OrganizationName, logData);
}
return(result);
}
}
/// <summary>
/// 更新支付历史
/// </summary>
/// <param name="paymentHistoryInfo">实体信息</param>
/// <returns>返回成功与否</returns>
public bool UpdatePaymentHistory(Business_Personnel_Information personInfo, Business_PaymentHistory_Information paymentHistoryInfo, Business_PaymentHistory_Information paymentHistoryInfoOld)
{
using (var db = SugarDao_MsSql.GetInstance())
{
db.BeginTran();
try
{
var configInfo = db.Queryable <Master_Configuration>().Where(i => i.ID == 15).SingleOrDefault();
paymentHistoryInfo.CreateDate = paymentHistoryInfoOld.CreateDate;
paymentHistoryInfo.CreateUser = paymentHistoryInfoOld.CreateUser;
paymentHistoryInfo.ChangeDate = DateTime.Now;
paymentHistoryInfo.ChangeUser = "******";
//插入操作日志表中
string logData = JsonHelper.ModelToJson(paymentHistoryInfo);
_logLogic.SaveLog(18, 46, personInfo.UserID + " " + personInfo.Name, "大众出租租赁营收费用", logData);
int revenueStauts = 2;
if (paymentHistoryInfo.PaymentStatus == "1")
{
paymentHistoryInfo.PaymentAmount = paymentHistoryInfoOld.PaymentAmount;
paymentHistoryInfo.RevenueReceivable = paymentHistoryInfoOld.RevenueReceivable;
}
paymentHistoryInfo.RevenueStatus = revenueStauts;
db.DisableUpdateColumns = new[] { "RevenueType", "WeChatPush_VGUID" };
db.Update <Business_PaymentHistory_Information>(paymentHistoryInfo, i => i.Remarks == paymentHistoryInfoOld.Remarks);
db.CommitTran();
return(true);
}
catch (Exception ex)
{
LogHelper.WriteLog(ex.ToString());
db.RollbackTran();
return(false);
}
}
}
/// <summary>
/// 分页查询已审核习题信息列表
/// </summary>
/// <param name="searchParam"></param>
/// <param name="para"></param>
/// <returns></returns>
public JsonResultModel <V_Business_Exercises_Infomation> GetExerciseListBySearch(Business_Exercises_Infomation_Search searchParam, GridParams para)
{
using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
{
JsonResultModel <V_Business_Exercises_Infomation> jsonResult = new JsonResultModel <V_Business_Exercises_Infomation>();
//只查询三个月内的数据
// DateTime endDate = DateTime.Now.AddMonths(-3);
var query = _dbMsSql.Queryable <V_Business_Exercises_Infomation>().Where(i => i.Status == 2);
if (!string.IsNullOrEmpty(searchParam.ExercisesName))
{
query.Where(i => i.ExercisesName.Contains(searchParam.ExercisesName));
}
if (!string.IsNullOrEmpty(searchParam.InputType))
{
int inputType = int.Parse(searchParam.InputType);
query.Where(i => i.InputType == inputType);
}
if (!string.IsNullOrEmpty(searchParam.EffectiveDate))
{
DateTime effectiveDate = DateTime.Parse(searchParam.EffectiveDate);
query.Where(i => i.EffectiveDate < effectiveDate);
}
//if (string.IsNullOrEmpty(searchParam.CreatedTimeStart) && string.IsNullOrEmpty(searchParam.CreatedTimeEnd))
//{
// query.Where(i => i.CreatedDate > endDate);
//}
if (!string.IsNullOrEmpty(searchParam.CreatedTimeStart) && !string.IsNullOrEmpty(searchParam.CreatedTimeEnd))
{
DateTime createdTimeStart = DateTime.Parse(searchParam.CreatedTimeStart);
DateTime createdTimeEnd = DateTime.Parse(searchParam.CreatedTimeEnd);
query.Where(i => i.CreatedDate > createdTimeStart && i.CreatedDate < createdTimeEnd); // && i.CreatedDate > endDate
}
else
{
if (!string.IsNullOrEmpty(searchParam.CreatedTimeStart))
{
DateTime createdTimeStart = DateTime.Parse(searchParam.CreatedTimeStart);
query.Where(i => i.CreatedDate > createdTimeStart); //&& i.CreatedDate > endDate
}
if (!string.IsNullOrEmpty(searchParam.CreatedTimeEnd))
{
DateTime createdTimeEnd = DateTime.Parse(searchParam.CreatedTimeEnd);
query.Where(i => i.CreatedDate < createdTimeEnd); //&& i.CreatedDate > endDate
}
}
query.OrderBy(para.sortdatafield + " " + para.sortorder);
jsonResult.TotalRows = query.Count();
jsonResult.Rows = query.ToPageList(para.pagenum, para.pagesize);
//存入操作日志表
string logData = JsonHelper.ModelToJson <JsonResultModel <V_Business_Exercises_Infomation> >(jsonResult);
_ll.SaveLog(3, 11, Common.CurrentUser.GetCurrentUser().LoginName, "已审核习题列表", logData);
return(jsonResult);
}
}
/// <summary>
/// 获取二维码的配置(系统)
/// </summary>
/// <returns></returns>
public List <Master_Configuration> GetSysConfigurations()
{
using (var db = SugarDao_MsSql.GetInstance())
{
var configurations = db.Queryable <Master_Configuration>().Where(i => i.CreateUser == "QRCode" && i.ModifyUser == null).OrderBy(i => i.ID).ToList();
var logData = JsonHelper.ModelToJson(configurations);
_logLogic.SaveLog(3, 35, CurrentUser.GetCurrentUser().LoginName, "二维码的配置", logData);
return(configurations);
}
}
/// <summary>
/// 分页查询问卷信息列表
/// </summary>
/// <param name="searchParam"></param>
/// <param name="para"></param>
/// <returns></returns>
public JsonResultModel <V_Business_Questionnaire> GetQuestionListBySearch(Business_Questionnaire_Search searchParam, GridParams para)
{
using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
{
JsonResultModel <V_Business_Questionnaire> jsonResult = new JsonResultModel <V_Business_Questionnaire>();
var query = _dbMsSql.Queryable <V_Business_Questionnaire>().Where(i => i.Status == "1");
if (!string.IsNullOrEmpty(searchParam.QuestionName))
{
query.Where(i => i.QuestionnaireName.Contains(searchParam.QuestionName));
}
if (!string.IsNullOrEmpty(searchParam.EffectiveDate))
{
DateTime effectiveDate = DateTime.Parse(searchParam.EffectiveDate);
query.Where(i => i.EffectiveDate < effectiveDate);
}
query.OrderBy(para.sortdatafield + " " + para.sortorder);
jsonResult.TotalRows = query.Count();
jsonResult.Rows = query.ToPageList(para.pagenum, para.pagesize);
//存入操作日志表
string logData = JsonHelper.ModelToJson <JsonResultModel <V_Business_Questionnaire> >(jsonResult);
_ll.SaveLog(3, 52, Common.CurrentUser.GetCurrentUser().LoginName, "问卷列表", logData);
return(jsonResult);
}
}
public JsonResultModel <v_Business_ExercisesLibrary_Infomation> GetCheckedExerciseListBySearch(Business_Exercises_Infomation_Search searchParam, GridParams para)
{
using (SqlSugarClient db = SugarDao_MsSql.GetInstance())
{
JsonResultModel <v_Business_ExercisesLibrary_Infomation> jsonResult = new JsonResultModel <v_Business_ExercisesLibrary_Infomation>();
var query = db.Queryable <v_Business_ExercisesLibrary_Infomation>().Where(i => i.Status == 2);
if (!string.IsNullOrEmpty(searchParam.ExerciseName))
{
query.Where(i => i.ExerciseName.Contains(searchParam.ExerciseName));
}
if (!string.IsNullOrEmpty(searchParam.InputType))
{
int inputType = int.Parse(searchParam.InputType);
query.Where(i => i.InputType == inputType);
}
if (!string.IsNullOrEmpty(searchParam.ExerciseType))
{
int exerciseType = int.Parse(searchParam.ExerciseType);
query.Where(i => i.ExerciseType == exerciseType);
}
if (!string.IsNullOrEmpty(searchParam.CreatedTimeStart) && !string.IsNullOrEmpty(searchParam.CreatedTimeEnd))
{
DateTime createdTimeStart = DateTime.Parse(searchParam.CreatedTimeStart);
DateTime createdTimeEnd = DateTime.Parse(searchParam.CreatedTimeEnd);
query.Where(i => i.CreatedDate > createdTimeStart && i.CreatedDate < createdTimeEnd);
}
else
{
if (!string.IsNullOrEmpty(searchParam.CreatedTimeStart))
{
DateTime createdTimeStart = DateTime.Parse(searchParam.CreatedTimeStart);
query.Where(i => i.CreatedDate > createdTimeStart);
}
if (!string.IsNullOrEmpty(searchParam.CreatedTimeEnd))
{
DateTime createdTimeEnd = DateTime.Parse(searchParam.CreatedTimeEnd);
query.Where(i => i.CreatedDate < createdTimeEnd);
}
}
query.OrderBy(para.sortdatafield + " " + para.sortorder);
var pageCount = 0;
jsonResult.Rows = query.ToPageList(para.pagenum, para.pagesize, ref pageCount);
jsonResult.TotalRows = pageCount;
//存入操作日志表
string logData = JsonHelper.ModelToJson(jsonResult);
_LogLogic.SaveLog(3, 39, CurrentUser.GetCurrentUser().LoginName, "正式习题列表", logData);
return(jsonResult);
}
}
/// <summary>
/// 通过查询条件获取推送信息列表
/// </summary>
/// <param name="searchParam"></param>
/// <param name="para"></param>
/// <returns></returns>
public JsonResultModel <V_User_Information> GetSysUserListBySearch(SearchSysUserList searchParam, GridParams para)
{
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance())
{
JsonResultModel <V_User_Information> jsonResult = new JsonResultModel <V_User_Information>();
Guid vguid = Guid.Parse(Common.Tools.MasterVGUID.SysAdmin);
var query = _dbMsSql.Queryable <V_User_Information>().Where(i => i.Vguid != vguid);
if (!string.IsNullOrEmpty(searchParam.LoginName))
{
query.Where(i => i.LoginName.Contains(searchParam.LoginName));
}
if (!string.IsNullOrEmpty(searchParam.Role))
{
query.Where(i => i.Role == searchParam.Role);
}
if (!string.IsNullOrEmpty(searchParam.Department))
{
query.Where(i => i.TranslationDepartment.Contains(searchParam.Department));
}
query.OrderBy(para.sortdatafield + " " + para.sortorder);
jsonResult.TotalRows = query.Count();
jsonResult.Rows = query.ToPageList(para.pagenum, para.pagesize);
//存入操作日志表
string logData = JsonHelper.ModelToJson(jsonResult);
_logLogic.SaveLog(3, 26, Common.CurrentUser.GetCurrentUser().LoginName, "用户管理列表", logData);
return(jsonResult);
}
}
/// <summary>
/// 导出
/// </summary>
/// <param name="exerciseVguid">习题Vguid</param>
/// <param name="exportType">导出类型</param>
/// <param name="departmentVguid">部门vguid</param>
public void Export(string exerciseVguid, string exportType, string departmentVguid)
{
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance())
{
string sql = string.Format(@"EXEC usp_ExercisesAnswerUser @ExercisesVGUID,@Type,@DepartmentVGUID");
DataTable dt = new DataTable();
string departVguid = string.Empty;
if (CurrentUser.GetCurrentUser().LoginName == "sysAdmin")
{
departVguid = string.IsNullOrEmpty(departmentVguid) ? "" : departmentVguid;
}
else
{
departVguid = CurrentUser.GetCurrentUser().Department;
if (!string.IsNullOrEmpty(departmentVguid))
{
Guid dep = Guid.Parse(departVguid);
var listDep = _dbMsSql.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + dep + "')"); //找到该部门以及其所有子部门
Guid depVguid = Guid.Parse(departmentVguid);
departVguid = listDep.Contains(depVguid) ? departmentVguid : departVguid;
}
}
dt = _dbMsSql.GetDataTable(sql, new
{
ExercisesVGUID = exerciseVguid,
Type = exportType,
DepartmentVGUID = departVguid
});
dt.TableName = "table";
DataView dv = dt.DefaultView;
dv.Sort = "Department";
dt = dv.ToTable();
//因为导出模板不一样（exportType为3/4/5的是一个模板,其他的是一个模板）
if (exportType == "3" || exportType == "4" || exportType == "5")
{
string amountFileName = SyntacticSugar.ConfigSugar.GetAppString("ScoreTemplate");
Common.ExportExcel.ExportExcels("ScoreTemplate.xlsx", amountFileName, dt);
_logLogic.SaveLog(13, 22, Common.CurrentUser.GetCurrentUser().LoginName, "ScoreTemplate", Common.Tools.DataTableHelper.Dtb2Json(dt));
}
else
{
string amountFileName = SyntacticSugar.ConfigSugar.GetAppString("PersonReportTemplate");
Common.ExportExcel.ExportExcels("ReprotPersonTemplate.xlsx", amountFileName, dt);
_logLogic.SaveLog(13, 22, Common.CurrentUser.GetCurrentUser().LoginName, "PersonReportTemplate", Common.Tools.DataTableHelper.Dtb2Json(dt));
}
}
}
/// <summary>
/// 通过vguid获取推送主表信息
/// </summary>
/// <param name="vguid"></param>
/// <returns></returns>
public Business_WeChatPush_Information GetWeChatMainByVguid(string vguid)
{
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance())
{
Guid Vguid = Guid.Parse(vguid);
Business_WeChatPush_Information weChatMsgMain = new Business_WeChatPush_Information();
weChatMsgMain = _dbMsSql.Queryable <Business_WeChatPush_Information>().Where(i => i.VGUID == Vguid).SingleOrDefault();
//存入操作日志表
string logData = JsonHelper.ModelToJson <Business_WeChatPush_Information>(weChatMsgMain);
_ll.SaveLog(3, 21, Common.CurrentUser.GetCurrentUser().LoginName, weChatMsgMain.Title, logData);
return(weChatMsgMain);
}
}
/// <summary>
/// 新增协议操作信息
/// </summary>
/// <param name="agreementInfo">协议操作信息</param>
/// <returns></returns>
public bool CreateAgreementOperationInfo(Business_ProtocolOperations_Information agreementInfo)
{
using (var db = SugarDao_MsSql.GetInstance())
{
try
{
db.BeginTran();
var personInfo = db.Queryable <Business_Personnel_Information>().Where(i => i.Vguid == agreementInfo.PersonnelVGUID).SingleOrDefault();
agreementInfo.VGUID = Guid.NewGuid();
agreementInfo.OperationTime = DateTime.Now;
agreementInfo.CreatedDate = DateTime.Now;
agreementInfo.CreatedUser = personInfo.Name;
string logData = JsonConverter.Serialize(agreementInfo);
_logLogic.SaveLog(1, 34, personInfo.Name, "协议操作", logData);
db.Insert(agreementInfo, false);
db.CommitTran();
return(true);
}
catch (Exception ex)
{
db.RollbackTran();
LogHelper.WriteLog(ex.ToString());
return(false);
}
}
}
/// <summary>
/// 获取用户详细信息
/// </summary>
/// <param name="userID"></param>
/// <returns></returns>
public Business_Personnel_Information GetUserInfo(string userID)
{
using (SqlSugarClient dbMsSql = SugarDao_MsSql.GetInstance())
{
Business_Personnel_Information personModel = new Business_Personnel_Information();
try
{
personModel = dbMsSql.Queryable <Business_Personnel_Information>().Where(i => i.UserID == userID).SingleOrDefault();
}
catch (Exception ex)
{
LogHelper.WriteLog(ex.Message);
_ll.SaveLog(5, 34, "", userID + personModel.Name, ex.Message);
}
return(personModel);
}
}
/// <summary>
/// 获取协议操作历史记录
/// </summary>
/// <param name="searchParam"></param>
/// <param name="para"></param>
/// <returns></returns>
public JsonResultModel <v_Business_ProtocolOperations_Information> GetAgreementOpertaionList(Search_AgreementOperation searchParam, GridParams para)
{
var jsonResult = new JsonResultModel <v_Business_ProtocolOperations_Information>();
using (var db = SugarDao_MsSql.GetInstance())
{
var query = db.Queryable <v_Business_ProtocolOperations_Information>();
if (!string.IsNullOrEmpty(searchParam.Title))
{
query.Where(i => i.Title.Contains(searchParam.Title));
}
if (!string.IsNullOrEmpty(searchParam.Result))
{
var listArr = searchParam.Result.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
query.In(i => i.Result, listArr);
}
if (searchParam.OperationTimeFrom != null && searchParam.OperationTimeTo == null)
{
query.Where(i => i.OperationTime >= searchParam.OperationTimeFrom);
}
if (searchParam.OperationTimeTo != null && searchParam.OperationTimeFrom == null)
{
query.Where(i => i.OperationTime <= searchParam.OperationTimeTo);
}
if (searchParam.OperationTimeTo != null && searchParam.OperationTimeFrom != null)
{
query.Where(i => i.OperationTime >= searchParam.OperationTimeFrom && i.OperationTime <= searchParam.OperationTimeTo);
}
if (!string.IsNullOrEmpty(searchParam.Name))
{
query.Where(i => i.Name.Contains(searchParam.Name));
}
if (!string.IsNullOrEmpty(searchParam.PhoneNumber))
{
query.Where(i => i.PhoneNumber.Contains(searchParam.PhoneNumber));
}
query.OrderBy(i => i.CreatedDate, OrderByType.Desc);
int pageCount = 0;
jsonResult.Rows = query.Select("Title,Result,OperationTime,Name,PhoneNumber,Vguid").ToPageList(para.pagenum, para.pagesize, ref pageCount);
jsonResult.TotalRows = pageCount;
//存入操作日志表
string logData = JsonConverter.Serialize(jsonResult);
_ll.SaveLog(3, 49, CurrentUser.GetCurrentUser().LoginName, "协议操作历史列表", logData);
return(jsonResult);
}
}
public Business_ScanData_Information Save(Guid vguid)
{
Business_ScanData_Information ScanData = new Business_ScanData_Information();
using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
{
var list = _dbMsSql.Queryable <Business_Personnel_Information>().Where(i => i.Vguid == vguid).SingleOrDefault();
var Organization = _dbMsSql.Queryable <Master_Organization>().Where(i => i.Vguid == list.OwnedFleet).SingleOrDefault();
if (list != null)
{
ScanData.MachineCode = "";
ScanData.SystemID = "";
ScanData.ID = list.IDNumber;
ScanData.Age = list.Age;
ScanData.Sex = list.Sex;
ScanData.JobNumber = list.JobNumber;
ScanData.ServiceNumber = list.ServiceNumber;
ScanData.OwnedFleet = Organization.OrganizationName.ToString();
ScanData.UserVguid = vguid;
ScanData.Vguid = Guid.NewGuid();
ScanData.LicensePlate = list.LicensePlate;
ScanData.Name = list.Name;
ScanData.ScanUser = list.UserID;
ScanData.PhoneNumber = list.PhoneNumber;
ScanData.ScanDate = DateTime.Now;
ScanData.CreatedDate = DateTime.Now;
ScanData.CreatedUser = list.Name;
bool result = false;
result = _dbMsSql.Insert(ScanData, false) != DBNull.Value;
}
if (list != null)
{
var history = new Business_ScanHistory_Information()
{
MachineCode = "",
SystemID = "",
ScanUser = list.UserID,
Data = "",
User = "",
ScanTime = DateTime.Now,
CreatedUser = list.Name,
CreatedDate = DateTime.Now,
Vguid = Guid.NewGuid()
};
_dbMsSql.Insert(history, false);
}
//存入操作日志表
string logData = JsonHelper.ModelToJson(ScanData);
_ll.SaveLog(1, 2, list.Name, ScanData.Name, logData);
return(ScanData);
}
}
/// <summary>
/// 分页获取扫描历史列表信息
/// </summary>
/// <param name="searchParam">搜索条件</param>
/// <param name="para">分页信息</param>
/// <returns></returns>
public JsonResultModel <Business_ScanHistory_Information> GetScanHistoryListBySearch(ScanHistorySearch searchParam, GridParams para)
{
using (var db = SugarDao_MsSql.GetInstance())
{
JsonResultModel <Business_ScanHistory_Information> jsonResult = new JsonResultModel <Business_ScanHistory_Information>();
var query = db.Queryable <Business_ScanHistory_Information>();
if (!string.IsNullOrEmpty(searchParam.MachineCode))
{
query.Where(it => it.MachineCode.Contains(searchParam.MachineCode));
}
if (!string.IsNullOrEmpty(searchParam.CreatedDateFrom) && !string.IsNullOrEmpty(searchParam.CreatedDateTo))
{
DateTime createdTimeStart = DateTime.Parse(searchParam.CreatedDateFrom);
DateTime createdTimeEnd = DateTime.Parse(searchParam.CreatedDateTo);
query.Where(i => i.CreatedDate > createdTimeStart && i.CreatedDate < createdTimeEnd);
}
else
{
if (!string.IsNullOrEmpty(searchParam.CreatedDateFrom))
{
DateTime createdTimeStart = DateTime.Parse(searchParam.CreatedDateFrom);
query.Where(i => i.CreatedDate > createdTimeStart);
}
if (!string.IsNullOrEmpty(searchParam.CreatedDateTo))
{
DateTime createdTimeEnd = DateTime.Parse(searchParam.CreatedDateTo);
query.Where(i => i.CreatedDate < createdTimeEnd);
}
}
query.OrderBy(para.sortdatafield + " " + para.sortorder);
jsonResult.TotalRows = query.Count();
jsonResult.Rows = query.ToPageList(para.pagenum, para.pagesize);
//存入操作日志表
string logData = JsonHelper.ModelToJson(jsonResult);
_logLogic.SaveLog(3, 44, CurrentUser.GetCurrentUser().LoginName, "扫码历史列表", logData);
return(jsonResult);
}
}
/// <summary>
/// 删除某一条角色数据（连同角色有关的权限全部删除掉）
/// </summary>
/// <param name="roleTypeVguid"></param>
/// <returns></returns>
public bool DeleteRoleType(string roleTypeVguid)
{
using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
{
bool result = false;
Guid vguid = Guid.Parse(roleTypeVguid);
Sys_Role sysRole = _dbMsSql.Queryable <Sys_Role>().Where(i => i.Vguid == vguid).SingleOrDefault();
string sysRoleJson = JsonHelper.ModelToJson <Sys_Role>(sysRole);
//保存日志
_logLogic.SaveLog(2, 30, Common.CurrentUser.GetCurrentUser().LoginName, "角色", sysRoleJson);
try
{
result = _dbMsSql.Delete <Sys_Role>(i => i.Vguid == vguid);
_dbMsSql.Delete <Sys_Role_Module>(i => i.RoleVGUID == vguid);
}
catch (Exception exp)
{
Common.LogHelper.LogHelper.WriteLog(exp.ToString());
}
return(result);
}
}
/// <summary>
/// 导出
/// </summary>
/// <param name="pushMsgName"></param>
public void Export(string pushMsgName)
{
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance())
{
string sql = string.Format(@"EXEC usp_WeChatPushRate @Title,@userVguid");
DataTable dt = new DataTable();
dt = _dbMsSql.GetDataTable(sql, new
{
Title = pushMsgName,
userVguid = CurrentUser.GetCurrentUser().Vguid
});
dt.TableName = "table";
string amountFileName = SyntacticSugar.ConfigSugar.GetAppString("PushTemplate");
Common.ExportExcel.ExportExcels("PushTemplate.xlsx", amountFileName, dt);
_logLogic.SaveLog(13, 23, Common.CurrentUser.GetCurrentUser().LoginName, "PushTemplate", Common.Tools.DataTableHelper.Dtb2Json(dt));
}
}
public string Index(string userName, string pwd)
{
string result = _ul.ProcessLogin(userName, pwd);
if (result == "登陆成功！")
{
var userInfo = _ul.GeUserManagement(userName);
//登陆成功：将登录信息写入cookie
var cm = CookiesManager <V_User_Information> .GetInstance();
cm.Add(CostCookies.COOKIES_KEY_LOGIN, userInfo, cm.Hour * 24);//将cookie保存24小时
//存入操作日志表
string logData = JsonHelper.ModelToJson(userInfo);
_ll.SaveLog(14, 0, userName, "", logData);
return("ok");
}
return(result);
}
/// <summary>
/// 分页查询习题信息列表
/// </summary>
/// <param name="searchParam"></param>
/// <param name="para"></param>
/// <returns></returns>
public JsonResultModel <V_Business_Exercises_Infomation> GetExerciseListBySearch(Business_Exercises_Infomation_Search searchParam, GridParams para)
{
using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
{
JsonResultModel <V_Business_Exercises_Infomation> jsonResult = new JsonResultModel <V_Business_Exercises_Infomation>();
var query = _dbMsSql.Queryable <V_Business_Exercises_Infomation>().Where(i => i.Status == 1);
if (!string.IsNullOrEmpty(searchParam.ExercisesName))
{
query.Where(i => i.ExercisesName.Contains(searchParam.ExercisesName));
}
//if (!string.IsNullOrEmpty(searchParam.Status))
//{
// int status = int.Parse(searchParam.Status);
// query.Where(i => i.Status == status);
//}
if (!string.IsNullOrEmpty(searchParam.InputType))
{
int inputType = int.Parse(searchParam.InputType);
query.Where(i => i.InputType == inputType);
}
if (!string.IsNullOrEmpty(searchParam.EffectiveDate))
{
DateTime effectiveDate = DateTime.Parse(searchParam.EffectiveDate);
query.Where(i => i.EffectiveDate < effectiveDate);
}
query.OrderBy(para.sortdatafield + " " + para.sortorder);
jsonResult.TotalRows = query.Count();
jsonResult.Rows = query.ToPageList(para.pagenum, para.pagesize);
//存入操作日志表
string logData = JsonHelper.ModelToJson <JsonResultModel <V_Business_Exercises_Infomation> >(jsonResult);
_ll.SaveLog(3, 7, Common.CurrentUser.GetCurrentUser().LoginName, "习题列表", logData);
return(jsonResult);
}
}
/// <summary>
/// 保存简答题分数
/// </summary>
/// <param name="personVguid">答题人Vguid</param>
/// <param name="vguid">习题Vguid</param>
/// <param name="exerciseDetailVguid">具体习题Vguid</param>
/// <param name="score">得分</param>
/// <returns></returns>
public bool SaveShortAnswerMarking(string personVguid, string vguid, string exerciseDetailVguid, string score)
{
using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
{
bool result = false;
try
{
int totalScore = 0;
Guid personVGUID = Guid.Parse(personVguid);
Guid exerciserVGUID = Guid.Parse(vguid);
Guid exerciserDetailVGUID = Guid.Parse(exerciseDetailVguid);
int personScore = int.Parse(score);
Business_ExercisesAnswer_Information exerciserAnswerModel = _dbMsSql.Queryable <Business_ExercisesAnswer_Information>().Where(i => i.BusinessPersonnelVguid == personVGUID && i.BusinessExercisesVguid == exerciserVGUID).SingleOrDefault();
//先更新答题的明细表（更新简答题得分）
result = _dbMsSql.Update <Business_ExercisesAnswerDetail_Information>(new { Score = personScore, ChangeDate = DateTime.Now, ChangeUser = Common.CurrentUser.GetCurrentUser().LoginName }, i => i.BusinessAnswerExercisesVguid == exerciserAnswerModel.Vguid && i.BusinessExercisesDetailVguid == exerciserDetailVGUID);
//获取更新后的总分
List <Business_ExercisesAnswerDetail_Information> exerciseAnswerDetailList = new List <Business_ExercisesAnswerDetail_Information>();
exerciseAnswerDetailList = _dbMsSql.Queryable <Business_ExercisesAnswerDetail_Information>().Where(i => i.BusinessAnswerExercisesVguid == exerciserAnswerModel.Vguid).ToList();
foreach (var item in exerciseAnswerDetailList)
{
totalScore += item.Score;
}
var model = new
{
TotalScore = totalScore,
Marking = 2,
Status = 2,
ChangeDate = DateTime.Now
};
result = _dbMsSql.Update <Business_ExercisesAnswer_Information>(model, i => i.Vguid == exerciserAnswerModel.Vguid);
_logLogic.SaveLog(4, 36, Common.CurrentUser.GetCurrentUser().LoginName, "简答题得分" + score, "vguid总分为" + totalScore);
}
catch (Exception exp)
{
LogHelper.WriteLog("保存简答题分数：" + exp.ToString());
}
return(result);
}
}
/// <summary>
/// 根据搜索条件获取红包领取记录历史
/// </summary>
/// <param name="searchParas"></param>
/// <param name="para"></param>
/// <returns></returns>
public JsonResultModel <v_Business_Redpacket_Push_Information> GetRedPacketHistoryList(Search_RedPacketHistory searchParas, GridParams para)
{
GetRedPacketInfo();
var jsonResult = new JsonResultModel <v_Business_Redpacket_Push_Information>();
using (var db = SugarDao_MsSql.GetInstance())
{
var query = db.Queryable <v_Business_Redpacket_Push_Information>();
if (!string.IsNullOrEmpty(searchParas.Name))
{
query.Where(i => i.Name.Contains(searchParas.Name));
}
if (!string.IsNullOrEmpty(searchParas.UserID))
{
query.Where(i => i.UserID.Contains(searchParas.UserID));
}
if (searchParas.RedpacketStatus != null)
{
query.Where(i => i.RedpacketStatus == searchParas.RedpacketStatus);
}
if (searchParas.ReceiveDateFrom != null && searchParas.ReceiveDateTo == null)
{
query.Where(i => i.ReceiveDate >= searchParas.ReceiveDateFrom);
}
if (searchParas.ReceiveDateFrom == null && searchParas.ReceiveDateTo != null)
{
query.Where(i => i.ReceiveDate <= searchParas.ReceiveDateTo);
}
if (searchParas.ReceiveDateFrom != null && searchParas.ReceiveDateTo != null)
{
query.Where(i => i.ReceiveDate >= searchParas.ReceiveDateFrom && i.ReceiveDate <= searchParas.ReceiveDateTo);
}
query.OrderBy(i => i.CreatedDate, OrderByType.Desc);
int totalCount = 0;
jsonResult.Rows = query.ToPageList(para.pagenum, para.pagesize, ref totalCount);
jsonResult.TotalRows = totalCount;
var logData = JsonConverter.Serialize(jsonResult);
_ll.SaveLog(3, 51, CurrentUser.GetCurrentUser().LoginName, "红包领取历史", logData);
}
return(jsonResult);
}
/// <summary>
/// 保存配置文件内容
/// </summary>
/// <param name="configModel">配置表实体</param>
/// <returns></returns>
public bool SaveConfig(Master_Configuration configModel)
{
using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
{
bool result = false;
try
{
result = _dbMsSql.Update <Master_Configuration>(new { ConfigValue = configModel.ConfigValue }, i => i.ID == configModel.ID);
//写入日志
Master_Configuration configuration = new Master_Configuration();
configuration = _dbMsSql.Queryable <Master_Configuration>().Where(i => i.ID == configModel.ID).SingleOrDefault();
string logData = Common.JsonHelper.ModelToJson(configuration);
_logLogic.SaveLog(4, 35, Common.CurrentUser.GetCurrentUser().LoginName, configuration.ConfigDescription, logData);
}
catch (Exception exp)
{
Common.LogHelper.LogHelper.WriteLog(exp.Message + "/n" + exp.ToString() + "/n" + exp.StackTrace);
}
return(result);
}
}
/// <summary>
/// 分页查询人员列表信息
/// </summary>
/// <param name="searchParam"></param>
/// <param name="para"></param>
/// <returns></returns>
public JsonResultModel <v_Business_PersonnelDepartment_Information> GetUserPageList(Business_PersonDepartmrnt_Search searchParam, GridParams para)
{
using (SqlSugarClient dbMsSql = SugarDao_MsSql.GetInstance())
{
var jsonResult = new JsonResultModel <v_Business_PersonnelDepartment_Information>();
var currentUserInfo = CurrentUser.GetCurrentUser();
string ownfleet = string.Empty;
//是系统管理员
if (currentUserInfo.LoginName.ToLower() == "sysadmin")
{
ownfleet = searchParam.OwnedFleet != Guid.Empty ? searchParam.OwnedFleet.ToString() : dbMsSql.Queryable <Master_Organization>().Where(i => i.ParentVguid == null).Select(i => i.Vguid).SingleOrDefault().ToString();
}
else //非系统管理员
{
//查出当前登录人的部门[dbo].[Sys_Role_Module]
Guid dep = Guid.Parse(currentUserInfo.Department);
ownfleet = dep.ToString();
var listDep = dbMsSql.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + dep + "')"); //找到该部门以及其所有子部门
if (searchParam.OwnedFleet != Guid.Empty)
{
ownfleet = !listDep.Contains(searchParam.OwnedFleet) ? dep.ToString() : searchParam.OwnedFleet.ToString();
}
}
var labelStr = string.Empty;
if (!string.IsNullOrEmpty(searchParam.LabelName))
{
var labelArr = searchParam.LabelName.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < labelArr.Length; i++)
{
labelArr[i] = "'" + labelArr[i].Trim() + "'";
}
labelStr = string.Join(",", labelArr);
}
string sql = "exec usp_Business_PersonnelDepartment_Information @UserName,@JobNumber,@OwnedFleet,@ServiceNumber,@IDNumber,@Status,@Phone,@Start,@End,@Count output,@LabelName";
var pars = SqlSugarTool.GetParameters(new
{
UserName = searchParam.name ?? "",
JobNumber = searchParam.JobNumber ?? "",
OwnedFleet = ownfleet,
ServiceNumber = searchParam.ServiceNumber ?? "",
IDNumber = searchParam.IDNumber ?? "",
Status = searchParam.TranslationApprovalStatus ?? "",
Phone = searchParam.PhoneNumber ?? "",
Start = (para.pagenum - 1) * para.pagesize + 1,
End = para.pagenum * para.pagesize,
Count = 0,
LabelName = labelStr
}); //将匿名对象转成SqlParameter
dbMsSql.IsClearParameters = false; //禁止清除参数
pars[9].Direction = ParameterDirection.Output; //将Count设为 output
var query = dbMsSql.SqlQuery <v_Business_PersonnelDepartment_Information>(sql, pars);
dbMsSql.IsClearParameters = true; //启动请动清除参数
var outPutValue = pars[9].Value.ObjToInt(); //获取output @Count的值
jsonResult.TotalRows = outPutValue;
jsonResult.Rows = query;
//存入操作日志表
string logData = JsonHelper.ModelToJson(jsonResult);
_ll.SaveLog(3, 5, CurrentUser.GetCurrentUser().LoginName, searchParam.name + searchParam.JobNumber + searchParam.ServiceNumber + searchParam.UserID, logData);
return(jsonResult);
}
}
/// <summary>
/// 导出报表
/// </summary>
/// <param name="searchParas">搜索条件</param>
public void Export(U_PaymentHistory_Search searchParas)
{
using (var db = SugarDao_MsSql.GetInstance())
{
#region 获取支付历史数据
var query = db.Queryable <v_PaymentHistory_Information>();
if (CurrentUser.GetCurrentUser().LoginName != "sysAdmin")
{
Guid dep = Guid.Parse(CurrentUser.GetCurrentUser().Department);
var listDep = db.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + dep + "')");
//找到该部门以及其所有子部门
query.In(i => i.OwnedFleet, listDep);
}
if (!string.IsNullOrEmpty(searchParas.Name))
{
query.Where(i => i.Name.Contains(searchParas.Name));
}
if (!string.IsNullOrEmpty(searchParas.PhoneNumber))
{
query.Where(i => i.PhoneNumber.Contains(searchParas.PhoneNumber));
}
if (!string.IsNullOrEmpty(searchParas.IDNumber))
{
query.Where(i => i.IDNumber.Contains(searchParas.IDNumber));
}
if (!string.IsNullOrEmpty(searchParas.JobNumber))
{
query.Where(i => i.JobNumber.Contains(searchParas.JobNumber));
}
if (!string.IsNullOrEmpty(searchParas.TransactionID))
{
query.Where(i => i.TransactionID.Contains(searchParas.TransactionID));
}
if (!string.IsNullOrEmpty(searchParas.Department))
{
Guid department = Guid.Parse(searchParas.Department);
var listDep = db.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + department + "')");
//找到该部门以及其所有子部门
query.In(i => i.OwnedFleet, listDep);
}
if (searchParas.PayDateFrom != null)
{
query.Where(i => i.PayDate >= searchParas.PayDateFrom);
}
if (searchParas.PayDateTo != null)
{
query.Where(i => i.PayDate <= searchParas.PayDateTo);
}
query.OrderBy(i => i.PayDate, OrderByType.Desc);
var dtPaymentHistory = query.ToDataTable();
dtPaymentHistory.TableName = "PaymentHistoryInfo";
#endregion
#region 获取支付总和
var mainDep = Guid.Empty;
if (CurrentUser.GetCurrentUser().LoginName != "sysAdmin")
{
Guid dep = Guid.Parse(CurrentUser.GetCurrentUser().Department);
mainDep = dep;
var listDep = db.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + dep + "')");
//找到该部门以及其所有子部门
if (!string.IsNullOrEmpty(searchParas.Department))
{
Guid searchDep = Guid.Parse(searchParas.Department);
if (!listDep.Contains(searchDep))
{
searchParas.Department = null;
}
}
}
else
{
mainDep = db.Queryable <Master_Organization>().Where(i => i.ParentVguid == null).Select(i => i.Vguid).SingleOrDefault();
}
switch (searchParas.PaymentStatus)
{
case "支付成功":
searchParas.PaymentStatus = "1";
break;
case "支付失败":
searchParas.PaymentStatus = "2";
break;
case "待支付":
searchParas.PaymentStatus = "3";
break;
case "已退款":
searchParas.PaymentStatus = "4";
break;
default:
searchParas.PaymentStatus = "1";
break;
}
var dtPaymentCount = db.GetDataTable("exec usp_Report_PayInformation @name,@phoneNumber,@OwnedFleet,@starDate,@endDate,@Status",
new
{
name = searchParas.Name ?? "",
phoneNumber = searchParas.PhoneNumber ?? "",
OwnedFleet = string.IsNullOrEmpty(searchParas.Department) ? mainDep.ToString() : searchParas.Department,
starDate =
searchParas.PayDateFrom == null ? "1900-01-01 00:00:00" : string.Format("{0:yyyy-MM-dd HH:mm:ss}", searchParas.PayDateFrom),
endDate = searchParas.PayDateTo == null ? "9999-12-31 23:59:59" : string.Format("{0:yyyy-MM-dd HH:mm:ss}", searchParas.PayDateTo),
Status = searchParas.PaymentStatus
});
dtPaymentCount.TableName = "PaymentCount";
#endregion
var ds = new DataSet();
ds.Tables.Add(dtPaymentHistory);
ds.Tables.Add(dtPaymentCount);
ExportExcel.ExportExcels("PaymentReport.xlsx", "PaymentReport" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls", ds);
_logLogic.SaveLog(13, 48, CurrentUser.GetCurrentUser().LoginName, "支付报表", Common.Tools.DataTableHelper.Dtb2Json(dtPaymentHistory));
}
}
