private void rsPanel_OnShowReportClick(object sender, EventArgs e) { this.Cursor = Cursors.WaitCursor; try { CardFeeSummary_cfs_Info searchInfo = new CardFeeSummary_cfs_Info(); if (!string.IsNullOrEmpty(this.rsPanel.RSP_MonthFrom)) { DateTime dtMonthFrom = DateTime.Parse(this.rsPanel.RSP_MonthFrom); searchInfo.cfs_dMonthFrom = dtMonthFrom; } if (!string.IsNullOrEmpty(this.rsPanel.RSP_MonthTo)) { DateTime dtMonthTo = DateTime.Parse(this.rsPanel.RSP_MonthTo); searchInfo.cfs_dMonthTo = dtMonthTo; } ICardFeeSummaryBL cardBL = MasterBLLFactory.GetBLL<ICardFeeSummaryBL>(MasterBLLFactory.CardFeeSummary); List<CardFeeSummary_cfs_Info> listDeposit = cardBL.SearchRecords(searchInfo, chkContainZero.Checked); ShowReport(listDeposit); } catch (Exception ex) { ShowErrorMessage(ex); } this.Cursor = Cursors.Default; }
public List<CardFeeSummary_cfs_Info> SearchRecords(CardFeeSummary_cfs_Info searchInfo, bool IsContainZero) { try { return this._ICardFeeSummaryDA.SearchRecords(searchInfo, IsContainZero); } catch (Exception ex) { throw ex; } }
public List<CardFeeSummary_cfs_Info> SearchRecords(CardFeeSummary_cfs_Info searchInfo, bool IsContainZero) { List<CardFeeSummary_cfs_Info> listRes = new List<CardFeeSummary_cfs_Info>(); try { StringBuilder sbIncome = new StringBuilder(); StringBuilder sbCost = new StringBuilder(); if (searchInfo != null) { if (searchInfo.cfs_dMonthFrom != DateTime.MinValue) { sbIncome.AppendLine(" and pcs_dAddDate >='" + searchInfo.cfs_dMonthFrom.ToString("yyyy-MM-01 00:00:00") + "'"); sbCost.AppendLine(" and prd_dCertificateDate >='" + searchInfo.cfs_dMonthFrom.ToString("yyyy-MM-01 00:00:00") + "'"); } if (searchInfo.cfs_dMonthTo != DateTime.MinValue) { sbIncome.AppendLine("and pcs_dAddDate <'" + DateTime.Parse(searchInfo.cfs_dMonthTo.ToString("yyyy-MM-01")).AddMonths(1).ToString("yyyy-MM-dd 00:00:00") + "'"); sbCost.AppendLine("and prd_dCertificateDate <'" + DateTime.Parse(searchInfo.cfs_dMonthTo.ToString("yyyy-MM-01")).AddMonths(1).ToString("yyyy-MM-dd 00:00:00") + "'"); } } StringBuilder sbSQL = new StringBuilder(); sbSQL.AppendLine(@" select ISNULL(AA.PayMonth,BB.PayMonth) as cfs_cMonthName, ISNULL(CardIncome,0.00) as cfs_fCardIncome,ISNULL(CardIncomeCount,0) as cfs_iCardIncomeCount, ISNULL(CardCost,0.00) as cfs_fCardPay,ISNULL(CardCount,0) as cfs_iCardPayCount from( select SUM(pcs_fCost) as CardIncome, COUNT(PayMonth)as CardIncomeCount,PayMonth from ( select pcs_fCost,SUBSTRING(CONVERT(VARCHAR(10),pcs_dAddDate,120),0,8) as PayMonth from PreConsumeRecord_pcs with(nolock) left join vie_AllStudentCardUserInfos with(nolock) on pcs_cUserID=cus_cRecordID "); sbSQL.AppendLine(" where (pcs_cConsumeType = '" + Common.DefineConstantValue.ConsumeMoneyFlowType.NewCardCost.ToString() + "' or pcs_cConsumeType = '" + Common.DefineConstantValue.ConsumeMoneyFlowType.ReplaceCardCost.ToString() + "') and "); if (IsContainZero) { sbSQL.Append(" pcs_fCost >=0 "); } else { sbSQL.Append(" pcs_fCost >0 "); } if (!string.IsNullOrEmpty(sbIncome.ToString())) { sbSQL.AppendLine(sbIncome.ToString()); } sbSQL.AppendLine(@" ) as A group by PayMonth) as AA left join( select SUM(prd_fPayMoney) as CardCost,SUM(prd_iPayCount) as CardCount,PayMonth from( select prd_fPayMoney,prd_iPayCount,SUBSTRING(CONVERT(VARCHAR(10),prd_dCertificateDate,120),0,8) AS PayMonth from dbo.PayRecord_prd with(nolock)"); sbSQL.AppendLine(" where prd_cPayType = 'Card'"); if (!string.IsNullOrEmpty(sbCost.ToString())) { sbSQL.AppendLine(sbCost.ToString()); } sbSQL.AppendLine(") as B group by PayMonth)as BB on AA.PayMonth = BB.PayMonth order by AA.PayMonth"); using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext()) { IEnumerable<CardFeeSummary_cfs_Info> query = db.ExecuteQuery<CardFeeSummary_cfs_Info>(sbSQL.ToString(), new object[] { }); if (query != null) { listRes = query.ToList(); } } } catch (Exception ex) { throw ex; } return listRes; }