private void btnQuery_Click(object sender, EventArgs e) { try { this.Cursor = Cursors.WaitCursor; ChangeCardDetail_ccd_Info query = new ChangeCardDetail_ccd_Info(); query.startDate = this.rspSearch.RSP_TimeFrom; query.endDate = this.rspSearch.RSP_TimeTo; query.ccd_cNewID = (Int32)(this.nbbNewCardNo.DecimalValue); if (this.rspSearch.RSP_ClassID != Guid.Empty) { query.classID = this.rspSearch.RSP_ClassID; } else if(this.rspSearch.RSP_DepartmentID != Guid.Empty) { query.classID = this.rspSearch.RSP_DepartmentID; } List<ChangeCardDetail_ccd_Info> sources = this._userCardPairBL.GetChangeCardDetail(query); if (sources != null && sources.Count > 0) { ShowReport(sources); } else { ShowWarningMessage("找不到符合条件的记录。"); } } catch { } this.Cursor = Cursors.Default; //frmChangeCardDetail win = new frmChangeCardDetail(); //win.ShowForm(sources); }
/// <summary> /// 取得换卡信息列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public List<ChangeCardDetail_ccd_Info> GetChangeCardDetail(ChangeCardDetail_ccd_Info query) { try { return this._IUserCardPairDA.GetChangeCardDetail(query); } catch (Exception Ex) { throw Ex; } }
/// <summary> /// 取得换卡信息列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public List<ChangeCardDetail_ccd_Info> GetChangeCardDetail(ChangeCardDetail_ccd_Info query) { List<ChangeCardDetail_ccd_Info> searchData = null; if (query != null) { StringBuilder sbSQL = new StringBuilder(); sbSQL.AppendLine(" select ISNULL( ucp1.ucp_iCardNo,'') as 'ccd_cOldID',"); sbSQL.AppendLine(" ISNULL( ucp2.ucp_iCardNo,'') as 'ccd_cNewID',"); sbSQL.AppendLine(" ISNULL(cus_cChaName,'') as 'ccd_cName',"); sbSQL.AppendLine(" ISNULL(pcs_fCost,CONVERT(decimal(18,2), 0)) as 'ccd_dMoney',"); sbSQL.AppendLine(" case"); sbSQL.AppendLine(" when cus_cIdentityNum='" + Common.DefineConstantValue.CodeMasterDefine.KEY2_SIOT_CardUserIdentity_Staff + "' then dpm_cName "); sbSQL.AppendLine(" when cus_cIdentityNum='" + Common.DefineConstantValue.CodeMasterDefine.KEY2_SIOT_CardUserIdentity_Student + "' then csm_cClassName"); sbSQL.AppendLine(" else '' end as 'ccd_cUnionName',"); sbSQL.AppendLine(" ucp1.ucp_dReturnTime as 'ccd_dOpTime',CONVERT (datetime, '" + query.startDate.Value.ToString("yyyy-MM-dd") + "') as startDate,CONVERT (datetime,'" + query.endDate.Value.ToString("yyyy-MM-dd") + "') as endDate"); sbSQL.AppendLine(" from dbo.UserCardPair_ucp as ucp1"); sbSQL.AppendLine(" join dbo.UserCardPair_ucp as ucp2"); sbSQL.AppendLine(" on ucp1.ucp_cCUSID=ucp2.ucp_cCUSID and ucp1.ucp_cRecordID <> ucp2.ucp_cRecordID and ucp2.ucp_dReturnTime IS NULL and ucp2.ucp_cUseStatus='" + Common.DefineConstantValue.ConsumeCardStatus.Normal.ToString() + "'"); sbSQL.AppendLine(" join dbo.CardUserMaster_cus"); sbSQL.AppendLine(" on ucp2.ucp_cCUSID=cus_cRecordID"); sbSQL.AppendLine(" join dbo.PreConsumeRecord_pcs"); sbSQL.AppendLine(" on ucp2.ucp_cRecordID=pcs_cSourceID"); sbSQL.AppendLine(" left join dbo.ClassMaster_csm"); sbSQL.AppendLine(" on cus_cClassID=csm_cRecordID"); sbSQL.AppendLine(" left join dbo.DepartmentMaster_dpm"); sbSQL.AppendLine(" on cus_cClassID=dpm_RecordID"); sbSQL.AppendLine(" where 1=1"); sbSQL.AppendLine(" and ucp1.ucp_dReturnTime IS NOT NULL"); if (query.startDate != null) { sbSQL.AppendLine(" and ucp1.ucp_dReturnTime>='" + query.startDate.Value.ToString("yyyy-MM-dd") + " 00:00'"); } if (query.endDate != null) { sbSQL.AppendLine(" and ucp1.ucp_dReturnTime<'" + query.endDate.Value.AddDays(1).ToString("yyyy-MM-dd") + " 00:00'"); } if (query.classID != null && query.classID != Guid.Empty) { sbSQL.AppendLine(" and cus_cClassID ='" + query.classID.ToString() + "'"); } if (query.ccd_cNewID > 0) { sbSQL.AppendLine(" and ucp2.ucp_iCardNo =" + query.ccd_cNewID.ToString()); } sbSQL.AppendLine(" order by ucp1.ucp_dReturnTime asc"); try { using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext()) { IEnumerable<ChangeCardDetail_ccd_Info> queryData = db.ExecuteQuery<ChangeCardDetail_ccd_Info>(sbSQL.ToString(), new object[] { }); if (query != null) { searchData = queryData.ToList<ChangeCardDetail_ccd_Info>(); } } } catch (Exception Ex) { throw Ex; } } return searchData; }