public List<AmountOfChange_aoc_Info> AccountDetailList(AmountOfChange_aoc_Info query)
{
try
{
return this._IRechargeRecordDA.AccountDetailList(query);
}
catch (Exception Ex)
{
throw Ex;
}
}
/// <summary>
/// 增减款记录
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<AmountOfChange_aoc_Info> AccountDetailList(AmountOfChange_aoc_Info query)
{
List<AmountOfChange_aoc_Info> listRecord = new List<AmountOfChange_aoc_Info>();
StringBuilder sbSQL = new StringBuilder();
#region
//sbSQL.AppendLine("select CONVERT(datetime, '" + query.startDate.Value.ToString("yyyy-MM-dd") + "') as startDate,CONVERT(datetime, '" + query.endDate.Value.ToString("yyyy-MM-dd") + "') as endDate,ISNULL( cus_cChaName,'') as aoc_cName, ");
//sbSQL.AppendLine("case cus_cIdentityNum");
//sbSQL.AppendLine("when '" + DefineConstantValue.CodeMasterDefine.KEY2_SIOT_CardUserIdentity_Staff + "' then ISNULL( dpm_cName,'')");
//sbSQL.AppendLine("when '" + DefineConstantValue.CodeMasterDefine.KEY2_SIOT_CardUserIdentity_Student + "' then ISNULL( csm_cClassName,'')");
//sbSQL.AppendLine("else ''");
//sbSQL.AppendLine("end");
//sbSQL.AppendLine("as aoc_cDepartment,");
//sbSQL.AppendLine("cus_cStudentID,");
//sbSQL.AppendLine("case rcr_cRechargeType");
//sbSQL.AppendLine("when '" + DefineConstantValue.ConsumeMoneyFlowType.Recharge_AdvanceMoney.ToString() + "' then ISNULL( rcr_fRechargeMoney ,CONVERT(decimal,0))");
//sbSQL.AppendLine("when '" + DefineConstantValue.ConsumeMoneyFlowType.Recharge_PersonalRealTime.ToString() + "' then ISNULL( rcr_fRechargeMoney ,CONVERT(decimal,0))");
//sbSQL.AppendLine("when '" + DefineConstantValue.ConsumeMoneyFlowType.Recharge_PersonalTransfer.ToString() + "' then ISNULL( rcr_fRechargeMoney ,CONVERT(decimal,0))");
//sbSQL.AppendLine("when '" + DefineConstantValue.ConsumeMoneyFlowType.Recharge_BatchTransfer.ToString() + "' then ISNULL( rcr_fRechargeMoney ,CONVERT(decimal,0))");
//sbSQL.AppendLine("select CONVERT(datetime, '" + query.startDate.Value.ToString("yyyy-MM-dd") + "') as startDate,CONVERT(datetime, '" + query.endDate.Value.ToString("yyyy-MM-dd") + "') as endDate,ISNULL( cus_cChaName,'') as aoc_cName, ");
//sbSQL.AppendLine("case cus_cIdentityNum");
//sbSQL.AppendLine("when '" + DefineConstantValue.CodeMasterDefine.KEY2_SIOT_CardUserIdentity_Staff + "' then ISNULL( dpm_cName,'')");
//sbSQL.AppendLine("when '" + DefineConstantValue.CodeMasterDefine.KEY2_SIOT_CardUserIdentity_Student + "' then ISNULL( csm_cClassName,'')");
//sbSQL.AppendLine("else ''");
//sbSQL.AppendLine("end");
//sbSQL.AppendLine("as aoc_cDepartment,");
//sbSQL.AppendLine("cus_cStudentID,");
//sbSQL.AppendLine("case rcr_cRechargeType");
//sbSQL.AppendLine("when '" + DefineConstantValue.ConsumeMoneyFlowType.Recharge_AdvanceMoney.ToString() + "' then ISNULL( rcr_fRechargeMoney ,CONVERT(decimal,0))");
//sbSQL.AppendLine("when '" + DefineConstantValue.ConsumeMoneyFlowType.Recharge_PersonalRealTime.ToString() + "' then ISNULL( rcr_fRechargeMoney ,CONVERT(decimal,0))");
//sbSQL.AppendLine("when '" + DefineConstantValue.ConsumeMoneyFlowType.Recharge_PersonalTransfer.ToString() + "' then ISNULL( rcr_fRechargeMoney ,CONVERT(decimal,0))");
//sbSQL.AppendLine("when '" + DefineConstantValue.ConsumeMoneyFlowType.Recharge_BatchTransfer.ToString() + "' then ISNULL( rcr_fRechargeMoney ,CONVERT(decimal,0))");
//sbSQL.AppendLine("when '" + DefineConstantValue.ConsumeMoneyFlowType.Refund_PersonalCash.ToString() + "' then 0-ISNULL( rcr_fRechargeMoney ,CONVERT(decimal,0))");
//sbSQL.AppendLine("when '" + DefineConstantValue.ConsumeMoneyFlowType.Refund_CardPersonalRealTime.ToString() + "' then 0-ISNULL( rcr_fRechargeMoney ,CONVERT(decimal,0))");
//sbSQL.AppendLine("when '" + DefineConstantValue.ConsumeMoneyFlowType.Refund_PersonalTransfer.ToString() + "' then 0-ISNULL( rcr_fRechargeMoney ,CONVERT(decimal,0))");
//sbSQL.AppendLine("when '" + DefineConstantValue.ConsumeMoneyFlowType.Refund_BatchTransfer.ToString() + "' then 0-ISNULL( rcr_fRechargeMoney ,CONVERT(decimal,0))");
//sbSQL.AppendLine("else 0");
//sbSQL.AppendLine("end");
//sbSQL.AppendLine("as aoc_dMoney,");
//sbSQL.AppendLine("ISNULL(rcr_fBalance ,CONVERT(decimal,0))as aoc_dAmount,");
//sbSQL.AppendLine("rcr_dRechargeTime as aoc_opTime");
//sbSQL.AppendLine("from dbo.RechargeRecord_rcr");
//sbSQL.AppendLine("left join dbo.CardUserMaster_cus");
//sbSQL.AppendLine("on rcr_cUserID=cus_cRecordID");
//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");
//sbSQL.AppendLine("( ");
////sbSQL.AppendLine("rcr_cRechargeType='" + DefineConstantValue.ConsumeMoneyFlowType.Recharge_AdvanceMoney.ToString() + "' or ");
//sbSQL.AppendLine(" rcr_cRechargeType='" + DefineConstantValue.ConsumeMoneyFlowType.Recharge_PersonalRealTime.ToString() + "'");
//sbSQL.AppendLine("or rcr_cRechargeType='" + DefineConstantValue.ConsumeMoneyFlowType.Recharge_PersonalTransfer.ToString() + "'");
//sbSQL.AppendLine("or rcr_cRechargeType='" + DefineConstantValue.ConsumeMoneyFlowType.Recharge_BatchTransfer.ToString() + "'");
//sbSQL.AppendLine("or rcr_cRechargeType='" + DefineConstantValue.ConsumeMoneyFlowType.Refund_PersonalCash.ToString() + "'");
//sbSQL.AppendLine("or rcr_cRechargeType='" + DefineConstantValue.ConsumeMoneyFlowType.Refund_CardPersonalRealTime.ToString() + "'");
//sbSQL.AppendLine("or rcr_cRechargeType='" + DefineConstantValue.ConsumeMoneyFlowType.Refund_PersonalTransfer.ToString() + "'");
//sbSQL.AppendLine("or rcr_cRechargeType='" + DefineConstantValue.ConsumeMoneyFlowType.Refund_BatchTransfer.ToString() + "'");
//sbSQL.AppendLine(")");
//sbSQL.AppendLine("and rcr_cStatus='" + DefineConstantValue.ConsumeMoneyFlowStatus.Finished.ToString() + "'");
// #endregion
// if (!String.IsNullOrEmpty(query.selectMenu))
// {
//=======
//sbSQL.AppendLine("or rcr_cRechargeType='" + DefineConstantValue.ConsumeMoneyFlowType.Refund_PersonalCash.ToString() + "'");
//sbSQL.AppendLine("or rcr_cRechargeType='" + DefineConstantValue.ConsumeMoneyFlowType.Refund_CardPersonalRealTime.ToString() + "'");
//sbSQL.AppendLine("or rcr_cRechargeType='" + DefineConstantValue.ConsumeMoneyFlowType.Refund_PersonalTransfer.ToString() + "'");
//sbSQL.AppendLine("or rcr_cRechargeType='" + DefineConstantValue.ConsumeMoneyFlowType.Refund_BatchTransfer.ToString() + "'");
//sbSQL.AppendLine(")");
//sbSQL.AppendLine("and rcr_cStatus='" + DefineConstantValue.ConsumeMoneyFlowStatus.Finished.ToString() + "'");
#endregion
if (!String.IsNullOrEmpty(query.selectMenu))
{
//现金充值，退款，实时退款
sbSQL.AppendLine("select CONVERT(datetime, '" + query.startDate.Value.ToString("yyyy-MM-dd") + "') as startDate,CONVERT(datetime, '" + query.endDate.Value.ToString("yyyy-MM-dd") + "') ");
sbSQL.AppendLine("as endDate,ISNULL( cus_cChaName,\'') as aoc_cName, ");
sbSQL.AppendLine("case cus_cIdentityNum");
sbSQL.AppendLine("when 'STAFF' then ISNULL( dpm_cName,'')");
sbSQL.AppendLine("when 'STUDENT' then ISNULL( csm_cClassName,'')");
sbSQL.AppendLine("else ''");
sbSQL.AppendLine("end");
sbSQL.AppendLine("as aoc_cDepartment,");
sbSQL.AppendLine("cus_cStudentID,");
sbSQL.AppendLine("case cuad_cFlowType");
sbSQL.AppendLine("when 'Recharge_AdvanceMoney' then ISNULL( cuad_fFlowMoney ,CONVERT(decimal,0))");
sbSQL.AppendLine("when 'Recharge_PersonalRealTime' then ISNULL( cuad_fFlowMoney ,CONVERT(decimal,0))");
sbSQL.AppendLine("when 'Recharge_PersonalTransfer' then ISNULL( cuad_fFlowMoney ,CONVERT(decimal,0))");
sbSQL.AppendLine("when 'Recharge_BatchTransfer' then ISNULL( cuad_fFlowMoney ,CONVERT(decimal,0))");
sbSQL.AppendLine("when 'Refund_PersonalCash' then 0-ISNULL( cuad_fFlowMoney ,CONVERT(decimal,0))");
sbSQL.AppendLine("when 'Refund_CardPersonalRealTime' then 0-ISNULL( cuad_fFlowMoney ,CONVERT(decimal,0))");
sbSQL.AppendLine("when 'Refund_PersonalTransfer' then 0-ISNULL( cuad_fFlowMoney ,CONVERT(decimal,0))");
sbSQL.AppendLine("when 'Refund_BatchTransfer' then 0-ISNULL( cuad_fFlowMoney ,CONVERT(decimal,0))");
sbSQL.AppendLine("else 0");
sbSQL.AppendLine("end");
sbSQL.AppendLine("as aoc_dMoney,");
sbSQL.AppendLine("ISNULL(cua_fCurrentBalance ,CONVERT(decimal,0))as aoc_dAmount,");
sbSQL.AppendLine("cuad_dOptTime as aoc_opTime");
sbSQL.AppendLine(@",case cuad_cFlowType
when 'Recharge_AdvanceMoney' then N'透支款'
when 'Recharge_PersonalRealTime' then N'个人实时充值'
when 'Recharge_PersonalTransfer' then N'个人转账充值'
when 'Recharge_BatchTransfer' then N'批量转账充值'
when 'Refund_PersonalCash' then N'现金退款'
when 'Refund_CardPersonalRealTime' then N'个人卡退款'
when 'Refund_PersonalTransfer' then N'个人转账退款'
when 'Refund_BatchTransfer' then N'批量转账退款'
else N'异常类型款项'
end
as aoc_cFlowType");
sbSQL.AppendLine("from dbo.CardUserAccountDetail_cuad with(nolock)");
sbSQL.AppendLine("join dbo.CardUserAccount_cua with(nolock)");
sbSQL.AppendLine("on cuad_cCUAID = cua_cRecordID");
sbSQL.AppendLine("left join dbo.CardUserMaster_cus with(nolock)");
sbSQL.AppendLine("on cua_cCUSID=cus_cRecordID");
sbSQL.AppendLine("left join dbo.ClassMaster_csm with(nolock)");
sbSQL.AppendLine("on cus_cClassID=csm_cRecordID");
sbSQL.AppendLine("left join dbo.DepartmentMaster_dpm with(nolock)");
sbSQL.AppendLine("on cus_cClassID=dpm_RecordID");
sbSQL.AppendLine("where 1=1");
if (!String.IsNullOrEmpty(query.selectMenu))
{
sbSQL.AppendLine("and cuad_cFlowType in ( ");
sbSQL.AppendLine(query.selectMenu);
sbSQL.AppendLine(")");
}
if (query.startDate != null)
{
sbSQL.AppendLine("and cuad_dOptTime>='" + query.startDate.Value.ToString("yyyy-MM-dd") + " 00:00:00'");
}
if (query.endDate != null)
{
sbSQL.AppendLine("and cuad_dOptTime<'" + query.endDate.Value.AddDays(1).ToString("yyyy-MM-dd") + " 00:00:00'");
}
if (!String.IsNullOrEmpty(query.cus_cStudentID))
{
sbSQL.AppendLine("and cus_cStudentID ='" + query.cus_cStudentID + "'");
}
if (!string.IsNullOrEmpty(query.cus_cIdentityNum))
{
sbSQL.AppendLine("and cus_cIdentityNum = '" + query.cus_cIdentityNum + "'");
if (query.cus_cIdentityNum == Common.DefineConstantValue.CodeMasterDefine.KEY2_SIOT_CardUserIdentity_Staff)
{
if (query.classID != Guid.Empty)
{
sbSQL.AppendLine("and dpm_RecordID = '" + query.classID + "'");
}
}
else if (query.cus_cIdentityNum == Common.DefineConstantValue.CodeMasterDefine.KEY2_SIOT_CardUserIdentity_Student)
{
if (query.classID != Guid.Empty)
{
sbSQL.AppendLine("and csm_cRecordID = '" + query.classID + "'");
}
}
}
if (!String.IsNullOrEmpty(query.aoc_cName))
{
sbSQL.AppendLine("and cus_cChaName like N'%" + query.aoc_cName + "%'");
}
}
//转帐充值，转账退款
if (query.IsRechargeTransfer || query.IsRefundTransfer)
{
if (!String.IsNullOrEmpty(sbSQL.ToString()))
{
sbSQL.AppendLine("union all");
}
sbSQL.AppendLine("select ");
sbSQL.AppendLine("CONVERT(datetime, '" + query.startDate.Value.ToString("yyyy-MM-dd") + "') as startDate,CONVERT(datetime, '" + query.endDate.Value.ToString("yyyy-MM-dd") + "') ");
sbSQL.AppendLine("as endDate,ISNULL( cus_cChaName,'') as aoc_cName,");
sbSQL.AppendLine("case cus_cIdentityNum");
sbSQL.AppendLine("when 'STAFF' then ISNULL( dpm_cName,'')");
sbSQL.AppendLine("when 'STUDENT' then ISNULL( csm_cClassName,'')");
sbSQL.AppendLine("else '' end as aoc_cDepartment, cus_cStudentID,");
sbSQL.AppendLine("ISNULL( prr_fRechargeMoney ,CONVERT(decimal,0)) as aoc_dMoney,");
sbSQL.AppendLine("ISNULL(cua_fCurrentBalance ,CONVERT(decimal,0))as aoc_dAmount,");
sbSQL.AppendLine("prr_dAddDate as aoc_opTime");
sbSQL.AppendLine(@",case prr_cRechargeType
when 'Recharge_PersonalTransfer' then N'个人转账充值'
when 'Recharge_BatchTransfer' then N'批量转账充值'
when 'Refund_PersonalTransfer' then N'个人转账退款'
when 'Refund_BatchTransfer' then N'批量转账退款'
else N'异常类型款项' end as aoc_cFlowType");
sbSQL.AppendLine("from ");
sbSQL.AppendLine("dbo.PreRechargeRecord_prr with(nolock) ");
sbSQL.AppendLine("join dbo.CardUserMaster_cus with(nolock) ");
sbSQL.AppendLine("on cus_cRecordID = prr_cUserID");
sbSQL.AppendLine("join dbo.CardUserAccount_cua with(nolock)");
sbSQL.AppendLine("on cua_cCUSID = cus_cRecordID");
sbSQL.AppendLine("left join dbo.ClassMaster_csm with(nolock)");
sbSQL.AppendLine("on cus_cClassID=csm_cRecordID");
sbSQL.AppendLine("left join dbo.DepartmentMaster_dpm with(nolock)");
sbSQL.AppendLine("on cus_cClassID=dpm_RecordID where 1=1 ");
sbSQL.AppendLine("and prr_cRechargeType in(");
if (query.IsRechargeTransfer)
{
sbSQL.AppendLine("'" + Common.DefineConstantValue.ConsumeMoneyFlowType.Recharge_PersonalTransfer.ToString() + "',");
sbSQL.AppendLine("'" + Common.DefineConstantValue.ConsumeMoneyFlowType.Recharge_BatchTransfer.ToString() + "'");
}
if (query.IsRefundTransfer)
{
if (query.IsRechargeTransfer)
{
sbSQL.AppendLine(",");
}
sbSQL.AppendLine("'" + Common.DefineConstantValue.ConsumeMoneyFlowType.Refund_BatchTransfer.ToString() + "',");
sbSQL.AppendLine("'" + Common.DefineConstantValue.ConsumeMoneyFlowType.Refund_PersonalTransfer.ToString() + "'");
}
sbSQL.AppendLine(")");
#region 15/11/2013 Add by Donald 特殊筛选（详见注释）
//用于筛出旧系统过渡入新系统的批量转账，该批数据的特征为，预转账时间均为'2012-01-01 00:00:00'
sbSQL.AppendLine(@"and prr_cRecordID not in(
select prr_cRecordID from
(
select * from PreRechargeRecord_prr with(nolock)
left join RechargeRecord_rcr with(nolock) on prr_cRCRID = rcr_cRecordID
left join CardUserAccountDetail_cuad with(nolock) on cuad_cConsumeID = rcr_cRecordID
where prr_cRechargeType = 'Recharge_BatchTransfer'
and prr_dAddDate < '2013-09-03'
) AS ccc)");
#endregion
if (query.startDate != null)
{
sbSQL.AppendLine("and prr_dAddDate>='" + query.startDate.Value.ToString("yyyy-MM-dd") + " 00:00:00'");
}
if (query.endDate != null)
{
sbSQL.AppendLine("and prr_dAddDate <'" + query.endDate.Value.AddDays(1).ToString("yyyy-MM-dd") + " 00:00:00'");
}
if (!String.IsNullOrEmpty(query.cus_cStudentID))
{
sbSQL.AppendLine("and cus_cStudentID ='" + query.cus_cStudentID + "'");
}
if (!string.IsNullOrEmpty(query.cus_cIdentityNum))
{
sbSQL.AppendLine("and cus_cIdentityNum = '" + query.cus_cIdentityNum + "'");
if (query.cus_cIdentityNum == Common.DefineConstantValue.CodeMasterDefine.KEY2_SIOT_CardUserIdentity_Staff)
{
if (query.classID != Guid.Empty)
{
sbSQL.AppendLine("and dpm_RecordID = '" + query.classID + "'");
}
}
else if (query.cus_cIdentityNum == Common.DefineConstantValue.CodeMasterDefine.KEY2_SIOT_CardUserIdentity_Student)
{
if (query.classID != Guid.Empty)
{
sbSQL.AppendLine("and csm_cRecordID = '" + query.classID + "'");
}
}
}
if (!String.IsNullOrEmpty(query.aoc_cName))
{
sbSQL.AppendLine("and cus_cChaName like N'%" + query.aoc_cName + "%'");
}
}
try
{
if (String.IsNullOrEmpty(sbSQL.ToString()))
{
return listRecord;
}
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
db.CommandTimeout = 600000;
IEnumerable<AmountOfChange_aoc_Info> queryData = db.ExecuteQuery<AmountOfChange_aoc_Info>(sbSQL.ToString(), new object[] { });
if (query != null)
{
listRecord = queryData.ToList<AmountOfChange_aoc_Info>();
}
}
}
catch (Exception ex)
{
throw ex;
}
return listRecord;
}
private void btnQuery_Click(object sender, EventArgs e)
{
try
{
this.Cursor = Cursors.WaitCursor;
AmountOfChange_aoc_Info query = new AmountOfChange_aoc_Info();
query.startDate = this.rspSearch.RSP_TimeFrom;
query.endDate = this.rspSearch.RSP_TimeTo;
query.cus_cStudentID = this.rspSearch.RSP_StudentID;
query.aoc_cName = this.rspSearch.RSP_ChaName;
query.IsRechargeTransfer = this.chbRechargeTransfer.Checked;
query.IsRefundTransfer = this.chbArther.Checked;
if (this.rspSearch.RSP_IsAllTech)//是否限制为教师身份
{
query.cus_cIdentityNum = Common.DefineConstantValue.CodeMasterDefine.KEY2_SIOT_CardUserIdentity_Staff;
}
else if (this.rspSearch.RSP_IsAllStu)//是否限制为学生身份
{
query.cus_cIdentityNum = Common.DefineConstantValue.CodeMasterDefine.KEY2_SIOT_CardUserIdentity_Student;
}
else if (!this.rspSearch.RSP_IsAllTech && !this.rspSearch.RSP_IsAllStu)
{
if (this.rspSearch.RSP_ClassID != Guid.Empty)
{
query.cus_cIdentityNum = Common.DefineConstantValue.CodeMasterDefine.KEY2_SIOT_CardUserIdentity_Student;
query.classID = this.rspSearch.RSP_ClassID;
}
else if (this.rspSearch.RSP_DepartmentID != Guid.Empty)
{
query.cus_cIdentityNum = Common.DefineConstantValue.CodeMasterDefine.KEY2_SIOT_CardUserIdentity_Staff;
query.classID = this.rspSearch.RSP_DepartmentID;
}
}
query.selectMenu = string.Empty;//充值类型条件
if (this.chbPersonalCash.Checked)//现金退款
{
query.selectMenu += "'" + DefineConstantValue.ConsumeMoneyFlowType.Refund_PersonalCash.ToString() + "'" + Environment.NewLine;
}
if (this.chbPersonalRealTime.Checked)//个人实时充值
{
if (!String.IsNullOrEmpty(query.selectMenu))
{
query.selectMenu += "," + Environment.NewLine;
}
query.selectMenu += "'" + DefineConstantValue.ConsumeMoneyFlowType.Recharge_PersonalRealTime.ToString() + "'" + Environment.NewLine;
}
if (this.chbArther.Checked)//其他款项
{
if (!String.IsNullOrEmpty(query.selectMenu))
{
query.selectMenu += "," + Environment.NewLine;
}
query.selectMenu += "'" + DefineConstantValue.ConsumeMoneyFlowType.Refund_CardPersonalRealTime.ToString() + "'" + Environment.NewLine;
//query.selectMenu += "," + Environment.NewLine;
//query.selectMenu += "'" + DefineConstantValue.ConsumeMoneyFlowType.Refund_PersonalTransfer.ToString() + "'" + Environment.NewLine;
//query.selectMenu += "," + Environment.NewLine;
//query.selectMenu += "'" + DefineConstantValue.ConsumeMoneyFlowType.Refund_BatchTransfer.ToString() + "'" + Environment.NewLine;
}
List<AmountOfChange_aoc_Info> sources = this._rechargeRecordBL.AccountDetailList(query);
if (sources != null && sources.Count > 0)
{
sources = sources.OrderByDescending(p => p.aoc_opTime).ToList();
ShowReport(sources);
}
else
{
rpvMain.LocalReport.DataSources.Clear();
rpvMain.RefreshReport();
ShowWarningMessage("找不到符合条件的记录。");
}
}
catch
{
}
this.Cursor = Cursors.Default;
}
