/// <summary>
/// 用户开始注册，生成邮箱的验证码
/// </summary>
/// <param name="phone"></param>
/// <param name="userName"></param>
/// <param name="emailAddress"></param>
/// <returns></returns>
public string NewUserSentVerificationCode(string phone, string userName, string emailAddress)
{
Random rd = new Random();
var aa = rd.Next(100000, 999999);
string verificationCode = aa.ToString();
emailAddress = emailAddress.TrimStart().TrimEnd();
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
broker.BeginTransaction();
try
{
string sql = @" INSERT INTO dbo.[User]( userid ,username ,password ,age ,realname ,idcard ,userclass ,createtime ,endtime ,logintime ,emailaddress ,phonenumber,VerificationCode) VALUES ( NEWID() ,'" + userName + "' ,'' ,0 ,'" + userName + "' ,'' ,0 ,GETDATE() ,GETDATE() ,GETDATE() ,'" + emailAddress + "' ,'" + phone + "','" + verificationCode + "')";
var success = broker.ExecuteSQL(sql);
broker.Commit();
}
catch (Exception e)
{
}
}
var reciever = emailAddress;
var cc = "";
var title = "用户注册";
var content = "感谢您注册磐石系统账号，您的验证码是:" + verificationCode;
LubrSentMail lubrmial = new LubrSentMail();
lubrmial.SendEmail(reciever, title, content, null);
return("");
}
public virtual bool DeleteUserByIdList(List <string> idList, string userid)
{
string sql = "";
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
broker.BeginTransaction();
try
{
foreach (string id in idList)
{
sql = "UPDATE " + CasUserTable.C_TableName;
//sql += " SET " + CasUserTable.C_IS_DELETE + "=1 ";
sql += " WHERE " + CasUserTable.C_USER_ID + "='" + id + "'";
broker.ExecuteSQL(sql);
}
broker.Commit();
WebCaching.UserCaching = null;
return(true);
}
catch
{
broker.RollBack();
return(false);
}
}
}
public static void Update <T>(DataAccessBroker broker, List <T> list) where T : EntityBase
{
// 目标为数据表的情况
string curProcess = "";
try
{
// 保存数据
curProcess = "修改数据";
if (list.Count > 0)
{
string sqlString = "UPDATE " + list[0].OringTableSchema.TableName + " SET " + ParseSQL(list[0].OringTableSchema.ValueColumnInfo, ",") + " WHERE " + ParseSQL(list[0].OringTableSchema.KeyColumnInfo, " AND ");
foreach (T entity in list)
{
Application.DoEvents();
// 给目标表新增记录
DataAccessParameterCollection dpc = new DataAccessParameterCollection();
foreach (ColumnInfo field in entity.OringTableSchema.AllColumnInfo)
{
dpc.AddWithValue(field, entity.GetData(field.ColumnName));
}
broker.ExecuteSQL(sqlString, dpc);
}
}
}
catch (Exception ex)
{
// 写日志
string description = "运行中异常，目前执行到'" + curProcess + "',单一事务，事务回滚.";
// 继续抛出异常
throw new InvalidOperationException(description + ex.Message);
}
}
/// <summary>
/// 用户开始注册，生成手机短信的验证码
/// </summary>
/// <param name="phone"></param>
/// <param name="userName"></param>
/// <returns></returns>
public string NewUserSentSMSCode(string phone, string userName, string flagstatus)
{
Random rd = new Random();
var aa = rd.Next(1000, 10000);
string SMSCode = aa.ToString();
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
broker.BeginTransaction();
try
{
string sql = @" INSERT INTO [eContract].[dbo].[UserPhoneSMSCode](phonenumber,sendtime,expiretime,smscode,spare2,SMSId,flagstatus) VALUES ( '" + phone + "', GETDATE() , 20 ,'" + SMSCode + "','',NEWID(),'" + flagstatus + "')";
var success = broker.ExecuteSQL(sql);
broker.Commit();
}
catch (Exception e)
{
}
}
/*var reciever = emailAddress;
* var cc = "";
* var title = "用户注册";
* var content = "感谢您注册磐石系统账号，您的验证码是:" + verificationCode;
* LubrSentMail lubrmial = new LubrSentMail();
* lubrmial.SendEmail(reciever, title, content, null);*/
return(SMSCode);
}
public static void DeleteAllIrisRecoResult()
{
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
broker.ExecuteSQL("delete from Iris_Reco_Result");
}
}
/// <summary>
/// 删除文件
/// </summary>
/// <param name="id"></param>
/// <returns>true:删除成功 false:删除失败</returns>
public bool DeleteFile(string id)
{
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
string sql = $"DELETE FROM dbo.CAS_ATTACHMENT WHERE ATTACHMENT_ID = '{id}'";
return(broker.ExecuteSQL(sql) >= 1);
}
}
/// <summary>
/// 删除用户
/// </summary>
/// <param name="userId"></param>
/// <param name="broker"></param>
public static void DeleteUser(string userId, DataAccessBroker broker)
{
DateTime time = DateTime.Now;
string now = time.Year.ToString() + "-" + time.Month.ToString() + "-" + time.Day.ToString() + " " + time.Hour.ToString() + "-" + time.Minute.ToString() + "-" + time.Second.ToString();
string sql = "update {0} set {1}={2}, {3}=to_date('{4}','YYYY-MM-DD HH24-MI-SS') where {5}='{6}'";
broker.ExecuteSQL(sql);
}
public bool UpdatePageControls(string pageID, int pageType, List <SecPageControlEntity> list)
{
List <SecPageControlEntity> oldPageControls = GetPageControls(pageID, pageType);
List <SecPageControlEntity> insertList = new List <SecPageControlEntity>();
List <SecPageControlEntity> updateList = new List <SecPageControlEntity>();
List <SecPageControlEntity> deleteList = new List <SecPageControlEntity>();
foreach (var item1 in oldPageControls)
{
bool find = false;
foreach (var item2 in list)
{
if (item2.ControlId == item1.ControlId)
{
find = true;
if (item1.ControlName != item2.ControlName || item1.ServerId != item2.ServerId)
{
updateList.Add(item2);
}
}
}
if (!find)
{
deleteList.Add(item1);
}
}
foreach (var item2 in list)
{
if (string.IsNullOrEmpty(item2.ControlId))
{
item2.ControlId = Guid.NewGuid().ToString();
insertList.Add(item2);
}
}
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
try
{
broker.BeginTransaction();
DataAccess.Update(updateList, broker);
DataAccess.Insert(insertList, broker);
foreach (var item in deleteList)
{
broker.ExecuteSQL("DELETE FROM [SEC_ROLE_CONTROL] WHERE [CONTROL_ID]='" + item.ControlId + "' and [PAGE_TYPE]=" + pageType);
}
DataAccess.Delete(deleteList, broker);
broker.Commit();
return(true);
}
catch (Exception ex)
{
broker.RollBack();
return(false);
}
}
}
public static void SaveToTargetDB(DataAccessBroker broker, TableMap tableMap, DataTable dtSource)
{
// 目标为数据表的情况
string curProcess = "";
try
{
if (tableMap.IsFullData == true)
{
// 清空目标表数据
curProcess = "清空目标表数据";
string deleteSql = tableMap.GetTargetDeleteAllSQL();
broker.ExecuteSQL(deleteSql);
}
// 保存数据
curProcess = "保存数据";
// 确保TableMap有FieldMap
tableMap.InitialFieldMapByDataTable(dtSource);
string insertSql = tableMap.GetTargetInsertSQL(broker.ParameterPrefix.ToString());
for (int i = 0; i < dtSource.Rows.Count; i++)
{
Application.DoEvents();
// 给目标表新增记录
DataRow row = dtSource.Rows[i];
DataAccessParameterCollection dpc = tableMap.GetTargetParameters(row, tableMap.TargetTable.NullFieldNameList);
broker.ExecuteSQL(insertSql, dpc);
}
}
catch (Exception ex)
{
// 写日志
string description = "运行中异常，目前执行到'" + curProcess + "',单一事务，事务回滚.";
// 继续抛出异常
throw new InvalidOperationException(description + ex.Message);
}
}
/// <summary>
/// 领导批注
/// </summary>
/// <param name="casContractApprovalResultEntity"></param>
/// <returns></returns>
public bool OptionCommnet(CasContractApprovalResultEntity casContractApprovalResultEntity)
{
bool flag = false;
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
broker.BeginTransaction();
try
{
string sql = $@"UPDATE dbo.CAS_CONTRACT_APPROVER SET STATUS = 3
WHERE APPROVER_TYPE = 1
AND CONTRACT_ID = '{casContractApprovalResultEntity.ContractId}'
AND APPROVER_ID = '{casContractApprovalResultEntity.ApproverId}'
AND STATUS = 2";
//审批人表更改领导批注状态为已批注
broker.ExecuteSQL(sql);
//插入审批结果
flag = DataAccess.Insert(casContractApprovalResultEntity);
if (flag)
{
broker.Commit();
}
else
{
broker.RollBack();
}
}
catch (Exception e)
{
flag = false;
broker.RollBack();
SystemService.LogErrorService.InsertLog(e);
}
}
return(flag);
}
public override List <LogEntity> GetExceptionLog()
{
List <LogEntity> list = new List <LogEntity>();
string strSql = "SELECT * FROM " + TableName + " WHERE LOG_STATE=2 AND LOG_TYPE=1 ORDER BY LOG_TIME DESC";
DataSet ds = DataAccess.SelectDataSet(strSql);
if (ds == null || ds.Tables.Count == 0)
{
return(list);
}
DataTable dt = ds.Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
LogEntity entity = new LogEntity();
entity.Time = row["LOG_TIME"].ToString();
entity.Category = Convert.ToInt32(row["LOG_STATE"]);
entity.JobCode = row["JOB_NAME"].ToString();
entity.Description = row["DESCRIPTION"].ToString();
entity.ExceptionType = row["EXCEPTION_TYPE"].ToString();
entity.ExceptionMessage = row["EXCEPTION_MESSAGE"].ToString();
entity.ThreadName = row["THREAD_Name"].ToString();
list.Add(entity);
}
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
broker.ExecuteSQL("UPDATE " + TableName + " SET LOG_TYPE=2 WHERE LOG_STATE=2 AND LOG_TYPE=1 ");
}
return(list);
}
/// <summary>
/// 写日志
/// </summary>
/// <param name="jobCode"></param>
/// <param name="logState"></param>
/// <param name="description"></param>
public override void WriteLogInternal(string jobCode, string description, Exception ex,
int logCategory, string threadName)
{
using (DataAccessBroker broder = DataAccessFactory.Instance())
{
string sqlString = "insert into " + this.TableName
+ " ("
+ " sn"
+ " ,LOG_DDP_ID"
+ " ,JOB_NAME"
+ " ,LOG_TIME"
+ " ,LOG_TYPE"
+ " ,LOG_STATE"
+ " ,PHASE"
+ " ,DESCRIPTION"
+ " ,EXCEPTION_TYPE"
+ " ,EXCEPTION_MESSAGE"
+ " ,MACHINE_NAME"
+ " ,PROCESS_NAME"
+ " ,THREAD_NAME"
+ " )"
+ " values("
+ " emp_sequence.nextval"
+ " ," + _prefix + "LOG_DDP_ID"
+ " ," + _prefix + "JOB_NAME"
+ " ," + _prefix + "LOG_TIME"
+ " ," + _prefix + "LOG_TYPE"
+ " ," + _prefix + "LOG_STATE"
+ " ," + _prefix + "PHASE"
+ " ," + _prefix + "DESCRIPTION"
+ " ," + _prefix + "EXCEPTION_TYPE"
+ " ," + _prefix + "EXCEPTION_MESSAGE"
+ " ," + _prefix + "MACHINE_NAME"
+ " ," + _prefix + "PROCESS_NAME"
+ " ," + _prefix + "THREAD_NAME"
+ ")";
DataAccessParameterCollection dpc = new DataAccessParameterCollection();
ColumnInfo columnInfo = new ColumnInfo("LOG_DDP_ID", "LOG_DDP_ID", true, typeof(string));
dpc.AddWithValue(columnInfo, Guid.NewGuid().ToString());
if (jobCode == "")
{
jobCode = " ";
}
columnInfo = new ColumnInfo("JOB_NAME", "JOB_NAME", true, typeof(string));
dpc.AddWithValue(columnInfo, jobCode);
columnInfo = new ColumnInfo("LOG_TIME", "LOG_TIME", true, typeof(DateTime));
dpc.AddWithValue(columnInfo, DateTime.Now);
columnInfo = new ColumnInfo("LOG_TYPE", "LOG_TYPE", true, typeof(int));
dpc.AddWithValue(columnInfo, Log_Type_Hand);
columnInfo = new ColumnInfo("LOG_STATE", "LOG_STATE", true, typeof(int));
dpc.AddWithValue(columnInfo, logCategory);
columnInfo = new ColumnInfo("PHASE", "PHASE", true, typeof(string));
dpc.AddWithValue(columnInfo, " ");
columnInfo = new ColumnInfo("DESCRIPTION", "DESCRIPTION", true, typeof(string));
dpc.AddWithValue(columnInfo, description);
if (ex == null)
{
columnInfo = new ColumnInfo("EXCEPTION_TYPE", "EXCEPTION_TYPE", true, typeof(string));
dpc.AddWithValue(columnInfo, " ");
columnInfo = new ColumnInfo("EXCEPTION_MESSAGE", "EXCEPTION_MESSAGE", true, typeof(string));
dpc.AddWithValue(columnInfo, " ");
}
else
{
columnInfo = new ColumnInfo("EXCEPTION_TYPE", "EXCEPTION_TYPE", true, typeof(string));
dpc.AddWithValue(columnInfo, ex.GetType().ToString());
string tempMessage = ex.Message + ex.StackTrace;
if (tempMessage.Length > 1999)
{
tempMessage = tempMessage.Substring(0, 1999);
}
columnInfo = new ColumnInfo("EXCEPTION_MESSAGE", "EXCEPTION_MESSAGE", true, typeof(string));
dpc.AddWithValue(columnInfo, tempMessage);
}
columnInfo = new ColumnInfo("MACHINE_NAME", "MACHINE_NAME", true, typeof(string));
dpc.AddWithValue(columnInfo, Environment.MachineName);
columnInfo = new ColumnInfo("PROCESS_NAME", "PROCESS_NAME", true, typeof(string));
dpc.AddWithValue(columnInfo, "eContract.DDP.WindowsService.exe");
columnInfo = new ColumnInfo("THREAD_NAME", "THREAD_NAME", true, typeof(string));
dpc.AddWithValue(columnInfo, threadName);
broder.ExecuteSQL(sqlString, dpc);
}
}
/// <summary>
/// 保存header和detail到数据,一笔header与对应的几笔detail作为一个事务
/// </summary>
/// <param name="dtHeader"></param>
/// <param name="dtDetail"></param>
private void SaveHeaderDetailToTarget(ref Hashtable parameters, DataTable dtHeader, DataTable dtDetail, string dataSplitOperator)
{
List <string> dataIDList = (List <string>)parameters[DDPConst.Param_DataIDList];
int nError = 0;
int nSuccess = 0;
DataAccessBroker brokerTarget = DataAccessFactory.Instance(this.TargetDataAccessCfg);
try
{
// header sql
this.HeaderTableMap.InitialFieldMapByDataTable(dtHeader);
string insertSqlHeader = this.HeaderTableMap.GetTargetInsertSQL(brokerTarget.ParameterPrefix.ToString());
// detail sql
if (dtDetail.Rows.Count > 0)
{
this.DetailTableMap.InitialFieldMapByDataTable(dtDetail.Rows[0]);
}
string insertSqlDetail = this.DetailTableMap.GetTargetInsertSQL(brokerTarget.ParameterPrefix.ToString());
for (int i = 0; i < dtHeader.Rows.Count; i++)
{
Application.DoEvents();
string recordsCondition = "";
// 一条header
DataRow rowHeader = dtHeader.Rows[i];
// 一笔完整的记录一个事务，包括header和detail
brokerTarget.BeginTransaction();
try
{
// 并出关联字段及值，便出检索detail
string refFieldSql = "";
string refValueSql = "";
string[] refFieldList = this.DetailTableMap.SourceTable.RefFields.Split(new char[] { ',' });
for (int x = 0; x < refFieldList.Length; x++)
{
string refField = refFieldList[x];
if (refFieldSql != "")
{
refFieldSql += "+'|'+";
}
refFieldSql += refField;
if (refValueSql != "")
{
refValueSql += "|";
}
refValueSql += rowHeader[refField].ToString();
}
recordsCondition = refFieldSql + "='" + refValueSql + "'";
// 找到Detail数据
DataRow[] detailRowList = dtDetail.Select(recordsCondition);
LogManager.Current.WriteCommonLog(this.JobCode, refValueSql + "对应" + detailRowList.Length.ToString() + "条detail记录。", this.ThreadName);
// 将header插入到数据库
DataAccessParameterCollection dpcHeader = this.HeaderTableMap.GetTargetParameters(rowHeader, this.HeaderTableMap.TargetTable.NullFieldNameList);
brokerTarget.ExecuteSQL(insertSqlHeader, dpcHeader);
// 将detail插入到数据库
for (int j = 0; j < detailRowList.Length; j++)
{
// detail
DataRow rowDetail = detailRowList[j];
DataAccessParameterCollection dpcDetail = this.DetailTableMap.GetTargetParameters(rowDetail, this.DetailTableMap.TargetTable.NullFieldNameList);
brokerTarget.ExecuteSQL(insertSqlDetail, dpcDetail);
}
// 事务提交
brokerTarget.Commit();
LogManager.Current.WriteCommonLog(this.JobCode, recordsCondition + "记录保存数据库成功。", this.ThreadName);
nSuccess++;
}
catch (Exception ex)
{
// 事务回滚
brokerTarget.RollBack();
LogManager.Current.WriteErrorLog(this.JobCode, recordsCondition + "记录保存数据库出错:" + ex.Message, this.ThreadName);
// 从DataIDList中移出此笔记录，这样就不会更新最新时间了
string dataID = this.GetFieldValue(rowHeader, this.HeaderTableMap.SourceTable.PrimaryKeys, dataSplitOperator);
dataIDList.Remove(dataID);
nError++;
// 继续下面的记录
continue;
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
brokerTarget.Close();
}
// 写日志
string errorInfo = "来源共" + dtHeader.Rows.Count.ToString() + "笔记录，" + nSuccess.ToString() + "笔保存成功，" + nError.ToString() + "笔保存失败。";
LogManager.Current.WriteCommonLog(this.JobCode, errorInfo, this.ThreadName);
// 保存到参数里，传给外面
parameters[DDPConst.Param_Info] = errorInfo;
// 将错误的记录数保存在参数里
if (nError > 0)
{
parameters[DDPConst.Param_ErrorCount] = nError;
}
}