private void EnsureUpdated()
{
log.LogInformation("Checking for DB incremental updates.");
var files = GetSqlFiles();
var scriptNames = files.Select(Path.GetFileNameWithoutExtension).ToArray();
var scriptsRan = incrementalUpdateRepository.AsQueryable()
.Select(i => i.Name)
.ToArray();
var toRun = scriptNames.Except(scriptsRan).ToArray();
foreach (var scriptName in toRun)
{
log.LogInformation("Running sql script: {0}", scriptName);
using (var transaction = dbHelper.BeginTransaction())
{
RunScript(scriptName);
SetExecuted(scriptName);
transaction.Commit();
}
}
}
/// <summary>
/// 修正选区链表
/// </summary>
public static ARESULT FixSelectionList(Int64 cellId)
{
IDbHelper connection = DBConnection.Instance.GetConnection();
if (connection == null)
{
return(ARESULT.E_FAIL);
}
try {
String queryString = "SELECT id FROM selection WHERE cellId=@cellId AND IsGlobalSelection=@IsGlobalSelection ORDER BY id ASC";
DataTable dt = DbFactory.Instance.CreateSqlHelper(connection, queryString)
.SetParameter("cellId", cellId)
.SetParameter("IsGlobalSelection", 0)
.ToDataTable();
if ((dt != null) && (dt.Rows.Count > 0))
{
List <String> strList = new List <String>();
foreach (DataRow dr in dt.Rows)
{
String str = dr["id"].ToString();
if (!String.IsNullOrEmpty(str))
{
strList.Add(str);
}
}
queryString = @"INSERT INTO selection(cellId, data, IsGlobalSelection)
SELECT cellId, data, IsGlobalSelection FROM selection WHERE cellId=@cellId AND IsGlobalSelection=0
ORDER BY id ASC";
connection.BeginTransaction();
Int32 ret = DbFactory.Instance.CreateSqlHelper(connection, queryString)
.SetParameter("cellId", cellId)
.ExecuteNonQuery();
connection.CommitTransaction();
if ((ret > 0) && (strList.Count > 0))
{
String str = String.Join(",", strList);
connection.BeginTransaction();
queryString = "DELETE FROM selection WHERE id IN (" + str + ")";
DbFactory.Instance.CreateSqlHelper(connection, queryString)
.ExecuteNonQuery();
connection.CommitTransaction();
}
}
return(ARESULT.S_OK);
}
catch (Exception e) {
Tracker.LogE(e);
return(ARESULT.E_FAIL);;
}
finally {
DBConnection.Instance.ReturnDBConnection(connection);
}
}
public static ARESULT UpateCurveSample(
Int32 selectionSample,
Int32 groupSelectionSample)
{
IDbHelper connection = DBConnection.Instance.GetConnection();
if (connection == null)
{
return(ARESULT.E_FAIL);
}
try {
connection.BeginTransaction();
String sql = @"UPDATE TempCurveSample SET SelectionSample=@SelectionSample,
GroupSelectionSample=@GroupSelectionSample";
Int32 ret = DbFactory.Instance.CreateSqlHelper(connection, sql)
.SetParameter("SelectionSample", selectionSample)
.SetParameter("GroupSelectionSample", groupSelectionSample)
.ExecuteNonQuery();
connection.CommitTransaction();
return(ret > 0 ? ARESULT.S_OK : ARESULT.E_FAIL);
}
catch (Exception e) {
connection.RollbackTransaction();
Tracker.LogE(e);
return(ARESULT.E_FAIL);
}
finally {
DBConnection.Instance.ReturnDBConnection(connection);
}
}
/// <summary>
/// 删除多个实体对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="models"></param>
/// <returns></returns>
public bool DeleteList <T>(List <T> models) where T : IModel, new()
{
using (IDbHelper db = DbHelper.Create()) {
IDbTransaction tran = db.BeginTransaction();
try {
var list = models;
foreach (T model in list)
{
var execute = DbHelper.CreateExecuteObject(ExecuteType.Delete);
execute.TableName = model.TableName;
execute.PrimaryKey = model.PrimeKey;
execute.AutoColumns.Add(model.PrimeKey);
Type type = model.GetType();
//处理主键
if (!string.IsNullOrEmpty(model.PrimeKey))
{
PropertyInfo info = type.GetProperty(model.PrimeKey, BindingFlags.Public | BindingFlags.Instance);
execute.PrimaryValue = info.GetValue(model, null);
execute.Add(execute.PrimaryKey, execute.PrimaryValue);
}
IDbDataParameter[] parms = ResolveExecuteObject(db, execute);
string sql = execute.CreateDeleteSQL();
db.ExecuteNonQuery(sql, parms);
}
return(true);
} catch (Exception ex) {
tran.Rollback();
throw ex;
}
}
}
/// <summary>
/// 更新手动录像记录
/// </summary>
public static ARESULT UpdateManualRecord(
Int64 manualRecordId,
Int64 recordId)
{
IDbHelper connection = DBConnection.Instance.GetConnection();
if (connection == null)
{
return(ARESULT.E_FAIL);
}
try {
connection.BeginTransaction();
String queryString = "UPDATE manualrecord SET endRecordIndex=@endRecordIndex, endTime=@endTime WHERE id=@id";
Int32 ret = DbFactory.Instance.CreateSqlHelper(connection, queryString)
.SetParameter("endRecordIndex", recordId)
.SetParameter("endTime", DateTime.Now)
.SetParameter("id", manualRecordId)
.ExecuteNonQuery();
connection.CommitTransaction();
return(ret == 1 ? ARESULT.S_OK : ARESULT.E_FAIL);
}
catch (Exception e) {
connection.RollbackTransaction();
Tracker.LogE(e);
return(ARESULT.E_FAIL);
}
finally {
DBConnection.Instance.ReturnDBConnection(connection);
}
}
/// <summary>
/// 删除全部组选区
/// </summary>
public static ARESULT RemoveAllGroupSelection(
Int64 cellId)
{
IDbHelper connection = DBConnection.Instance.GetConnection();
if (connection == null)
{
return(ARESULT.E_FAIL);
}
try {
connection.BeginTransaction();
String queryString = "DELETE FROM groupselection WHERE cellId=@cellId";
Int32 ret = DbFactory.Instance.CreateSqlHelper(connection, queryString)
.SetParameter("cellId", cellId)
.ExecuteNonQuery();
connection.CommitTransaction();
return(ARESULT.S_OK);
}
catch (Exception e) {
connection.RollbackTransaction();
Tracker.LogE(e);
return(ARESULT.E_FAIL);
}
finally {
DBConnection.Instance.ReturnDBConnection(connection);
}
}
/// <summary>
/// 结束监控单元所有未结束的告警
/// </summary>
public static ARESULT UpdateAlarmInfo(
Int64 cellId)
{
IDbHelper connection = DBConnection.Instance.GetConnection();
if (connection == null)
{
return(ARESULT.E_FAIL);
}
try {
connection.BeginTransaction();
String sql = @"UPDATE alarm SET endTime=@endTime, endRecordIndex=startRecordIndex WHERE
cellId= @cellId AND endTime IS NULL";
Int32 ret = DbFactory.Instance.CreateSqlHelper(connection, sql)
.SetParameter("cellId", cellId)
.SetParameter("endTime", DateTime.Now)
.ExecuteNonQuery();
connection.CommitTransaction();
return(ret >= 0 ? ARESULT.S_OK : ARESULT.E_FAIL);
}
catch (Exception e) {
connection.RollbackTransaction();
Tracker.LogE(e);
return(ARESULT.E_FAIL);
}
finally {
DBConnection.Instance.ReturnDBConnection(connection);
}
}
/// <summary>
/// 更新组选区
/// </summary>
public static ARESULT UpdateGroupSelection(
Int64 groupSelectionId,
String data)
{
IDbHelper connection = DBConnection.Instance.GetConnection();
if (connection == null)
{
return(ARESULT.E_FAIL);
}
String queryString = "UPDATE groupselection SET data=@data WHERE id=@id";
try {
connection.BeginTransaction();
Int32 ret = DbFactory.Instance.CreateSqlHelper(connection, queryString)
.SetParameter("id", groupSelectionId)
.SetParameter("data", data)
.ExecuteNonQuery();
connection.CommitTransaction();
return(ret >= 1 ? ARESULT.S_OK : ARESULT.E_FAIL);
}
catch (Exception e) {
Tracker.LogE(e);
connection.RollbackTransaction();
return(ARESULT.E_FAIL);
}
finally {
DBConnection.Instance.ReturnDBConnection(connection);
}
}
/// <summary>
/// 删除组选区
/// </summary>
public static ARESULT RemoveGroupSelection(
Int64 groupSelectionId)
{
IDbHelper connection = DBConnection.Instance.GetConnection();
if (connection == null)
{
return(ARESULT.E_FAIL);
}
try {
connection.BeginTransaction();
Int32 ret = DbFactory.Instance.CreateDeleteHelper(connection, "groupselection")
.SetParameter("id", groupSelectionId)
.ExecuteNonQuery();
connection.CommitTransaction();
return(ret >= 1 ? ARESULT.S_OK : ARESULT.E_FAIL);
}
catch (Exception e) {
connection.RollbackTransaction();
Tracker.LogE(e);
return(ARESULT.E_FAIL);
}
finally {
DBConnection.Instance.ReturnDBConnection(connection);
}
}
/// <summary>
/// 更新红外参数
/// </summary>
public static ARESULT UpateIRParam(
Single emissivity,
Single reflectedTemperature,
Single transmission,
Single atmosphericTemperature,
Single relativeHumidity,
Single distance,
ref Int32 id)
{
IDbHelper connection = DBConnection.Instance.GetConnection();
if (connection == null)
{
return(ARESULT.E_FAIL);
}
ARESULT result = ARESULT.E_FAIL;
try {
connection.BeginTransaction();
Int32 ret = DbFactory.Instance.CreateInsertHelper(connection, "IrParam")
.SetParameter("Emissivity", emissivity)
.SetParameter("ReflectedTemperature", reflectedTemperature)
.SetParameter("Transmission", transmission)
.SetParameter("AtmosphericTemperature", atmosphericTemperature)
.SetParameter("RelativeHumidity", relativeHumidity)
.SetParameter("Distance", distance)
.ExecuteNonQuery();
if (ret != 1)
{
return(ARESULT.E_FAIL);
}
DataTable dt = DbFactory.Instance.CreateQueryHelper(connection, "IrParam")
.Query("id")
.OrderBy("id")
.ToDataTable();
if ((dt == null) || (dt.Rows.Count <= 0))
{
return(ARESULT.E_FAIL);
}
DataRow row = dt.Rows[dt.Rows.Count - 1];
id = Convert.ToInt32(row["id"]);
connection.CommitTransaction();
return(ARESULT.S_OK);
}
catch (Exception e) {
connection.RollbackTransaction();
Tracker.LogE(e);
return(ARESULT.E_FAIL);
}
finally {
DBConnection.Instance.ReturnDBConnection(connection);
}
}
/// <summary>
/// 提交审批(步骤流)
/// </summary>
/// <param name="userInfo">用户信息</param>
/// <param name="categoryCode">单据类型代码</param>
/// <param name="categoryFullName">单据类型名称</param>
/// <param name="objectIds">单据主键组</param>
/// <param name="objectFullName">单据名称</param>
/// <param name="workFlowCode">工作流编号</param>
/// <param name="auditIdea">审批意见</param>
/// <param name="returnStatusCode">返回码</param>
/// <param name="dtWorkFlowActivity">步骤列表</param>
/// <returns></returns>
public string AuditStatr(BaseUserInfo userInfo, string categoryCode, string categoryFullName, string[] objectIds, string objectFullName, string workFlowCode, string auditIdea, out string returnStatusCode, DataTable dtWorkFlowActivity = null)
{
// 写入调试信息
#if (DEBUG)
int milliStart = BaseBusinessLogic.StartDebug(userInfo, MethodBase.GetCurrentMethod());
#endif
// 加强安全验证防止未授权匿名调用
#if (!DEBUG)
LogOnService.UserIsLogOn(userInfo);
#endif
string returnValue = string.Empty;
returnStatusCode = string.Empty;
using (IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.WorkFlowDbType))
{
try
{
dbHelper.Open(WorkFlowDbConnection);
// 默认的都按报表来处理，特殊的直接调用，明确指定
IWorkFlowManager userReportManager = new UserReportManager(userInfo);
BaseWorkFlowCurrentManager workFlowCurrentManager = new BaseWorkFlowCurrentManager(dbHelper, userInfo);
// 事物开始
dbHelper.BeginTransaction();
for (int i = 0; i < objectIds.Length; i++)
{
returnValue = workFlowCurrentManager.AutoStatr(userReportManager, objectIds[i], objectFullName, categoryCode, categoryFullName, workFlowCode, auditIdea, dtWorkFlowActivity);
}
BaseLogManager.Instance.Add(dbHelper, userInfo, this.serviceName, MethodBase.GetCurrentMethod());
// 提交事务
dbHelper.CommitTransaction();
if (!string.IsNullOrEmpty(returnValue))
{
returnStatusCode = StatusCode.OK.ToString();
}
}
catch (Exception ex)
{
// 回滚事务
dbHelper.RollbackTransaction();
BaseExceptionManager.LogException(dbHelper, userInfo, ex);
throw ex;
}
finally
{
dbHelper.Close();
}
}
// 写入调试信息
#if (DEBUG)
BaseBusinessLogic.EndDebug(MethodBase.GetCurrentMethod(), milliStart);
#endif
return(returnValue);
}
/// <summary>
/// 更新常规设置
/// </summary>
public static ARESULT UpdateUserRoutineInfo(
String province,
String city,
String company,
String projectLeader,
String testPersonnel,
String substation,
String deviceposition,
ref Int32 id)
{
IDbHelper connection = DBConnection.Instance.GetConnection();
if (connection == null)
{
return(ARESULT.E_FAIL);
}
try {
connection.BeginTransaction();
String sql = @"INSERT INTO userroutineconfig(province, city, company, projectleader
,testpersonnel, substation, deviceposition) VALUES(@province, @city, @company, @projectleader
,@testpersonnel, @substation, @deviceposition); SELECT Max(id) from userroutineconfig";
Object obj = DbFactory.Instance.CreateSqlHelper(connection, sql)
.SetParameter("province", province)
.SetParameter("city", city)
.SetParameter("company", company)
.SetParameter("projectleader", projectLeader)
.SetParameter("testpersonnel", testPersonnel)
.SetParameter("substation", substation)
.SetParameter("deviceposition", deviceposition)
.ToScalar <Object>();
if (obj == null)
{
connection.RollbackTransaction();
return(ARESULT.E_FAIL);
}
else
{
id = Convert.ToInt32(obj);
connection.CommitTransaction();
return(ARESULT.S_OK);
}
}
catch (Exception e) {
connection.RollbackTransaction();
Tracker.LogE(e);
return(ARESULT.E_FAIL);
}
finally {
DBConnection.Instance.ReturnDBConnection(connection);
}
}
/// <summary>
/// 更新告警设置配置
/// </summary>
/// <param name="sendUser">通讯录</param>
/// <param name="isAlarmSend">告警短信</param>
/// <param name="isHourSend">整点通知</param>
/// <param name="isRegularTimeSend">定时通知</param>
/// <param name="regularTime">定时通知时间集</param>
/// <param name="isAutoReply">自动回复</param>
/// <param name="isSelectionRecord">普通选区录像</param>
/// <param name="isGroupSelectionRecord">组选区录像</param>
/// <returns></returns>
public static ARESULT UpdateAlarmNoticeConfigInfo(
List <string> sendUser,
bool isAlarmSend,
bool isHourSend,
bool isRegularTimeSend,
List <TimeSpan> regularTime,
bool isAutoReply,
bool isSelectionRecord,
bool isGroupSelectionRecord)
{
IDbHelper connection = DBConnection.Instance.GetConnection();
if (connection == null)
{
return(ARESULT.E_FAIL);
}
try {
connection.BeginTransaction();
string sendUserStr = "";
sendUser.ForEach(i => sendUserStr = sendUserStr + i + ",");
string regularTimeStr = "";
regularTime.ForEach(i => regularTimeStr = regularTimeStr + i.ToString() + ",");
string sqlStr = @"UPDATE AlarmNoticeConfig SET senduser=@senduser,
isalarmsend=@isalarmsend, ishoursend=@ishoursend, isregulartimesend=@isregulartimesend,
regulartime=@regulartime, isautoreply=@isautoreply, IsSelectionRecord=@IsSelectionRecord,
IsGroupSelectionRecord=@IsGroupSelectionRecord";
Int32 ret = DbFactory.Instance.CreateSqlHelper(connection, sqlStr)
.SetParameter("senduser", sendUserStr)
.SetParameter("isalarmsend", (isAlarmSend ? 1 : 0))
.SetParameter("ishoursend", (isHourSend ? 1 : 0))
.SetParameter("isregulartimesend", (isRegularTimeSend ? 1 : 0))
.SetParameter("regulartime", regularTimeStr)
.SetParameter("isautoreply", (isAutoReply ? 1 : 0))
.SetParameter("IsSelectionRecord", (isSelectionRecord ? 1 : 0))
.SetParameter("IsGroupSelectionRecord", (isGroupSelectionRecord ? 1 : 0))
.ExecuteNonQuery();
connection.CommitTransaction();
return(ret >= 1 ? ARESULT.S_OK : ARESULT.E_FAIL);
}
catch (Exception e) {
connection.RollbackTransaction();
Tracker.LogE(e);
return(ARESULT.E_FAIL);
}
finally {
DBConnection.Instance.ReturnDBConnection(connection);
}
}
/// <summary>
/// 更新多个实体对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="models"></param>
/// <returns></returns>
public bool UpdateList <T>(List <T> models) where T : IModel, new()
{
using (IDbHelper db = DbHelper.Create()) {
IDbTransaction tran = db.BeginTransaction();
try {
var list = models;
foreach (T model in list)
{
var execute = DbHelper.CreateExecuteObject(ExecuteType.Update);
execute.TableName = model.TableName;
execute.PrimaryKey = model.PrimeKey;
execute.AutoColumns.Add(model.PrimeKey);
Type type = model.GetType();
var properties = model.PropertyChangedList;
//处理主键
if (!properties.Contains(model.PrimeKey))
{
PropertyInfo info = type.GetProperty(model.PrimeKey, BindingFlags.Public | BindingFlags.Instance);
execute.PrimaryValue = info.GetValue(model, null);
execute.Add(execute.PrimaryKey, execute.PrimaryValue);
}
//处理其他属性
foreach (var property in properties)
{
PropertyInfo info = type.GetProperty(property, BindingFlags.Public | BindingFlags.Instance);
//属性可写
if (info.CanWrite)
{
if (info.Name == model.PrimeKey)
{
execute.PrimaryValue = info.GetValue(model, null);
}
var val = info.GetValue(model, null);
if (val != null)
{
execute.Add(info.Name, val);
}
}
}
IDbDataParameter[] parms = ResolveExecuteObject(db, execute);
string sql = execute.CreateUpdateSQL();
db.ExecuteNonQuery(sql, parms);
}
tran.Commit();
return(true);
} catch (Exception ex) {
tran.Rollback();
throw ex;
}
}
}
/// <summary>
/// 批量删除角色
/// </summary>
/// <param name="userInfo">用户</param>
/// <param name="ids">主键数组</param>
/// <returns>影响行数</returns>
public int BatchDelete(BaseUserInfo userInfo, string[] ids)
{
// 写入调试信息
#if (DEBUG)
int milliStart = BaseBusinessLogic.StartDebug(userInfo, MethodBase.GetCurrentMethod());
#endif
// 加强安全验证防止未授权匿名调用
#if (!DEBUG)
LogOnService.UserIsLogOn(userInfo);
#endif
int returnValue = 0;
using (IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.UserCenterDbType))
{
try
{
dbHelper.Open(UserCenterDbConnection);
// 开始数据库事务
dbHelper.BeginTransaction();
string tableName = BaseRoleEntity.TableName;
if (!string.IsNullOrEmpty(BaseSystemInfo.SystemCode))
{
tableName = BaseSystemInfo.SystemCode + "Role";
}
BaseRoleManager roleManager = new BaseRoleManager(dbHelper, userInfo, tableName);
returnValue = roleManager.BatchDelete(ids);
// 提交数据库事务
BaseLogManager.Instance.Add(dbHelper, userInfo, serviceName, AppMessage.RoleService_BatchDelete, MethodBase.GetCurrentMethod());
dbHelper.CommitTransaction();
}
catch (Exception ex)
{
// 撤销数据库事务
dbHelper.RollbackTransaction();
BaseExceptionManager.LogException(dbHelper, userInfo, ex);
throw ex;
}
finally
{
dbHelper.Close();
}
}
// 写入调试信息
#if (DEBUG)
BaseBusinessLogic.EndDebug(MethodBase.GetCurrentMethod(), milliStart);
#endif
return(returnValue);
}
/// <summary>
/// 添加手动录像记录
/// </summary>
public static ARESULT AddManualRecord(
Int64 cellId,
Int64 recordId,
ref Int64 id)
{
IDbHelper connection = DBConnection.Instance.GetConnection();
if (connection == null)
{
return(ARESULT.E_FAIL);
}
try {
connection.BeginTransaction();
DateTime time = DateTime.Now;
String queryString = @"INSERT INTO manualrecord (cellId, name, startTime, startRecordIndex)
VALUES (@cellId, @name, @startTime, @startRecordIndex);
SELECT last_insert_rowid();";
Object obj = DbFactory.Instance.CreateSqlHelper(connection, queryString)
.SetParameter("cellId", cellId)
.SetParameter("name", time.ToString("yyyyMMddHHmmssfff"))
.SetParameter("startTime", time)
.SetParameter("startRecordIndex", recordId)
.ToScalar <Object>();
if (obj == null)
{
id = -1;
connection.RollbackTransaction();
return(ARESULT.E_FAIL);
}
else
{
id = Int64.Parse(obj.ToString());
connection.CommitTransaction();
return(ARESULT.S_OK);
}
}
catch (Exception e) {
connection.RollbackTransaction();
Tracker.LogE(e);
return(ARESULT.E_FAIL);
}
finally {
DBConnection.Instance.ReturnDBConnection(connection);
}
}
/// <summary>
/// 添加录像
/// </summary>
public static ARESULT AddRecord(
Int64 cellId,
String videoFileName,
String selectionFileName,
ref Int64 id)
{
IDbHelper connection = DBConnection.Instance.GetConnection();
if (connection == null)
{
return(ARESULT.E_FAIL);
}
try {
String queryString = @"INSERT INTO record (cellId, date, videoFileName, infoFileName)
VALUES (@cellId, @date, @videoFileName, @irFileName);
SELECT last_insert_rowid();";
connection.BeginTransaction();
Object obj = DbFactory.Instance.CreateSqlHelper(connection, queryString)
.SetParameter("cellId", cellId)
.SetParameter("date", DateTime.Now)
.SetParameter("videoFileName", videoFileName)
.SetParameter("irFileName", selectionFileName)
.ToScalar <Object>();
if (obj == null)
{
id = -1;
connection.RollbackTransaction();
return(ARESULT.E_FAIL);
}
else
{
id = Int64.Parse(obj.ToString());
connection.CommitTransaction();
return(ARESULT.S_OK);
}
}
catch (Exception e) {
connection.RollbackTransaction();
Tracker.LogE(e);
return(ARESULT.E_FAIL);
}
finally {
DBConnection.Instance.ReturnDBConnection(connection);
}
}
/// <summary>
/// 批量执行数据库操作，如果出错会回滚操作。
/// </summary>
/// <param name="queue">SQL 语句队列</param>
/// <param name="connKey">数据库连接字符串</param>
/// <param name="type">指令类型</param>
/// <param name="parms">参数</param>
/// <returns></returns>
public static void Execute(Queue <string> queue, CommandType type = CommandType.Text, string connKey = null, params IDbDataParameter[] parms)
{
using (IDbHelper db = DbHelper.Create(connKey)) {
IDbTransaction tran = db.BeginTransaction();
try {
foreach (string sql in queue)
{
db.ExecuteNonQuery(sql, parms);
}
tran.Commit();
} catch (Exception ex) {
tran.Rollback();
throw ex;
}
}
}
/// <summary>
/// 添加选区
/// </summary>
public static ARESULT AddSelection(
Int64 cellId,
String data,
Boolean isGlobalSelection,
ref Int64 id)
{
IDbHelper connection = DBConnection.Instance.GetConnection();
if (connection == null)
{
return(ARESULT.E_FAIL);
}
String sqlString = @"INSERT INTO selection(cellId, data, IsGlobalSelection) VALUES
(@cellId, @data, @IsGlobalSelection);
SELECT last_insert_rowid();";
try {
connection.BeginTransaction();
Object obj = DbFactory.Instance.CreateSqlHelper(connection, sqlString)
.SetParameter("cellId", cellId)
.SetParameter("data", data)
.SetParameter("IsGlobalSelection", (isGlobalSelection ? 1 : 0))
.ToScalar <Object>();
if (obj == null)
{
id = -1;
connection.RollbackTransaction();
return(ARESULT.E_FAIL);
}
else
{
id = Int64.Parse(obj.ToString());
connection.CommitTransaction();
return(ARESULT.S_OK);
}
}
catch (Exception e) {
Tracker.LogE(e);
connection.RollbackTransaction();
return(ARESULT.E_FAIL);
}
finally {
DBConnection.Instance.ReturnDBConnection(connection);
}
}
// #region public DataTable GetLogGeneralForOnlineUser(BaseUserInfo userInfo) 获取用户访问情况日志
// /// <summary>
// /// 获取用户访问情况日志
// /// </summary>
// /// <param name="userInfo">用户</param>
// /// <returns>数据表</returns>
// public DataTable GetLogGeneralForOnlineUser(BaseUserInfo userInfo, bool UserOnLine)
// {
// // 写入调试信息
// #if (DEBUG)
// int milliStart = BaseBusinessLogic.StartDebug(userInfo, MethodBase.GetCurrentMethod());
// #endif
// // 加强安全验证防止未授权匿名调用
// #if (!DEBUG)
// LogOnService.UserIsLogOn(userInfo);
// #endif
// DataTable dataTable = new DataTable(BaseLogEntity.TableName);
// using (IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.UserCenterDbType))
// {
// BaseUserManager userManager = new BaseUserManager(dbHelper, userInfo);
// try
// {
// dbHelper.Open(UserCenterDbConnection);
// if (userInfo.IsAdministrator)
// {
// dataTable = userManager.GetDataTable();
// }
// else
// {
// BasePermissionScopeManager permissionScopeManager = new BasePermissionScopeManager(dbHelper, userInfo);
// string[] userIds = permissionScopeManager.GetUserIds(userInfo.Id, "Resource.ManagePermission");
// dataTable = userManager.GetDataTableByIds(userIds,UserOnLine);
// }
// dataTable.TableName = BaseLogEntity.TableName;
// BaseLogManager.Instance.Add(dbHelper, userInfo, this.serviceName, AppMessage.LogService_GetLogGeneral, MethodBase.GetCurrentMethod());
// }
// catch (Exception ex)
// {
// BaseExceptionManager.LogException(dbHelper, userInfo, ex);
// throw ex;
// }
// finally
// {
// dbHelper.Close();
// }
// }
// // 写入调试信息
//#if (DEBUG)
// BaseBusinessLogic.EndDebug(MethodBase.GetCurrentMethod(), milliStart);
//#endif
// return dataTable;
// }
// #endregion
#region public DataTable ResetVisitInfo(BaseUserInfo userInfo, string[] ids) 重置用户访问情况
/// <summary>
/// 重置用户访问情况
/// </summary>
/// <param name="userInfo">用户</param>
/// <param name="ids">日志主键</param>
/// <returns>数据表</returns>
public DataTable ResetVisitInfo(BaseUserInfo userInfo, string[] ids)
{
// 写入调试信息
#if (DEBUG)
int milliStart = BaseBusinessLogic.StartDebug(userInfo, MethodBase.GetCurrentMethod());
#endif
// 加强安全验证防止未授权匿名调用
#if (!DEBUG)
LogOnService.UserIsLogOn(userInfo);
#endif
DataTable dataTable = new DataTable(BaseLogEntity.TableName);
using (IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.UserCenterDbType))
{
try
{
dbHelper.Open(UserCenterDbConnection);
dbHelper.BeginTransaction();
BaseUserManager userManager = new BaseUserManager(dbHelper, userInfo);
// 重置访问情况
userManager.ResetVisitInfo(ids);
// 获取列表
dataTable = userManager.GetDataTable();
dataTable.TableName = BaseLogEntity.TableName;
BaseLogManager.Instance.Add(dbHelper, userInfo, this.serviceName, AppMessage.LogService_ResetVisitInfo, MethodBase.GetCurrentMethod());
dbHelper.CommitTransaction();
}
catch (Exception ex)
{
dbHelper.RollbackTransaction();
BaseExceptionManager.LogException(dbHelper, userInfo, ex);
throw ex;
}
finally
{
dbHelper.Close();
}
}
// 写入调试信息
#if (DEBUG)
BaseBusinessLogic.EndDebug(MethodBase.GetCurrentMethod(), milliStart);
#endif
return(dataTable);
}
/// <summary>
/// 将用户从角色中移除
/// </summary>
/// <param name="userInfo">用户</param>
/// <param name="roleId">角色主键</param>
/// <param name="userIds">用户主键</param>
/// <returns>影响行数</returns>
public int RemoveUserFromRole(BaseUserInfo userInfo, string roleId, string[] userIds)
{
// 写入调试信息
#if (DEBUG)
int milliStart = BaseBusinessLogic.StartDebug(userInfo, MethodBase.GetCurrentMethod());
#endif
// 加强安全验证防止未授权匿名调用
#if (!DEBUG)
LogOnService.UserIsLogOn(userInfo);
#endif
int returnValue = 0;
using (IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.UserCenterDbType))
{
try
{
dbHelper.Open(UserCenterDbConnection);
dbHelper.BeginTransaction();
BaseUserManager userManager = new BaseUserManager(dbHelper, userInfo);
if (userIds != null)
{
returnValue += userManager.RemoveFormRole(userIds, roleId);
}
BaseLogManager.Instance.Add(dbHelper, userInfo, serviceName, AppMessage.RoleService_RemoveUserFromRole, MethodBase.GetCurrentMethod());
dbHelper.CommitTransaction();
}
catch (Exception ex)
{
dbHelper.RollbackTransaction();
BaseExceptionManager.LogException(dbHelper, userInfo, ex);
throw ex;
}
finally
{
dbHelper.Close();
}
}
// 写入调试信息
#if (DEBUG)
BaseBusinessLogic.EndDebug(MethodBase.GetCurrentMethod(), milliStart);
#endif
return(returnValue);
}
/// <summary>
/// 更新告警录像状态
/// </summary>
public static ARESULT UpdateAlarmStatus(
List <Int64> alarmIdLis)
{
if ((alarmIdLis == null) || (alarmIdLis.Count <= 0))
{
return(ARESULT.E_FAIL);
}
IDbHelper connection = DBConnection.Instance.GetConnection();
if (connection == null)
{
return(ARESULT.E_FAIL);
}
try {
connection.BeginTransaction();
String sql = @"UPDATE alarm SET status=@status, startRecordIndex=@startRecordIndex,
endRecordIndex=@endRecordIndex WHERE id IN (";
foreach (Int64 alarmId in alarmIdLis)
{
sql += alarmId.ToString() + ",";
}
sql = sql.Substring(0, sql.Length - 1);
sql += ")";
DbFactory.Instance.CreateSqlHelper(connection, sql)
.SetParameter("status", 0)
.SetParameter("startRecordIndex", -1)
.SetParameter("endRecordIndex", -1)
.ExecuteNonQuery();
connection.CommitTransaction();
return(ARESULT.S_OK);
}
catch (Exception e) {
connection.RollbackTransaction();
Tracker.LogE(e);
return(ARESULT.E_FAIL);
}
finally {
DBConnection.Instance.ReturnDBConnection(connection);
}
}
/// <summary>
/// 保存
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSaveBill_Click(object sender, EventArgs e)
{
if (this.PrintBillEntities.Any())
{
var start = BaseBusinessLogic.ConvertToLong(txtStartBill.Text);
var end = BaseBusinessLogic.ConvertToLong(txtEndBill.Text);
var count = end - start;
if (count.ToString() == cmbPrintBillCount.Text)
{
for (int i = 0; i < PrintBillEntities.Count; i++)
{
var billCode = BaseBusinessLogic.ConvertToLong(txtStartBill.Text) + i;
PrintBillEntities[i].BillCode = billCode.ToString();
}
if (MessageUtil.ConfirmYesNo(string.Format("确定保存{0}条单号吗？", PrintBillEntities.Count)))
{
using (IDbHelper dbHelper = DbHelperFactory.GetHelper(CurrentDbType.SQLite, BillPrintHelper.BillPrintConnectionString))
{
try
{
dbHelper.BeginTransaction();
var manager = new ZtoPrintBillManager(dbHelper);
foreach (var itemBill in PrintBillEntities)
{
manager.SetProperty(new KeyValuePair <string, object>(ZtoPrintBillEntity.FieldId, itemBill.Id), new KeyValuePair <string, object>(ZtoPrintBillEntity.FieldBillCode, itemBill.BillCode));
}
dbHelper.CommitTransaction();
MessageUtil.ShowTips("保存成功");
}
catch (Exception ex)
{
dbHelper.RollbackTransaction();
LogUtil.WriteException(ex);
}
}
}
}
else
{
string error = string.Format("需要打印{0}，结束单号-开始单号和需要打印数据不相等，不能保存", PrintBillEntities.Count);
MessageUtil.ShowError(error);
}
}
}
/// <summary>
/// 最终审核通过
/// </summary>
/// <param name="userInfo">当前用户</param>
/// <param name="id">主键</param>
/// <param name="auditIdea">审核意见</param>
/// <returns>影响行数</returns>
public int AuditComplete(BaseUserInfo userInfo, string[] ids, string auditIdea)
{
// 写入调试信息
#if (DEBUG)
int milliStart = BaseBusinessLogic.StartDebug(userInfo, MethodBase.GetCurrentMethod());
#endif
// 加强安全验证防止未授权匿名调用
#if (!DEBUG)
LogOnService.UserIsLogOn(userInfo);
#endif
int returnValue = 0;
using (IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.WorkFlowDbType))
{
try
{
dbHelper.Open(WorkFlowDbConnection);
//IWorkFlowManager userReportManager = new UserReportManager(userInfo);
BaseWorkFlowCurrentManager workFlowCurrentManager = new BaseWorkFlowCurrentManager(dbHelper, userInfo);
dbHelper.BeginTransaction();
returnValue += workFlowCurrentManager.AuditComplete(ids, auditIdea);
dbHelper.CommitTransaction();
BaseLogManager.Instance.Add(dbHelper, userInfo, this.serviceName, MethodBase.GetCurrentMethod());
}
catch (Exception ex)
{
dbHelper.RollbackTransaction();
BaseExceptionManager.LogException(dbHelper, userInfo, ex);
throw ex;
}
finally
{
dbHelper.Close();
}
}
// 写入调试信息
#if (DEBUG)
BaseBusinessLogic.EndDebug(MethodBase.GetCurrentMethod(), milliStart);
#endif
return(returnValue);
}
public void Test_TransManager()
{
IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.CenterDbConnectionString, BaseSystemInfo.CenterDbType);
dbHelper.Open();
try
{
dbHelper.BeginTransaction();
UserManager userManager = new UserManager(dbHelper);
for (int i = 0; i < 10; i++)
{
if (i == 5)
{
throw new Exception();
}
//dbHelper.Open();
UserEntity userEntity = new UserEntity()
{
F1 = DateTime.Now.ToString(),
F2 = new string('a', 50),
F3 = "333",
DateTimeType = DateTime.Now,
NumberType = i,
DoubleType = new Random().Next(1000) * 0.21,
FloatType = 1.23456789f,
DecimalType = 300.055M,
NullType = null,
};
userManager.Insert(userEntity);
//dbHelper.Close();
}
dbHelper.CommitTransaction();
}
catch
{
dbHelper.RollbackTransaction();
}
finally
{
dbHelper.Close();
}
}
/// <summary>
/// 删除收件人
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnDeleteSendMan_Click(object sender, EventArgs e)
{
if (gvReceiveMan.RowCount == 0)
{
XtraMessageBox.Show(@"请添加收件人数据。", AppMessage.MSG0000, MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
var list = GetCheckedUserRecord(gvReceiveMan);
if (list.Any())
{
if (XtraMessageBox.Show(string.Format("确定删除选中的{0}条收件人记录吗？", list.Count), AppMessage.MSG0000, MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.Cancel)
{
return;
}
using (IDbHelper dbHelper = DbHelperFactory.GetHelper(CurrentDbType.SQLite, BillPrintHelper.BillPrintConnectionString))
{
try
{
dbHelper.BeginTransaction();
var userManager = new ZtoUserManager(dbHelper);
var idArray = (from p in list select p.Id.ToString()).ToArray();
var result = userManager.Delete(idArray);
if (result > 0)
{
dbHelper.CommitTransaction();
ReceiveManDataBind();
XtraMessageBox.Show(string.Format("成功删除{0}条收件人记录。", result), AppMessage.MSG0000, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
dbHelper.RollbackTransaction();
XtraMessageBox.Show(@"删除失败", AppMessage.MSG0000, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch
{
dbHelper.RollbackTransaction();
}
}
}
}
/// <summary>
/// 删除手动录像记录
/// </summary>
public static ARESULT RemovetManualRecord(
List <Int64> manualRecordIdList)
{
if ((manualRecordIdList == null) || (manualRecordIdList.Count <= 0))
{
return(ARESULT.E_FAIL);
}
IDbHelper connection = DBConnection.Instance.GetConnection();
if (connection == null)
{
return(ARESULT.E_FAIL);
}
try {
connection.BeginTransaction();
String sqlString = @"DELETE FROM manualrecord WHERE id IN (";
foreach (Int64 manualRecordId in manualRecordIdList)
{
sqlString += manualRecordId.ToString() + ",";
}
sqlString = sqlString.Substring(0, sqlString.Length - 1);
sqlString += ")";
Int32 ret = DbFactory.Instance.CreateSqlHelper(connection, sqlString)
.ExecuteNonQuery();
connection.CommitTransaction();
return(ret > 0 ? ARESULT.S_OK : ARESULT.E_FAIL);
}
catch (Exception e) {
connection.RollbackTransaction();
Tracker.LogE(e);
return(ARESULT.E_FAIL);
}
finally {
DBConnection.Instance.ReturnDBConnection(connection);
}
}
/// <summary>
/// 结束告警录像
/// </summary>
public static ARESULT UpdateAlarmRecord(
Int64 cellId,
Int64 selectionId,
Int64 recordId,
Int32 alarmMode,
Int32 alarmType)
{
IDbHelper connection = DBConnection.Instance.GetConnection();
if (connection == null)
{
return(ARESULT.E_FAIL);
}
try {
connection.BeginTransaction();
String sqlString = @"UPDATE alarm SET endRecordIndex=@endRecordIndex WHERE id IN (SELECT id FROM
alarm WHERE cellId=@cellId AND selectionId=@selectionId AND type=@type AND mode=@mode
AND endTime IS NULL)";
Int32 ret = DbFactory.Instance.CreateSqlHelper(connection, sqlString)
.SetParameter("cellId", cellId)
.SetParameter("selectionId", selectionId)
.SetParameter("mode", alarmMode)
.SetParameter("type", alarmType)
.SetParameter("endRecordIndex", recordId)
.ExecuteNonQuery();
connection.CommitTransaction();
return(ret > 0 ? ARESULT.S_OK : ARESULT.E_FAIL);
}
catch (Exception e) {
connection.RollbackTransaction();
Tracker.LogE(e);
return(ARESULT.E_FAIL);
}
finally {
DBConnection.Instance.ReturnDBConnection(connection);
}
}
public static ARESULT AddSelectionTemperature(
Int64 cellId,
Int64 selectionId,
DateTime recordTime,
Single maxTemperature,
Single minTemperature,
Single aveTemperature)
{
IDbHelper connection = DBConnection.Instance.GetConnection();
if (connection == null)
{
return(ARESULT.E_FAIL);
}
try {
connection.BeginTransaction();
Int32 ret = DbFactory.Instance.CreateInsertHelper(connection, "selectiontemperature")
.SetParameter("cellid", cellId)
.SetParameter("selectionid", selectionId)
.SetParameter("maxtemperature", maxTemperature)
.SetParameter("mintemperature", minTemperature)
.SetParameter("avgtemperature", aveTemperature)
.SetParameter("recordTime", recordTime.ToString("yyyy-MM-dd HH:mm:ss"))
.ExecuteNonQuery();
connection.CommitTransaction();
return(ret == 1 ? ARESULT.S_OK : ARESULT.E_FAIL);
}
catch (Exception e) {
Tracker.LogE(e);
connection.RollbackTransaction();
return(ARESULT.E_FAIL);
}
finally {
DBConnection.Instance.ReturnDBConnection(connection);
}
}
/// <summary>
/// 导入Excel数据到本地数据库
/// </summary>
public bool Import()
{
if (string.IsNullOrEmpty(txtFileFullPath.Text.Trim()))
{
XtraMessageBox.Show(@"请选择录单模板", AppMessage.MSG0000, MessageBoxButtons.OK, MessageBoxIcon.Information);
btnOpenExcel_Click(this, null);
return(false);
}
if (!File.Exists(txtFileFullPath.Text))
{
XtraMessageBox.Show(@"选中文件不存在，请重新选择导入Excel文件", AppMessage.MSG0000, MessageBoxButtons.OK, MessageBoxIcon.Information);
btnOpenExcel_Click(this, null);
return(false);
}
if (!splashScreenManagerImportExcel.IsSplashFormVisible)
{
splashScreenManagerImportExcel.ShowWaitForm();
}
Application.DoEvents();
splashScreenManagerImportExcel.SetWaitFormCaption("请稍后");
splashScreenManagerImportExcel.SetWaitFormDescription("开始导入Excel数据...");
try
{
int crossRow = int.Parse(cmbPrintNumber.Text) - 1;
if (crossRow < 0)
{
crossRow = 1;
}
DataTable chooseDt = ExcelHelper.ExcelToDataTable(txtFileFullPath.Text.Trim(), 38, 0, crossRow);
if (chooseDt != null && chooseDt.Rows.Count > 0)
{
var list = new List <ZtoUserEntity>();
int temp = 0;
foreach (DataRow dr in chooseDt.Rows)
{
++temp;
splashScreenManagerImportExcel.SetWaitFormDescription(string.Format("正在导入Excel数据：{0}/{1}", temp, chooseDt.Rows.Count));
var userEntity = new ZtoUserEntity {
Realname = BaseBusinessLogic.ConvertToString(dr[8])
};
if (ValidateUtil.IsMobile(BaseBusinessLogic.ConvertToString(dr[9])))
{
userEntity.Mobile = BaseBusinessLogic.ConvertToString(dr[9]);
}
else
{
userEntity.TelePhone = BaseBusinessLogic.ConvertToString(dr[9]);
}
var tempProvince = BaseBusinessLogic.ConvertToString(dr[10]);
// 收件人地址（有可能用户不填写收件省市区）
userEntity.Address = BaseBusinessLogic.ConvertToString(dr[13]);
if (tempProvince != null)
{
userEntity.Province = tempProvince.Trim();
}
var tempCity = BaseBusinessLogic.ConvertToString(dr[11]);
if (tempCity != null)
{
userEntity.City = tempCity.Trim();
}
var tempCounty = BaseBusinessLogic.ConvertToString(dr[12]);
if (tempCounty != null)
{
userEntity.County = tempCounty.Trim();
}
if (string.IsNullOrEmpty(userEntity.Province) && string.IsNullOrEmpty(userEntity.City) && string.IsNullOrEmpty(userEntity.County))
{
if (!string.IsNullOrEmpty(userEntity.Address))
{
var result = BaiduMapHelper.GetProvCityDistFromBaiduMap(userEntity.Address);
if (result != null)
{
userEntity.Province = result.Result.AddressComponent.Province;
userEntity.City = result.Result.AddressComponent.City;
userEntity.County = result.Result.AddressComponent.District;
}
}
}
userEntity.Company = BaseBusinessLogic.ConvertToString(dr[31]);
userEntity.Postcode = BaseBusinessLogic.ConvertToString(dr[32]);
userEntity.Issendorreceive = "0";
if (string.IsNullOrEmpty(userEntity.Postcode))
{
if (!string.IsNullOrEmpty(userEntity.City) && !string.IsNullOrEmpty(userEntity.County))
{
userEntity.Postcode = NetworkHelper.GetPostCodeByAddress(userEntity.City, userEntity.County);
}
}
if (!string.IsNullOrEmpty(userEntity.Province))
{
var areaEntity = _areaManager.GetList <BaseAreaEntity>(new KeyValuePair <string, object>(BaseAreaEntity.FieldFullName, userEntity.Province)).FirstOrDefault();
if (areaEntity != null)
{
userEntity.ProvinceId = areaEntity.Id;
}
}
if (!string.IsNullOrEmpty(userEntity.City))
{
var areaEntity = _areaManager.GetList <BaseAreaEntity>(new KeyValuePair <string, object>(BaseAreaEntity.FieldFullName, userEntity.City)).FirstOrDefault();
if (areaEntity != null)
{
userEntity.CityId = areaEntity.Id;
}
}
if (!string.IsNullOrEmpty(userEntity.County))
{
var areaEntity = _areaManager.GetList <BaseAreaEntity>(new KeyValuePair <string, object>(BaseAreaEntity.FieldFullName, userEntity.County)).FirstOrDefault();
if (areaEntity != null)
{
userEntity.CountyId = areaEntity.Id;
}
}
userEntity.CreateOn = DateTime.Now;
list.Add(userEntity);
}
using (IDbHelper dbHelper = DbHelperFactory.GetHelper(CurrentDbType.SQLite, BillPrintHelper.BillPrintConnectionString))
{
try
{
dbHelper.BeginTransaction();
var manager = new ZtoUserManager(dbHelper);
foreach (ZtoUserEntity ztoUserEntity in list)
{
manager.Add(ztoUserEntity, true);
}
dbHelper.CommitTransaction();
GridDataBind();
}
catch (Exception ex)
{
dbHelper.RollbackTransaction();
ProcessException(ex);
}
}
if (splashScreenManagerImportExcel != null && splashScreenManagerImportExcel.IsSplashFormVisible)
{
splashScreenManagerImportExcel.CloseWaitForm();
}
Close();
}
else
{
XtraMessageBox.Show(@"模板没有填写任何数据，导入失败", AppMessage.MSG0000, MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
catch (Exception ex)
{
ProcessException(ex);
return(false);
}
finally
{
if (splashScreenManagerImportExcel != null && splashScreenManagerImportExcel.IsSplashFormVisible)
{
splashScreenManagerImportExcel.CloseWaitForm();
}
}
return(true);
}
