public VisitLog GetByOpenId(string openId)
{
VisitLog log = new VisitLog();
using (MySqlConnection conn = new MySqlConnection(connStr))
{
try
{
var weekAgo = DateTime.Now.AddDays(-7).ToString("yyyy-MM-dd");
conn.Open();
MySqlCommand cmd = new MySqlCommand($"SELECT openid, nickname, max(createdat) as lastVisitedAt, count(1) as count"
+ " FROM demo.apilog"
+ $" where createdat > '{weekAgo}' and openid='{openId}'", conn);
var sqlReader = cmd.ExecuteReader();
if (sqlReader.Read())
{
log.openId = sqlReader["openid"] == DBNull.Value ? string.Empty : (string)sqlReader["openid"];
log.nickName = sqlReader["nickname"] == DBNull.Value ? string.Empty : (string)sqlReader["nickname"];
log.lastVisitedAt = sqlReader["lastVisitedAt"] == DBNull.Value ? string.Empty : ((DateTime)sqlReader["lastVisitedAt"]).ToString("yyyy-MM-dd HH:mm:ss");
log.visitCountLastWeek = sqlReader["count"] == DBNull.Value ? 0 : (long)sqlReader["count"];
}
}
finally
{
conn.Close();
}
}
return(log);
}
private void CreateRemindEmail(VisitLog log)
{
throw new BizException("提醒邮件没有发送，请修改此处代码。");
//IPPSystemUserInfo ippuserinfo = SystemUserHelper.GetUserInfoBy(log.CreateUserSysNumber);
//UserInfo userinfo = null;
//try
//{
// userinfo = ControlPanelServiceAdapter.GetUserInfo(new UserInfo() { LoginName = ippuserinfo.UniqueUserName, CompanyCode = ippuserinfo.CompanyCode });
//}
//catch (System.Exception e)
//{
// throw new BusinessException("获取回访操作者信息失败：" + e.Message);
//}
//if (userinfo != null)
//{
// VisitEmailRemind entityVER = new VisitEmailRemind();
// entityVER.CSEmail = userinfo.EmailAddress;
// entityVER.VisitSysNo = visitCustomer.SystemNumber;
// entityVER.RemindDate = log.RemindDate;
// entityVER.VisitType = 1;
// entityVER.CreateDate = log.CreateDate;
// entityVER.CreateUserSysNumber = log.CreateUserSysNumber;
// entityVER.CustomerSysNo = log.CustomerSysNo;
// try
// {
// VisitEmailRemindDA.InsertVisitEmailRemind(entityVER);
// }
// catch (System.Exception e)
// {
// throw new BusinessException("添加维护邮件提醒失败：" + e.Message);
// }
//}
}
public string UpdateVisitLog(VisitLog visitlog) //更新某条访问记录
{
try
{
SqlConnection conn = new SqlConnection(DbUtil.ConnString);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = DbUtil.visitlogDataBasesql4;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqlParameter("@account", visitlog.Account));
cmd.Parameters.Add(new SqlParameter("@name", visitlog.Name));
cmd.Parameters.Add(new SqlParameter("@time", visitlog.Time));
cmd.Parameters.Add(new SqlParameter("@imageNumber", visitlog.ImageNumber));
cmd.Parameters.Add(new SqlParameter("@departmentNumber", visitlog.ProductType));
conn.Open(); //打开数据链接
SqlDataReader reader = cmd.ExecuteReader();
conn.Close();
}
catch (Exception ee)
{
exception = ee.ToString();
//Console.WriteLine(ee.ToString());
}
//Console.WriteLine(image.ToString());
return(exception);
}
public bool UpdateLog(string id, string logTime, string purpose, string recepter, string remark, string leftTime)
{
if (logTime.Length == 0)
{
logTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
logTime = DateTime.Parse(logTime).ToString("yyyy-MM-dd HH:mm:ss");
}
if (leftTime.Length > 0)
{
leftTime = DateTime.Parse(leftTime).ToString("yyyy-MM-dd HH:mm:ss");
}
VisitLog log = new VisitLog();
log.Id = id;
log.Time = logTime;
log.Purpose = purpose;
log.Recepter = recepter;
log.Remark = remark;
log.LeaveTime = leftTime;
return(_logRepository.update(log) > 0);
}
public List <VisitLog> GetList(string startDate, string visitor, string accepter)
{
string sql = "select l.id, l.time, l.purpose, l.recepter, l.remark, l.leave_time, l.visitor_id, v.name, v.company from visit_log l left join visitor v on l.visitor_id=v.id where l.time>@startTime and v.name like '%" +
visitor + "%' and l.recepter like '%" + accepter + "%' order by l.time desc";
SQLiteParameter[] parameters = new SQLiteParameter[1];
parameters[0] = new SQLiteParameter("@startTime", startDate);
SQLiteDataReader reader = SQLHelper.ExecuteReader(sql, parameters);
List <VisitLog> list = new List <VisitLog>();
while (reader.Read())
{
VisitLog log = new VisitLog();
log.Id = reader["id"].ToString();
log.Time = reader["time"].ToString();
log.Purpose = reader["purpose"].ToString();
log.Recepter = reader["recepter"].ToString();
log.Remark = reader["remark"].ToString();
log.VisitorName = reader["name"].ToString();
log.VisitorCompany = reader["company"].ToString();
log.LeaveTime = reader["leave_time"].ToString();
log.VisitorId = reader["visitor_id"].ToString();
list.Add(log);
}
return(list);
}
public string RemoveVisitLog(VisitLog visitlog) //表中删除某行
{
try
{
SqlConnection conn = new SqlConnection(DbUtil.ConnString);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = DbUtil.visitlogDataBasesql2;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqlParameter("@account", visitlog.Account));
conn.Open(); //打开数据链接
SqlDataReader reader = cmd.ExecuteReader();
conn.Close();
}
catch (Exception ee)
{
exception = ee.ToString();
//Console.WriteLine(ee.ToString());
}
//Console.WriteLine(image.ToString());
return(exception);
}
public virtual VisitLog AddOrderVisitLog(VisitLog log)
{
VisitCustomer visitCustomer = CustomerVisitDA.GetVisitCustomerByCustomerSysNo(log.CustomerSysNo.Value);
if (visitCustomer == null)
{
throw new BizException(string.Format(ResouceManager.GetMessageString("Customer.CustomerVisit", "AddOrderVisitLog_Customer_NoSelect"),
log.CustomerSysNo));
}
if (!(visitCustomer.IsActive.HasValue && visitCustomer.IsActive.Value) || !(visitCustomer.IsRMA.HasValue && visitCustomer.IsRMA.Value))
{
throw new BizException(ResouceManager.GetMessageString("Customer.CustomerVisit", "AddOrderVisitLog_Customer_NoActivation"));
}
if (visitCustomer.LastMaintenanceStatus != null && (visitCustomer.LastMaintenanceStatus == VisitDealStatus.Complete || visitCustomer.LastMaintenanceStatus == VisitDealStatus.Failed))
{
throw new BizException(ResouceManager.GetMessageString("Customer.CustomerVisit", "AddOrderVisitLog_Maintenance_Repeat"));
}
log.VisitSysNo = visitCustomer.SysNo;
try
{
CustomerVisitDA.InsertOrderVisitLog(log);
}
catch (System.Exception e)
{
throw new BizException(string.Format(ResouceManager.GetMessageString("Customer.CustomerVisit", "AddOrderVisitLog_Err"),
e.Message));
}
UpdateVisitCusotmer(visitCustomer, log, true);
return(log);
}
private VisitCustomer AddVisitCusotmer(VisitLog log)
{
VisitCustomer info = new VisitCustomer
{
ConsumeDesire = log.ConsumeDesire,
ContactCount = 1,
CustomerID = log.CustomerID,
CustomerSysNo = log.CustomerSysNo,
//EditDate = log.EditDate,
//EditUserAcct = log.EditUserAcct,
//EditUserSysNo = log.EditUserSysNo,
//InDate = log.InDate,
//InUserAcct = log.InUserAcct,
//InUserSysNo = log.InUserSysNo,
IsRMA = false,
LastCallResult = log.CallResult,
LastCallStatus = log.DealStatus,
LastCallTime = DateTime.Now,
OrderAmount = 0,
OrderCount = 0,
};
info = CustomerVisitDA.InsertVisitCustomer(info);
return(info);
}
public virtual VisitLog AddVisitLog(VisitLog visiLog)
{
if (GetVisitorFromSession(visiLog.VisitorId) == null)
{
ClearVisitorCache(visiLog.VisitorId);
}
return(VisitorData.AddVisitLog(visiLog));
}
public virtual VisitLog InsertVisitBlackCustomer(VisitLog info)
{
DataCommand cmd = DataCommandManager.GetDataCommand("Customer_Visit_Insert_VisitBlackList");
cmd.SetParameterValue <VisitLog>(info);
cmd.ExecuteNonQuery();
return(info);
}
public VisitLog AddVisitLog(VisitLog visiLog)
{
using (var cnn = ActionDb.DbConnection())
{
cnn.Execute(visiLog.GetSqliteInsertSql(null), visiLog);
return(visiLog);
}
}
private void leaveVisitor(VisitLog log)
{
FrmLeave frmLeave = new FrmLeave(log);
if (frmLeave.ShowDialog() == DialogResult.OK)
{
refreshData(null);
}
}
public virtual VisitLog AddOrderVisitLog(VisitLog log)
{
log = VisitProcessor.AddOrderVisitLog(log);
if (log.DealStatus == VisitDealStatus.FollowUp && log.RemindDate.HasValue)
{
CreateRemindEmail(log);
}
return(log);
}
public virtual VisitLog AddCustomerVisitLog(VisitLog log)
{
log = VisitProcessor.AddCustomerVisitLog(log);
if (log.DealStatus.Value == VisitDealStatus.FollowUp && log.RemindDate.HasValue)
{
//需要跟进
//CreateRemindEmail(entity);
}
return(log);
}
public VisitLog AddVisitLog(VisitLog log)
{
if (log.SOSysNo.HasValue && log.SOSysNo.Value > 0)
{
return(CustomerVisitAppService.AddOrderVisitLog(log));
}
else
{
return(CustomerVisitAppService.AddCustomerVisitLog(log));
}
}
private void AddVisitLog(ActionExecutingContext filterContext, string vid)
{
VisitLog log = new VisitLog();
log.VisitorId = vid;
log.VisitTime = DateTime.Now;
log.ClientIp = HttpRequestUtil.GetClientIp(filterContext.RequestContext.HttpContext.Request);
log.UserAgent = filterContext.RequestContext.HttpContext.Request.UserAgent;
log.EnterUrl = string.Concat(filterContext.RequestContext.HttpContext.Request.Url);
log.RefererUrl = string.Concat(filterContext.RequestContext.HttpContext.Request.UrlReferrer);
log.VisitTimeInMs = TimeUtility.ToUtcTimeInMillis(log.VisitTime);
VisitorService.AddVisitLog(log);
}
public int update(VisitLog log)
{
string sql = "update visit_log set time=@time, purpose=@purpose, recepter=@recepter, remark=@remark, leave_time=@leave_time where id=@id";
SQLiteParameter[] parameters = new SQLiteParameter[6];
parameters[0] = new SQLiteParameter("@id", log.Id);
parameters[1] = new SQLiteParameter("@time", log.Time);
parameters[2] = new SQLiteParameter("@purpose", log.Purpose);
parameters[3] = new SQLiteParameter("@recepter", log.Recepter);
parameters[4] = new SQLiteParameter("@remark", log.Remark);
parameters[5] = new SQLiteParameter("@leave_time", log.LeaveTime);
return(SQLHelper.ExecuteNonQuery(sql, parameters));
}
public int insert(VisitLog log)
{
string sql = "insert into visit_log(id, time, purpose, recepter, remark, visitor_id, leave_time) values(@id, @time, @purpose, @recepter, @remark, @visitor_id, @leave_time)";
SQLiteParameter[] parameters = new SQLiteParameter[7];
parameters[0] = new SQLiteParameter("@id", log.Id);
parameters[1] = new SQLiteParameter("@time", log.Time);
parameters[2] = new SQLiteParameter("@purpose", log.Purpose);
parameters[3] = new SQLiteParameter("@recepter", log.Recepter);
parameters[4] = new SQLiteParameter("@remark", log.Remark);
parameters[5] = new SQLiteParameter("@visitor_id", log.VisitorId);
parameters[6] = new SQLiteParameter("@leave_time", log.LeaveTime);
return(SQLHelper.ExecuteNonQuery(sql, parameters));
}
public VisitEditFormVM(Booth booth)
{
Msg = "";
if (booth.Background == Brushes.Yellow)
{
VisitLog = dc.VisitLogs.Where(x => x.BoothNumber == booth.Number && x.EndDate == null).First();
VisitLog.EndDate = DateTime.Now;
CardId = VisitLog.CardId;
CardFill = false;
}
else
{
VisitLog = new VisitLog {
BoothNumber = booth.Number, BeginDate = DateTime.Now
};
CardFill = true;
}
}
public async Task Invoke(HttpContext context)
{
visitLog = new VisitLog();
HttpRequest request = context.Request;
visitLog.Url = request.Path.ToString();
visitLog.Headers = request.Headers.ToDictionary(k => k.Key, v => string.Join(";", v.Value.ToList()));
visitLog.Method = request.Method;
visitLog.ExcuteStartTime = DateTime.Now;
using (var reader = new StreamReader(request.Body))
{
visitLog.RequestBody = reader.ReadToEnd();
}
context.Response.OnCompleted(ResponseCompletedCallback, context);
await _next(context);
}
public void AddLog(CustomerVisitMaintainView view, System.Action action)
{
string relativeUrl = "/CustomerService/Visit/AddLog";
view.Log.CompanyCode = CPApplication.Current.CompanyCode;
VisitLog log = EntityConverter <VisitLogVM, VisitLog> .Convert(view.Log);
restClient.Create <VisitLog>(relativeUrl, log, (sender, e) =>
{
if (e.FaultsHandle())
{
return;
}
if (action != null)
{
action();
}
});
}
private void gvLog_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (e.ColumnIndex == 7)
{
VisitLog log = gvLog.Rows[e.RowIndex].DataBoundItem as VisitLog;
if (log != null)
{
if (log.LeaveTime.Length == 0)
{
leaveVisitor(log);
}
else
{
MessageBox.Show("访客:[" + log.VisitorName + "]已经离开了哦", "提示");
}
}
}
}
public ActionResult VisitLogCreate([DataSourceRequest] DataSourceRequest request, VisitLogViewModel visitLog)
{
if (visitLog != null && ModelState.IsValid)
{
var vl = new VisitLog
{
DoctorId = visitLog.DoctorId,
PatientId = visitLog.PatientId,
Complaint = visitLog.Complaint,
Diagnosis = visitLog.Diagnosis,
VisitDate = visitLog.VisitDate
};
_context.VisitLog.Add(vl);
_context.SaveChanges();
}
return(Json(new[] { visitLog }.ToDataSourceResult(request, ModelState)));
}
public List <VisitLog> SelectVisitLog(string account) //查找表 返回结果
{
VisitLog visitlog = new VisitLog();
List <VisitLog> visitlogList = null;
try
{
SqlConnection conn = new SqlConnection(DbUtil.ConnString);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = DbUtil.visitlogDataBasesql3;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqlParameter("@account", account));
conn.Open(); //打开数据链接
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) //开始读取数据
{
visitlog.Account = reader.GetString(reader.GetOrdinal("account"));
visitlog.Name = reader.GetString(reader.GetOrdinal("name"));
visitlog.Time = reader.GetString(reader.GetOrdinal("time"));
visitlog.ImageNumber = reader.GetString(reader.GetOrdinal("imageNumber"));
visitlog.ProductType = reader.GetString(reader.GetOrdinal("productType"));
visitlogList.Add(visitlog);
}
}
catch (Exception ee)
{
exception = ee.ToString();
//Console.WriteLine(ee.ToString());
}
//Console.WriteLine(image.ToString());
//return exception;
//Console.WriteLine(image.ToString());
return(visitlogList);
}
public IList <string> MostVisitedPattern(string[] username, int[] timestamp, string[] website)
{
List <string> result = new List <string>();
VisitLog[] log = new VisitLog[username.Length];
// number of websites user visited
Dictionary <string, int> dict = new Dictionary <string, int>();
for (int i = 0; i < username.Length; i++)
{
log[i] = new VisitLog(username[i], timestamp[i], website[i]);
if (!dict.ContainsKey(username[i]))
{
dict.Add(username[i], 0);
}
dict[username[i]]++;
}
Array.Sort(log, (a, b) => a.UserName == b.UserName ? a.TimeStamp.CompareTo(b.TimeStamp) : a.UserName.CompareTo(b.UserName));
Dictionary <(string, string, string), int> dictCountPattern = new Dictionary <(string, string, string), int>();
int j = 0;
while (j < log.Length)
{
string userName = log[j].UserName;
int noOfWebsites = dict[userName];
//all combinations
for (int x = j; x < j + noOfWebsites - 2; x++)
{
for (int y = x + 1; y < j + noOfWebsites - 1; y++)
{
for (int z = y + 1; z < j + noOfWebsites; z++)
{
if (!dictCountPattern.ContainsKey((log[x].Website, log[y].Website, log[z].Website)))
{
dictCountPattern.Add((log[x].Website, log[y].Website, log[z].Website), 0);
}
dictCountPattern[(log[x].Website, log[y].Website, log[z].Website)]++;
private void btnModifyLog_Click(object sender, EventArgs e)
{
VisitLog log = null;
if (gvLog.SelectedRows.Count > 0)
{
log = gvLog.SelectedRows[0].DataBoundItem as VisitLog;
}
if (log == null)
{
MessageBox.Show("请点击行头，选择要修改的记录！", "提示");
return;
}
EditLog logFrm = new EditLog(log);
logFrm.OnFormClose += refreshData;
logFrm.ShowDialog(this);
}
public bool AddLog(string logTime, string purpose, string recepter, string remark, string visitorId)
{
if (logTime.Length == 0)
{
logTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
logTime = DateTime.Parse(logTime).ToString("yyyy-MM-dd HH:mm:ss");
}
VisitLog log = new VisitLog();
log.Id = this.NewGUID();
log.Time = logTime;
log.Purpose = purpose;
log.Recepter = recepter;
log.Remark = remark;
log.VisitorId = visitorId;
return(_logRepository.insert(log) > 0);
}
private void btnDeleteLog_Click(object sender, EventArgs e)
{
VisitLog log = null;
if (gvLog.SelectedRows.Count > 0)
{
log = gvLog.SelectedRows[0].DataBoundItem as VisitLog;
}
if (log == null)
{
MessageBox.Show("请点击行头，选择要删除的记录！", "提示");
return;
}
if (MessageBox.Show("确定要删除访问记录吗？", "提示", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
_logService.DeleteLog(log.Id);
bindGridView();
}
}
/// <summary>
/// 增加
/// </summary>
/// <param name="VisitLog">VisitLog实体对象</param>
/// <returns>int值,返回自增ID</returns>
public int AddReturnId(VisitLog model)
{
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@CreateTime", model.CreateTime),
new SqlParameter("@UserId", model.UserId),
new SqlParameter("@UserIP", model.UserIP),
new SqlParameter("@UserCity", model.UserCity),
new SqlParameter("@Device", model.Device),
new SqlParameter("@Browse", model.Browse),
new SqlParameter("@Url", model.Url),
new SqlParameter("@GroupId", model.GroupId),
new SqlParameter("@ProId", model.ProId),
new SqlParameter("@Status", model.Status),
new SqlParameter("@OrderIndex", model.OrderIndex),
new SqlParameter("@Description", model.Description),
new SqlParameter("@VisitCount", model.VisitCount),
new SqlParameter("@IsDelete", model.IsDelete),
new SqlParameter("@IsFront", model.IsFront)
};
return(Convert.ToInt32(DBHelper.ExecuteScalar("VisitLog_AddReturnId", param)));
}
public int ImportVisitLogSource(TextReader reader)
{
var csvReader = new CsvReader(reader, new CsvConfiguration {
Delimiter = "|"
});
//for large csv file, read row by row to prevent memory peak
while (csvReader.Read())
{
var visitLog = new VisitLog
{
date = csvReader.GetField <DateTime>(nameof(VisitLog.date)),
website = csvReader.GetField <string>(nameof(VisitLog.website)),
visits = csvReader.GetField <int>(nameof(VisitLog.visits)),
createdBy = "dataImporter",
createdOn = DateTime.Now
};
_dataStoreRepository.AddEntity(visitLog);
}
return(_dataStoreRepository.SaveChanges());
}
