public void SyncData()
{
try
{
//查询最后一次同步时间
channelhistoryarchivesync syncTime = channelhistoryarchivesync.SingleOrDefault("where id=1");
if (syncTime == null)
{
syncTime = new channelhistoryarchivesync();
syncTime.date = DateTime.Now.AddYears(-1);
syncTime.date_created = DateTime.Now;
syncTime.Insert();
}
DateTime syncDate = DateTime.Now.Date;
//查询之前没有同步的数据
Sql channelhistorySql = new Sql();
channelhistorySql.Append(
"select channelNo,DATE_FORMAT(date_created,'%Y-%m-%d') as datestr,count(id) as visitcount")
.Append("from channelHistory")
.Append("WHERE date_created > @0", syncTime.date)
.Append("WHERE date_created <= @0", syncDate)
.Append("group by datestr,channelNo");
IEnumerable<channelhistory> channelhistoryDayList = videoContextDB.GetInstance().Query<channelhistory>(channelhistorySql);
foreach (var channelhistory in channelhistoryDayList)
{
bool isExists = channelhistoryarchive.Exists(
"where channelNo=@0 and date=@1",
channelhistory.channelNo,
channelhistory.datestr);
if (!isExists)
{
channelhistoryarchive channelhistoryarchiveModel = new channelhistoryarchive();
channelhistoryarchiveModel.channelNo = channelhistory.channelNo;
channelhistoryarchiveModel.date = channelhistory.datestr;
channelhistoryarchiveModel.realcount = channelhistory.visitcount;
channelhistoryarchiveModel.date_created = DateTime.Now;
channelhistoryarchiveModel.paymentstate = false;
channelhistoryarchiveModel.Insert();
}
}
//同步的日期
syncTime.date = syncDate;
syncTime.Update();
this.OutPutDialogString(ResponseStatus.Success, "同步成功", "ChannelInfoArchiveList", string.Empty, CallbackType.forward, string.Empty);
}
catch (Exception ex)
{
this.OutPutDialogString(ResponseStatus.Fail, "同步失败", "ChannelInfoArchiveList", string.Empty, CallbackType.forward, string.Empty);
}
}
public void SyncData()
{
try
{
//查询最后一次同步时间
channelhistoryarchivesync syncTime = channelhistoryarchivesync.SingleOrDefault("where id=1");
if (syncTime == null)
{
syncTime = new channelhistoryarchivesync();
syncTime.date = DateTime.Now.AddYears(-1);
syncTime.date_created = DateTime.Now;
syncTime.Insert();
}
DateTime syncDate = DateTime.Now.Date;
//1,同步IP流量
//查询之前没有同步的数据
Sql channelipSql = new Sql();
channelipSql.Append(
"select channelNo,DATE_FORMAT(date_created,'%Y-%m-%d') as datestr,count(id) as visitcount")
.Append("from channelHistory")
.Append("WHERE date_created > @0", syncTime.date)
.Append("WHERE date_created <= @0", syncDate)
.Append("group by datestr,channelNo");
IEnumerable<channelhistory> channelipDayList = videoContextDB.GetInstance().Query<channelhistory>(channelipSql);
//2,同步安装量
Sql channelinstallSql = new Sql();
channelinstallSql.Append(
"select channelNo,createdate,count(id) as installcount")
.Append("from channelinstallhistoryinfo")
.Append("WHERE date_created > @0", syncTime.date)
.Append("WHERE date_created <= @0", syncDate)
.Append("group by channelNo,createdate");
IEnumerable<channelinstallhistoryinfo> channelinstallList = videoContextDB.GetInstance().Query<channelinstallhistoryinfo>(channelinstallSql);
//3,同步Pv
Sql channelpvSql = new Sql();
channelpvSql.Append(
"select channelNo,createdate,count(id) as pvcount")
.Append("from pvinfo")
.Append("WHERE date_created > @0", syncTime.date)
.Append("WHERE date_created <= @0", syncDate)
.Append("group by channelNo,createdate");
IEnumerable<pvinfo> channelpvList = videoContextDB.GetInstance().Query<pvinfo>(channelpvSql);
//4，同步支付成功
Sql channelpaymentsuccessSql = new Sql();
channelpaymentsuccessSql.Append(
"select channelNo,createdate,count(id) as paymentcount")
.Append("from paymentinfo")
.Append("WHERE date_created > @0", syncTime.date)
.Append("WHERE date_created <= @0 and ptype=2", syncDate)
.Append("group by channelNo,createdate");
IEnumerable<paymentinfo> channelpaymentsuccessList = videoContextDB.GetInstance().Query<paymentinfo>(channelpaymentsuccessSql);
//5,同步支付请求数
Sql channelpaymentSql = new Sql();
channelpaymentSql.Append(
"select channelNo,createdate,count(id) as paymentcount")
.Append("from paymentinfo")
.Append("WHERE date_created > @0", syncTime.date)
.Append("WHERE date_created <= @0 and ptype=1", syncDate)
.Append("group by channelNo,createdate");
IEnumerable<paymentinfo> channelpaymentList = videoContextDB.GetInstance().Query<paymentinfo>(channelpaymentSql);
//相差同步时间差
TimeSpan sp = syncDate.Subtract(syncTime.date);
int days = sp.Days;
for (int i = 0; i < days; i++)
{
}
this.OutPutDialogString(ResponseStatus.Success, "同步成功", "ChannelCountList", string.Empty, CallbackType.forward, string.Empty);
}
catch (Exception ex)
{
this.OutPutDialogString(ResponseStatus.Fail, "同步失败", "ChannelCountList", string.Empty, CallbackType.forward, string.Empty);
}
}
