public ReturnValueInfo InsertRecord(PaymentUDGeneralSetting_pus_Info infoObject)
{
ReturnValueInfo returnInfo = new ReturnValueInfo(false);
if (infoObject != null)
{
try
{
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
string sqlStr = string.Empty;
sqlStr += "delete from " + Environment.NewLine;
sqlStr += "dbo.PaymentUDGeneralSetting_pus" + Environment.NewLine;
sqlStr += "where 1=1" + Environment.NewLine;
if (infoObject.pus_cGradeID != null)
{
sqlStr += "and pus_cGradeID='" + infoObject.pus_cGradeID.Value.ToString() + "'" + Environment.NewLine;
}
if (infoObject.pus_cCardUserID != null)
{
sqlStr += "and pus_cCardUserID='" + infoObject.pus_cCardUserID.Value.ToString() + "'" + Environment.NewLine;
}
if (infoObject.pus_cClassID != null)
{
sqlStr += "and pus_cClassID='" + infoObject.pus_cClassID.Value.ToString() + "'" + Environment.NewLine;
}
if (infoObject.pus_iWeek != null)
{
sqlStr += "and pus_iWeek=" + infoObject.pus_iWeek.Value.ToString() + "" + Environment.NewLine;
}
db.ExecuteCommand(sqlStr, new object[] { });
PaymentUDGeneralSetting_pus insertData = Common.General.CopyObjectValue<PaymentUDGeneralSetting_pus_Info, PaymentUDGeneralSetting_pus>(infoObject);
db.PaymentUDGeneralSetting_pus.InsertOnSubmit(insertData);
db.SubmitChanges();
returnInfo.boolValue = true;
}
}
catch (Exception Ex)
{
returnInfo.messageText = Ex.Message;
}
}
return returnInfo;
}
public ReturnValueInfo BatchSyncAccountDetail(List<PreConsumeRecord_pcs_Info> listUpdatePreCost, List<PreConsumeRecord_pcs_Info> listDelPreCost, List<ConsumeRecord_csr_Info> listConsume)
{
ReturnValueInfo rvInfo = new ReturnValueInfo();
try
{
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
//db.Connection.Open();
//db.Transaction = db.Connection.BeginTransaction();
List<CardUserAccountDetail_cuad> listUserAccount = new List<CardUserAccountDetail_cuad>();
List<SystemAccountDetail_sad> listSysAccount = new List<SystemAccountDetail_sad>();
List<PreConsumeRecord_pcs> listDelPreCost_DB = new List<PreConsumeRecord_pcs>();
#region 准备需要删除的重复扣费列表
if (listDelPreCost != null && listDelPreCost.Count > 0)
{
foreach (var item in listDelPreCost)
{
if (item == null)
continue;
PreConsumeRecord_pcs preCostDel = db.PreConsumeRecord_pcs.Where(x => x.pcs_cRecordID == item.pcs_cRecordID).FirstOrDefault();
if (preCostDel != null)
{
listDelPreCost_DB.Add(preCostDel);
}
}
}
#endregion
#region 归档处理实际卡消费记录
foreach (ConsumeRecord_csr_Info consumeItem in listConsume)
{
CardUserAccount_cua account = db.CardUserAccount_cua.Where(x => x.cua_cCUSID == consumeItem.csr_cCardUserID).FirstOrDefault();
ConsumeRecord_csr consumeInfo = db.ConsumeRecord_csr.Where(x => x.csr_cRecordID == consumeItem.csr_cRecordID).FirstOrDefault();
consumeInfo.csr_lIsSettled = consumeItem.csr_lIsSettled;
consumeInfo.csr_dSettleTime = consumeItem.csr_dSettleTime;
string strFlowType = string.Empty;//公用消费类型
CardUserAccountDetail_cuad userAccountDetail = new CardUserAccountDetail_cuad();
userAccountDetail.cuad_cRecordID = Guid.NewGuid();
userAccountDetail.cuad_cConsumeID = consumeInfo.csr_cRecordID;
userAccountDetail.cuad_cCUAID = account.cua_cRecordID;
userAccountDetail.cuad_fFlowMoney = consumeItem.csr_fConsumeMoney;
if (consumeItem.csr_cConsumeType == Common.DefineConstantValue.ConsumeMachineType.StuSetmeal.ToString())
{
strFlowType = Common.DefineConstantValue.ConsumeMoneyFlowType.SetMealCost.ToString();
}
else
{
strFlowType = Common.DefineConstantValue.ConsumeMoneyFlowType.FreeMealCost.ToString();
}
userAccountDetail.cuad_cFlowType = strFlowType;
userAccountDetail.cuad_cOpt = "sys";
userAccountDetail.cuad_dOptTime = DateTime.Now;
listUserAccount.Add(userAccountDetail);
SystemAccountDetail_sad sysAccountDetail = new SystemAccountDetail_sad();
sysAccountDetail.sad_cRecordID = Guid.NewGuid();
sysAccountDetail.sad_cConsumeID = consumeInfo.csr_cRecordID;
sysAccountDetail.sad_cDesc = Common.DefineConstantValue.GetMoneyFlowDesc(strFlowType);
sysAccountDetail.sad_cFLowMoney = consumeItem.csr_fConsumeMoney;
sysAccountDetail.sad_cFlowType = strFlowType;
sysAccountDetail.sad_cOpt = "sys";
sysAccountDetail.sad_dOptTime = DateTime.Now;
listSysAccount.Add(sysAccountDetail);
}
#endregion
#region 处理预扣费记录，更新已结算部分数据与未结算部分数据，未结算部分等待下次充值时补扣
foreach (PreConsumeRecord_pcs_Info preCostItem in listUpdatePreCost)
{
PreConsumeRecord_pcs preCostInfo = db.PreConsumeRecord_pcs.Where(x => x.pcs_cRecordID == preCostItem.pcs_cRecordID).FirstOrDefault();
if (preCostInfo != null)
{
preCostInfo.pcs_cSourceID = preCostItem.pcs_cSourceID;
preCostInfo.pcs_cConsumeType = preCostItem.pcs_cConsumeType;
preCostInfo.pcs_lIsSettled = preCostItem.pcs_lIsSettled;
preCostInfo.pcs_dSettleTime = preCostItem.pcs_dSettleTime;
preCostInfo.pcs_fCost = Math.Abs(preCostItem.pcs_fCost);
preCostInfo.pcs_cAdd = preCostItem.pcs_cAdd;
preCostInfo.pcs_dAddDate = preCostItem.pcs_dAddDate;
}
else
{
throw new Exception("预付款记录丢失。" + preCostItem.pcs_cRecordID.ToString());
}
}
#endregion
#region 更新用户账户的余额以及同步时间【停用，改用SQL处理】
/*1、取出用户最新的卡消费记录
* 2、取出用户最新的转账或充值记录
* 3、对比两者时间，取较新时间的记录去到用户的余额
*/
//var groupConsumeRecord = db.ConsumeRecord_csr.GroupBy(x => x.csr_cCardUserID);
//foreach (var itemUserConsumeGroup in groupConsumeRecord)
//{
// Guid userID = itemUserConsumeGroup.Key;
// List<ConsumeRecord_csr> listUserRecord = itemUserConsumeGroup.OrderByDescending(x => x.csr_dConsumeDate).ToList();
// if (listUserRecord != null && listUserRecord.Count > 0)
// {
// ConsumeRecord_csr record = listUserRecord.FirstOrDefault();//最新的消费记录
// if (record != null)
// {
// decimal fCardCostCurrentBalance = record.csr_fCardBalance;
// decimal fAdvanceRecharge = decimal.Zero;//透支额度
// RechargeRecord_rcr advanceRecharge = db.RechargeRecord_rcr.Where(x =>
// x.rcr_cUserID == record.csr_cCardUserID
// && x.rcr_cRechargeType == Common.DefineConstantValue.ConsumeMoneyFlowType.Recharge_AdvanceMoney.ToString()
// ).OrderByDescending(x => x.rcr_dRechargeTime).FirstOrDefault();
// if (advanceRecharge != null)
// {
// fAdvanceRecharge = advanceRecharge.rcr_fRechargeMoney;
// }
// //找出最后消费记录后，有充值情况出现的，需使用此充值后结果
// List<RechargeRecord_rcr> listCardRecharge = db.RechargeRecord_rcr.Where(x =>
// x.rcr_cUserID == record.csr_cCardUserID
// && x.rcr_dRechargeTime >= record.csr_dConsumeDate
// ).OrderByDescending(x => x.rcr_dRechargeTime).ToList();//以充值时间倒序，取出最新的充值列表
// listCardRecharge = listCardRecharge.Where(x =>
// x.rcr_cRechargeType != Common.DefineConstantValue.ConsumeMoneyFlowType.AdvanceMealCost.ToString()
// && x.rcr_cRechargeType != Common.DefineConstantValue.ConsumeMoneyFlowType.FreeMealCost.ToString()
// && x.rcr_cRechargeType != Common.DefineConstantValue.ConsumeMoneyFlowType.HedgeFund.ToString()
// && x.rcr_cRechargeType != Common.DefineConstantValue.ConsumeMoneyFlowType.IndeterminateCost.ToString()
// && x.rcr_cRechargeType != Common.DefineConstantValue.ConsumeMoneyFlowType.NewCardCost.ToString()
// && x.rcr_cRechargeType != Common.DefineConstantValue.ConsumeMoneyFlowType.Recharge_AdvanceMoney.ToString()
// && x.rcr_cRechargeType != Common.DefineConstantValue.ConsumeMoneyFlowType.ReplaceCardCost.ToString()
// && x.rcr_cRechargeType != Common.DefineConstantValue.ConsumeMoneyFlowType.SetMealCost.ToString()).ToList();
// RechargeRecord_rcr cardRecharge = listCardRecharge.FirstOrDefault();
// if (cardRecharge != null)
// {
// if (record.csr_dConsumeDate < cardRecharge.rcr_dRechargeTime)
// {
// //如果充值记录存在，且充值时间比消费时间新，则以充值后余额为最后余额值
// fCardCostCurrentBalance = cardRecharge.rcr_fBalance;
// }
// }
// CardUserAccount_cua userAccount = db.CardUserAccount_cua.Where(x => x.cua_cCUSID == record.csr_cCardUserID).FirstOrDefault();
// if (userAccount != null)
// {
// decimal fCurrentBalance = fCardCostCurrentBalance - fAdvanceRecharge;
// userAccount.cua_fCurrentBalance = fCurrentBalance;
// userAccount.cua_dLastSyncTime = DateTime.Now;
// }
// }
// }
// else
// {
// continue;
// }
//}
#endregion
db.Connection.Open();
db.Transaction = db.Connection.BeginTransaction();
try
{
if (listConsume.Count > 0)
{
if (listUserAccount.Count > 0)
{
db.CardUserAccountDetail_cuad.InsertAllOnSubmit(listUserAccount);
db.SystemAccountDetail_sad.InsertAllOnSubmit(listSysAccount);
}
}
if (listDelPreCost_DB.Count > 0)
{
db.PreConsumeRecord_pcs.DeleteAllOnSubmit(listDelPreCost_DB);
}
db.SubmitChanges();
#region 同步学生账户金额信息
string strStuSycnAccount = @"declare @tb_CardConsumeDetail table (UserID uniqueidentifier,ConsumeDate datetime,CardBalance decimal(18,2))
declare @tb_CardRechargeDetail table (UserID uniqueidentifier,RechargeDate datetime,CardBalance decimal(18,2))
insert into @tb_CardConsumeDetail
select distinct csr_cCardUserID,csr_dConsumeDate,csr_fCardBalance
from ConsumeRecord_csr with(nolock)
join(
select UserID=csr_cCardUserID,MaxDate=MAX(csr_dConsumeDate)
from ConsumeRecord_csr with(nolock)
join vie_AllStudentCardUserInfos on csr_cCardUserID=cus_cRecordID
group by csr_cCardUserID)A
on csr_cCardUserID=UserID and csr_dConsumeDate=MaxDate
insert into @tb_CardRechargeDetail
select distinct rcr_cUserID,rcr_dRechargeTime,rcr_fBalance
from RechargeRecord_rcr with(nolock)
join(
select UserID=rcr_cUserID,MaxDate=MAX(rcr_dRechargeTime)
from RechargeRecord_rcr with(nolock)
join vie_AllStudentCardUserInfos on rcr_cUserID=cus_cRecordID and rcr_dRechargeTime is not null
and rcr_cRechargeType<>'Recharge_AdvanceMoney'
group by rcr_cUserID)A
on rcr_cUserID=UserID and rcr_dRechargeTime=MaxDate
update CardUserAccount_cua set cua_fCurrentBalance=StuBalance,cua_dLastSyncTime=GETDATE()
from CardUserAccount_cua Acc with(nolock)
join(
select cua_cRecordID,StuBalance=(case when (A.UserID is not null and B.UserID is not null) then
(case when ConsumeDate>=RechargeDate then A.CardBalance else B.CardBalance end)
when (A.UserID is not null and B.UserID is null) then A.CardBalance
when (A.UserID is null and B.UserID is not null) then B.CardBalance
else 0 end)-cua_fSysAdvance
from vie_AllStudentCardUserInfos
left join @tb_CardConsumeDetail A on A.UserID=cus_cRecordID
left join @tb_CardRechargeDetail B on B.UserID=cus_cRecordID
)TT on Acc.cua_cRecordID=TT.cua_cRecordID
select * from vie_AllStudentCardUserInfos";
db.ExecuteCommand(strStuSycnAccount, new object[] { });
#endregion
#region 同步教师账户金额信息
string strStaffSycnAccount = @"declare @tb_CardConsumeDetail table (UserID uniqueidentifier,ConsumeDate datetime,CardBalance decimal(18,2))
declare @tb_CardRechargeDetail table (UserID uniqueidentifier,RechargeDate datetime,CardBalance decimal(18,2))
insert into @tb_CardConsumeDetail
select distinct csr_cCardUserID,csr_dConsumeDate,csr_fCardBalance
from ConsumeRecord_csr with(nolock)
join(
select UserID=csr_cCardUserID,MaxDate=MAX(csr_dConsumeDate)
from ConsumeRecord_csr with(nolock)
join CardUserMaster_cus on csr_cCardUserID=cus_cRecordID
and cus_cIdentityNum='STAFF'
group by csr_cCardUserID)A
on csr_cCardUserID=UserID and csr_dConsumeDate=MaxDate
insert into @tb_CardRechargeDetail
select distinct rcr_cUserID,rcr_dRechargeTime,rcr_fBalance
from RechargeRecord_rcr with(nolock)
join(
select UserID=rcr_cUserID,MaxDate=MAX(rcr_dRechargeTime)
from RechargeRecord_rcr with(nolock)
join CardUserMaster_cus on rcr_cUserID=cus_cRecordID
and cus_cIdentityNum='STAFF'
and rcr_dRechargeTime is not null and rcr_cRechargeType<>'Recharge_AdvanceMoney'
group by rcr_cUserID)A
on rcr_cUserID=UserID and rcr_dRechargeTime=MaxDate
update CardUserAccount_cua set cua_fCurrentBalance=StuBalance,cua_dLastSyncTime=GETDATE()
from CardUserAccount_cua Acc with(nolock)
join(
select cua_cRecordID,StuBalance=(case when (A.UserID is not null and B.UserID is not null) then
(case when ConsumeDate>=RechargeDate then A.CardBalance else B.CardBalance end)
when (A.UserID is not null and B.UserID is null) then A.CardBalance
when (A.UserID is null and B.UserID is not null) then B.CardBalance
else 0 end)-cua_fSysAdvance--,ConsumeDate,A.CardBalance,RechargeDate,B.CardBalance
from CardUserMaster_cus with(nolock)
join CardUserAccount_cua with(nolock) on cua_cCUSID=cus_cRecordID
left join @tb_CardConsumeDetail A on A.UserID=cus_cRecordID
left join @tb_CardRechargeDetail B on B.UserID=cus_cRecordID
where cus_cIdentityNum='STAFF'
)Temp
on Acc.cua_cRecordID=Temp.cua_cRecordID
select * from CardUserAccount_cua
join CardUserMaster_cus on cus_cRecordID=cua_cCUSID
and cus_cIdentityNum='STAFF'";
db.ExecuteCommand(strStaffSycnAccount, new object[] { });
#endregion
#region 临时清除午餐误扣费
string strChkLunch = @"update PreConsumeRecord_pcs set pcs_lIsSettled=1,pcs_dSettleTime=GETDATE()
where pcs_cRecordID in
(
select pcs_cRecordID from PreConsumeRecord_pcs
where pcs_lIsSettled = 0
and pcs_cConsumeType = '" + Common.DefineConstantValue.ConsumeMoneyFlowType.SetMealCost.ToString() + "'" + Environment.NewLine;
strChkLunch += "and pcs_dConsumeDate between '" + DateTime.Now.ToString("yyyy-MM-dd 09:00") + "' and '" + DateTime.Now.ToString("yyyy-MM-dd 13:00") + "'" + Environment.NewLine;
strChkLunch += @"and pcs_fCost > 2
and pcs_cUserID in
(
select csr_cCardUserID from ConsumeRecord_csr
where csr_dConsumeDate between '" + DateTime.Now.ToString("yyyy-MM-dd 09:00") + "' and '" + DateTime.Now.ToString("yyyy-MM-dd 13:59") + "'" + Environment.NewLine;
strChkLunch += "and csr_cConsumeType = '" + Common.DefineConstantValue.ConsumeMachineType.StuSetmeal.ToString() + "' and csr_cMealType = '" + Common.DefineConstantValue.MealType.Lunch.ToString() + "'))" + Environment.NewLine;
db.ExecuteCommand(strChkLunch, new object[] { });
#endregion
#region 临时清除晚餐误扣费
string strChkDinner = @"update PreConsumeRecord_pcs set pcs_lIsSettled=1,pcs_dSettleTime=GETDATE()
where pcs_cRecordID in
(
select pcs_cRecordID from PreConsumeRecord_pcs
where pcs_lIsSettled = 0
and pcs_cConsumeType = '" + Common.DefineConstantValue.ConsumeMoneyFlowType.SetMealCost.ToString() + "'" + Environment.NewLine;
strChkDinner += "and pcs_dConsumeDate between '" + DateTime.Now.ToString("yyyy-MM-dd 13:30") + "' and '" + DateTime.Now.ToString("yyyy-MM-dd 23:59") + "'" + Environment.NewLine;
strChkDinner += @"and pcs_fCost > 2
and pcs_cUserID in
(
select csr_cCardUserID from ConsumeRecord_csr
where csr_dConsumeDate between '" + DateTime.Now.ToString("yyyy-MM-dd 15:00") + "' and '" + DateTime.Now.ToString("yyyy-MM-dd 23:59") + "'" + Environment.NewLine;
strChkDinner += "and csr_cConsumeType = '" + Common.DefineConstantValue.ConsumeMachineType.StuSetmeal.ToString() + "' and csr_cMealType = '" + Common.DefineConstantValue.MealType.Supper.ToString() + "'))" + Environment.NewLine;
db.ExecuteCommand(strChkDinner, new object[] { });
#endregion
if (listConsume.Count < 1 && listUpdatePreCost.Count < 1)
{
rvInfo.boolValue = false;
rvInfo.messageText = "没有可用的数据。";
}
else
{
rvInfo.boolValue = true;
}
}
catch (Exception exx)
{
db.Transaction.Rollback();
db.Connection.Close();
throw exx;
}
db.Transaction.Commit();
db.Connection.Close();
}
}
catch (Exception ex)
{
rvInfo.isError = true;
rvInfo.messageText = ex.Message;
}
return rvInfo;
}
public ReturnValueInfo UpdateBatchRecord(List<BlacklistChangeRecord_blc_Info> listRecords)
{
ReturnValueInfo rvInfo = new ReturnValueInfo();
try
{
if (listRecords == null || (listRecords != null && listRecords.Count < 1))
{
rvInfo.messageText = Common.DefineConstantValue.SystemMessageText.strMessageText_E_ObjectNull;
rvInfo.isError = true;
return rvInfo;
}
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
var groupCardNo = listRecords.GroupBy(x => x.blc_iCardNo);
if (groupCardNo != null)
{
StringBuilder sbSQL = new StringBuilder();
string strSQLTemp = "UPDATE BlacklistChangeRecord_blc SET blc_lIsFinished = 1, blc_dFinishDate =GETDATE() WHERE 1=1 AND blc_lIsFinished = 0";
foreach (var itemCardNo in groupCardNo)
{
int iCardNo = itemCardNo.Key;
BlacklistChangeRecord_blc_Info blInfo = itemCardNo.OrderByDescending(x => x.blc_dAddDate).FirstOrDefault();
if (blInfo != null)
{
StringBuilder sbSubSQL = new StringBuilder();
sbSubSQL.AppendLine(strSQLTemp);
sbSubSQL.AppendLine("AND blc_iCardNo = " + iCardNo);
sbSubSQL.AppendLine("AND blc_dAddDate <= '" + blInfo.blc_dAddDate.ToString() + "';");
sbSQL.AppendLine(sbSubSQL.ToString());
}
}
if (!string.IsNullOrEmpty(sbSQL.ToString()))
{
int iRes = db.ExecuteCommand(sbSQL.ToString(), new object[] { });
}
}
}
rvInfo.boolValue = true;
}
catch (Exception ex)
{
rvInfo.isError = true;
rvInfo.messageText = ex.Message;
}
return rvInfo;
}