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