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); } }