public DataSet QueryClientInvoiceReport(DateTime?startDate, DateTime?endDate, int?clientId, int?codeId)
{
string sql = "SELECT ClientId, CodeID, ReportDate,SUM(DownTotalCount) AS SDownTotalCount FROM dbo.SPDayReport WITH (nolock) ";
string where = " Where 1=1 ";
if (startDate.HasValue)
{
where += " and ReportDate>=@startDate ";
}
if (endDate.HasValue)
{
where += " and ReportDate<@endDate ";
}
if (clientId.HasValue)
{
where += " and ClientId=@ClientId ";
}
if (codeId.HasValue)
{
where += " and CodeID=@CodeID ";
}
sql += where;
DbParameters dbParameters = this.CreateNewDbParameters();
if (startDate.HasValue)
{
dbParameters.AddWithValue("startDate", startDate.Value.Date);
}
if (endDate.HasValue)
{
dbParameters.AddWithValue("endDate", endDate.Value.Date.AddDays(1));
}
if (clientId.HasValue)
{
dbParameters.AddWithValue("ClientId", clientId.Value);
}
if (codeId.HasValue)
{
dbParameters.AddWithValue("CodeID", codeId.Value);
}
sql += " GROUP BY ClientId, CodeID, ReportDate ";
sql += " ORDER BY ClientId, CodeID, ReportDate ";
return(this.ExecuteDataSet(string.Format(sql, where), CommandType.Text, dbParameters));
}
public void ClearAllReportedData(DateTime date)
{
string sql = "update SPRecord set IsReport = 1 where CreateDate >= @startDate and CreateDate < @endDate";
DbParameters dbParameters = this.CreateNewDbParameters();
dbParameters.AddWithValue("startDate", date.Date);
dbParameters.AddWithValue("endDate", date.Date.AddDays(1));
this.ExecuteNoQuery(sql, CommandType.Text, dbParameters);
}
public void UpdateUrlSuccessSend(int recordId, string url)
{
string sql = "update SPRecord set IsSycnSuccessed =1 where ID=@RecordID and IsIntercept=0 and IsSycnToClient=1 and IsStatOK=1;" +
"update SPRecordExtendInfo set sSycnDataUrl=@sSycnDataUrl,sSycnDataFailedMessage='' where RecordID=@RecordID ;";
DbParameters dbParameters = this.CreateNewDbParameters();
dbParameters.AddWithValue("sSycnDataUrl", url);
dbParameters.AddWithValue("RecordID", recordId);
this.ExecuteNoQuery(sql, CommandType.Text, dbParameters);
}
public DataTable CaculateReport(DateTime reportDate, DayReportType dayReportType)
{
string sql = "Select [ChannelID],[ClientID],[CodeID],Sum([Count]) as RecordCount from SPRecord with(nolock) where CreateDate >= @startDate and CreateDate < @endDate";
sql += BuildDayReportTypeFilterSql(dayReportType);
sql += " group by [ChannelID],[ClientID],[CodeID]";
DbParameters dbParameters = this.CreateNewDbParameters();
dbParameters.AddWithValue("startDate", reportDate.Date);
dbParameters.AddWithValue("endDate", reportDate.Date.AddDays(1));
return(this.ExecuteDataSet(sql, CommandType.Text, dbParameters).Tables[0]);
}
public void UpdateUrlFailedSend(int recordId, string url, string errMessage)
{
if (errMessage.Length > 50)
{
errMessage = errMessage.Substring(0, 50);
}
string sql = "update SPRecordExtendInfo set sSycnDataUrl=@sSycnDataUrl,sSycnDataFailedMessage=@Error where RecordID=@RecordID ;";
DbParameters dbParameters = this.CreateNewDbParameters();
dbParameters.AddWithValue("sSycnDataUrl", url);
dbParameters.AddWithValue("RecordID", recordId);
dbParameters.AddWithValue("Error", errMessage);
this.ExecuteNoQuery(sql, CommandType.Text, dbParameters);
}
public decimal CaculteActualInterceptRate(SPClientCodeRelationEntity clientCodeRelation, DateTime date)
{
string sql = "SELECT IsIntercept,COUNT(*) as RCount FROM [SPRecord] with(nolock) where CreateDate>@startDate and CreateDate<@endDate and ClientCodeRelationID=@ClientCodeRelationID and IsStatOK=1 group by IsIntercept";
DbParameters dbParameters = this.CreateNewDbParameters();
dbParameters.AddWithValue("startDate", date.Date);
dbParameters.AddWithValue("endDate", date.Date.AddDays(1));
dbParameters.AddWithValue("ClientCodeRelationID", clientCodeRelation.Id);
DataTable dt = this.ExecuteDataSet(sql, CommandType.Text, dbParameters).Tables[0];
object result = dt.Compute("Sum(RCount)", "");
int totalCount = 0;
if (result != System.DBNull.Value)
{
totalCount = Convert.ToInt32(result);
}
int totalInterceptCount = 0;
object result2 = dt.Compute("Sum(RCount)", " IsIntercept =1 ");
if (result2 != System.DBNull.Value)
{
totalInterceptCount = Convert.ToInt32(result2);
}
if (totalCount <= 0)
{
return(Decimal.Zero);
}
return((decimal)totalInterceptCount / (decimal)totalCount);
}
public DataSet QueryRecordProvine(DateTime?startDate, DateTime?endDate, DayReportType dayReportType, int?channelId, int?codeID, int?clientID)
{
string sql = "SELECT [Province] ,count(*) as ProvinceCount FROM [dbo].[SPRecord] with(nolock) {0} group by [Province]";
string where = " Where 1=1 ";
where += BuildDayReportTypeFilterSql(dayReportType);
if (startDate.HasValue)
{
where += " and CreateDate>=@startDate ";
}
if (endDate.HasValue)
{
where += " and CreateDate<@endDate ";
}
if (channelId.HasValue)
{
where += " and ChannelID=@ChannelId ";
}
if (codeID.HasValue)
{
where += " and CodeID=@CodeID ";
}
if (clientID.HasValue)
{
where += " and ClientID=@ClientID ";
}
DbParameters dbParameters = this.CreateNewDbParameters();
if (startDate.HasValue)
{
dbParameters.AddWithValue("startDate", startDate.Value.Date);
}
if (endDate.HasValue)
{
dbParameters.AddWithValue("endDate", endDate.Value.Date.AddDays(1));
}
if (channelId.HasValue)
{
dbParameters.AddWithValue("ChannelId", channelId.Value);
}
if (codeID.HasValue)
{
dbParameters.AddWithValue("CodeID", codeID.Value);
}
if (clientID.HasValue)
{
dbParameters.AddWithValue("ClientID", clientID.Value);
}
return(this.ExecuteDataSet(string.Format(sql, where), CommandType.Text, dbParameters));
}