public List<BlacklistChangeRecord_blc_Info> AllRecords()
{
List<BlacklistChangeRecord_blc_Info> listRecord = null;
try
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine(" SELECT blc_cRecordID,blc_iCardNo,blc_cOperation,blc_cOptReason,blc_lIsFinished,blc_cAdd ");
sbSQL.AppendLine(" ,blc_dAddDate,blc_dFinishDate,cus_cIdentityNum as IdentityNum ");
sbSQL.AppendLine(" FROM BlacklistChangeRecord_blc WITH(NOLOCK) ");
sbSQL.AppendLine(" JOIN UserCardPair_ucp WITH(NOLOCK) ON ucp_iCardNo=blc_iCardNo ");
sbSQL.AppendLine(" JOIN CardUserMaster_cus WITH(NOLOCK) ON cus_cRecordID=ucp_cCUSID ");
sbSQL.AppendLine(" WHERE blc_lIsFinished=0 ");
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
IEnumerable<BlacklistChangeRecord_blc_Info> query = db.ExecuteQuery<BlacklistChangeRecord_blc_Info>(sbSQL.ToString(), new object[] { });
if (query != null)
{
listRecord = query.ToList<BlacklistChangeRecord_blc_Info>();
}
}
}
catch (Exception ex)
{
throw ex;
}
return listRecord;
}
public List<MealBookingHistory_mbh_Info> AllRecords()
{
List<MealBookingHistory_mbh_Info> listPairInfo = null;
try
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine("SELECT ");
sbSQL.AppendLine("* FROM MealBookingHistory_mbh WHERE 1 = 1");
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
IEnumerable<MealBookingHistory_mbh_Info> query = db.ExecuteQuery<MealBookingHistory_mbh_Info>(sbSQL.ToString(), new object[] { });
if (query != null)
{
listPairInfo = query.ToList<MealBookingHistory_mbh_Info>();
}
}
}
catch (Exception ex)
{
throw ex;
}
return listPairInfo;
}
public List<Model.HHZX.ConsumerDevice.ConsumeMachineMaster_cmm_Info> AllRecords()
{
List<ConsumeMachineMaster_cmm_Info> listRecordInfo = null;
try
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine("SELECT TOP");
sbSQL.AppendLine(Common.DefineConstantValue.ListRecordMaxCount.ToString());
sbSQL.AppendLine("* FROM ConsumeMachineMaster_cmm WHERE 1 = 1");
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
IEnumerable<ConsumeMachineMaster_cmm_Info> query = db.ExecuteQuery<ConsumeMachineMaster_cmm_Info>(sbSQL.ToString(), new object[] { });
if (query != null)
{
listRecordInfo = query.ToList<ConsumeMachineMaster_cmm_Info>();
}
}
}
catch (Exception ex)
{
throw ex;
}
return listRecordInfo;
}
public List<Sys_UserMaster_usm_Info> SearchRecords(Sys_UserMaster_usm_Info infoObj)
{
List<Sys_UserMaster_usm_Info> listReturn = null;
try
{
string strSql = "select * from dbo.Sys_UserMaster_usm where 1=1 ";
if(infoObj != null)
{
if (!String.IsNullOrEmpty(infoObj.usm_cUserLoginID))
{
strSql += " and usm_cUserLoginID like %'" + infoObj.usm_cUserLoginID + "'% ";
}
}
IEnumerable<Sys_UserMaster_usm_Info> infos = null;
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
infos = db.ExecuteQuery<Sys_UserMaster_usm_Info>(strSql, new object[] { });
if (infos != null)
{
listReturn = infos.ToList();
}
}
}
catch
{
throw;
}
return listReturn;
}
public List<UserCardPair_ucp_Info> AllRecords()
{
List<UserCardPair_ucp_Info> listPairInfo = null;
try
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine("SELECT ");
sbSQL.AppendLine("* FROM UserCardPair_ucp WHERE 1 = 1");
sbSQL.AppendLine("AND ucp_lIsActive = 1");
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
IEnumerable<UserCardPair_ucp_Info> query = db.ExecuteQuery<UserCardPair_ucp_Info>(sbSQL.ToString(), new object[] { });
if (query != null)
{
listPairInfo = query.ToList<UserCardPair_ucp_Info>();
}
}
}
catch (Exception ex)
{
throw ex;
}
return listPairInfo;
}
public List<CardUserAccountDetail_cuad_Info> AllRecords()
{
List<CardUserAccountDetail_cuad_Info> listRecord = null;
try
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine("SELECT ");
sbSQL.AppendLine("* FROM CardUserAccountDetail_cuad WHERE 1 = 1");
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
IEnumerable<CardUserAccountDetail_cuad_Info> query = db.ExecuteQuery<CardUserAccountDetail_cuad_Info>(sbSQL.ToString(), new object[] { });
if (query != null)
{
listRecord = query.ToList<CardUserAccountDetail_cuad_Info>();
}
}
}
catch (Exception ex)
{
throw ex;
}
return listRecord;
}
public List<DeductionBalance_Info> SearchRecords(Model.IModel.IModelObject searchCondition)
{
List<DeductionBalance_Info> returnList = new List<DeductionBalance_Info>();
try
{
DeductionBalance_Info searchInfo = searchCondition as DeductionBalance_Info;
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine("select *,cua_fCurrentBalance as Balance ");
sbSQL.AppendLine("from dbo.PreConsumeRecord_pcs");
sbSQL.AppendLine("join dbo.CardUserAccount_cua on cua_cRecordID = pcs_cAccountID");
sbSQL.AppendLine("join dbo.CardUserMaster_cus on cus_cRecordID = cua_cCUSID");
sbSQL.AppendLine("join dbo.ClassMaster_csm on csm_cRecordID = cus_cClassID");
sbSQL.AppendLine("join dbo.UserCardPair_ucp on ucp_cCUSID = cus_cRecordID and ucp_dReturnTime is null");
sbSQL.AppendLine(" where pcs_cConsumeType = 'SetMealCost' and pcs_lIsSettled = 1");
if (searchInfo != null)
{
if (searchInfo.ClassID != Guid.Empty)
{
sbSQL.AppendLine("AND csm_cRecordID ='" + searchInfo.ClassID.ToString() + "'");
}
if (searchInfo.CardNo > 0)
{
sbSQL.AppendLine("AND ucp_iCardNo ='" + searchInfo.CardNo.ToString() + "'");
}
if (!String.IsNullOrEmpty(searchInfo.ChaName))
{
sbSQL.AppendLine("AND cus_cChaName like N'%" + searchInfo.ChaName.ToString() + "%'");
}
if (!String.IsNullOrEmpty(searchInfo.StudentID))
{
sbSQL.AppendLine("AND cus_cStudentID ='" + searchInfo.StudentID.ToString() + "'");
}
sbSQL.AppendLine("AND pcs_dConsumeDate between '" + searchInfo.FromTime.ToString("yyyy/MM/dd") + "' and '" + searchInfo.ToTime.ToString("yyyy/MM/dd") + "'");
sbSQL.AppendLine("order by pcs_dConsumeDate desc");
}
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
IEnumerable<DeductionBalance_Info> query = db.ExecuteQuery<DeductionBalance_Info>(sbSQL.ToString(), new object[] { });
if (query != null)
{
returnList = query.ToList<DeductionBalance_Info>();
}
}
}
catch
{
}
return returnList;
}
/// <summary>
/// 取得日志
/// </summary>
/// <param name="p_strIP">IP</param>
/// <param name="p_strSystemName">系统名</param>
/// <param name="p_strModel">模组</param>
/// <param name="p_strType">类型</param>
/// <param name="p_strOperator">操作人</param>
/// <returns></returns>
public List<LogDetail_lgd_Info> GetLogs(string p_strIP, string p_strSystemName, string p_strModel, string p_strType, string p_strOperator)
{
StringBuilder l_strSQL = new StringBuilder();
l_strSQL.AppendLine("Select Top 200 lgd_iID,");
l_strSQL.AppendLine("lgd_cIpAddr,");
l_strSQL.AppendLine("lgd_cSysName,");
l_strSQL.AppendLine("lgd_cClassMethodName,");
l_strSQL.AppendLine("lgd_cLogType,");
l_strSQL.AppendLine("lgd_cLogMessage,");
l_strSQL.AppendLine("lgd_cRemark,");
l_strSQL.AppendLine("lgd_cOperator,");
l_strSQL.AppendLine("lgd_dOperateDateTime");
l_strSQL.AppendLine("From LogDetail_lgd");
l_strSQL.AppendLine("Where 1=1");
if (p_strIP != "")
{
l_strSQL.AppendLine("And lgd_cIpAddr = '" + p_strIP + "'");
}
if (p_strSystemName != "")
{
l_strSQL.AppendLine("And lgd_cSysName = '" + p_strSystemName + "'");
}
if (p_strModel != "")
{
l_strSQL.AppendLine("And lgd_cClassMethodName = '" + p_strModel + "'");
}
if (p_strType != "")
{
l_strSQL.AppendLine("And lgd_cLogType = '" + p_strType + "'");
}
if (p_strOperator != "")
{
l_strSQL.AppendLine("And lgd_cOperator = '" + p_strOperator + "'");
}
l_strSQL.AppendLine("Order By lgd_dOperateDateTime Desc");
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
return db.ExecuteQuery<LogDetail_lgd_Info>(l_strSQL.ToString(), new object[] { }).ToList();
}
}
public List<DepartmentMaster_dpm_Info> AllRecords()
{
List<DepartmentMaster_dpm_Info> listReturn = null;
try
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine("SELECT * FROM DepartmentMaster_dpm");
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
listReturn = db.ExecuteQuery<DepartmentMaster_dpm_Info>(sbSQL.ToString(), new object[] { }).ToList();
}
}
catch (Exception ex)
{
throw ex;
}
return listReturn;
}
public decimal GetOrtherIncome(DateTime dtQuery)
{
decimal fReturn = decimal.Zero;
try
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine("SELECT rid_fOrtherIncome FROM ReportIncomeDetail_rid");
sbSQL.AppendLine("WHERE rid_cDateNo = '" + dtQuery.ToString("yyyy-MM-dd") + "'");
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
decimal fSource = db.ExecuteQuery<decimal>(sbSQL.ToString(), new object[] { }).FirstOrDefault();
fReturn = fSource;
}
}
catch (Exception ex)
{
return decimal.Zero;
}
return fReturn;
}
public List<PreConsumeRecord_pcs_Info> SearchRecords(IModelObject searchCondition)
{
List<PreConsumeRecord_pcs_Info> listRecordInfo = null;
try
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine("SELECT pcs_cRecordID,pcs_cUserID,pcs_cAccountID,pcs_cSourceID,pcs_fCost,pcs_cConsumeType");
sbSQL.AppendLine(",pcs_dConsumeDate,pcs_lIsSettled,pcs_dSettleTime,pcs_cAdd,pcs_dAddDate, mbh_cMealType as MealType");
sbSQL.AppendLine("from PreConsumeRecord_pcs with(nolock)");
sbSQL.AppendLine("LEFT JOIN MealBookingHistory_mbh with(nolock) on mbh_cRecordID = pcs_cSourceID ");
PreConsumeRecord_pcs_Info searchInfo = searchCondition as PreConsumeRecord_pcs_Info;
if (searchInfo != null)
{
sbSQL.AppendLine("where 1= 1");
if (searchInfo.pcs_cUserID != Guid.Empty)
sbSQL.AppendLine("AND pcs_cUserID ='" + searchInfo.pcs_cUserID + "'");
if (!string.IsNullOrEmpty(searchInfo.pcs_cConsumeType))
sbSQL.AppendLine("AND pcs_cConsumeType ='" + searchInfo.pcs_cConsumeType + "'");
if (searchInfo.pcs_cAccountID != Guid.Empty)
sbSQL.AppendLine("AND pcs_cAccountID ='" + searchInfo.pcs_cAccountID.ToString() + "'");
if (searchInfo.pcs_dConsumeDate != DateTime.MinValue)
sbSQL.AppendLine("AND pcs_dConsumeDate ='" + searchInfo.pcs_dConsumeDate.ToString("yyyy-MM-dd HH:mm:ss") + "'");
if (searchInfo.pcs_fCost != 0)
sbSQL.AppendLine("AND pcs_fCost =" + searchInfo.pcs_fCost);
}
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
db.CommandTimeout = 10 * 60000;
IEnumerable<PreConsumeRecord_pcs_Info> query = db.ExecuteQuery<PreConsumeRecord_pcs_Info>(sbSQL.ToString(), new object[] { });
if (query != null)
{
listRecordInfo = query.ToList<PreConsumeRecord_pcs_Info>();
}
}
}
catch (Exception ex)
{
throw ex;
}
return listRecordInfo;
}
public List<Model.HHZX.ComsumeAccount.CardUserAccount_cua_Info> SearchRecords(Model.IModel.IModelObject searchCondition)
{
List<CardUserAccount_cua_Info> cuaList = new List<CardUserAccount_cua_Info>();
try
{
CardUserMaster_cus_Info searchInfo = searchCondition as CardUserMaster_cus_Info;
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine(@"select cus_cStudentID,cus_cChaName as cua_cUserName
,ISNULL(csm_cClassName,dpm_cName) as cua_cClassName
,ISNULL(cua_fCurrentBalance,0.00) as cua_fCurrentBalance
,cua_dLastSyncTime
,ISNULL(csr_fCardBalance,0.00) as cua_dCardBlance
from CardUserMaster_cus with(nolock)
left join CardUserAccount_cua with(nolock) on cus_cRecordID = cua_cCUSID
left join ClassMaster_csm with(nolock) on csm_cRecordID = cus_cClassID
left join DepartmentMaster_dpm with(nolock) on dpm_RecordID = cus_cClassID
left join
(
select * from ConsumeRecord_csr with(nolock) inner join
(select MAX(csr_dConsumeDate) as ConsumeDate,csr_cCardUserID as UserID from ConsumeRecord_csr with(nolock)
where csr_lIsSettled = 1
group by csr_cCardUserID) as MaxTB
on csr_cCardUserID = UserID and csr_dConsumeDate = ConsumeDate
) as CosumeTB on cus_cRecordID = UserID");
sbSQL.AppendLine("where 1=1 and cua_fCurrentBalance is not null");
if (searchInfo != null)
{
if (searchInfo.cus_cClassID != Guid.Empty)
{
sbSQL.AppendLine("AND cus_cClassID ='" + searchInfo.cus_cClassID.ToString() + "'");
}
if (!String.IsNullOrEmpty(searchInfo.cus_cStudentID))
{
sbSQL.AppendLine("AND cus_cStudentID like '%" + searchInfo.cus_cStudentID + "%'");
}
if (!String.IsNullOrEmpty(searchInfo.cus_cChaName))
{
sbSQL.AppendLine("AND cus_cChaName like N'%" + searchInfo.cus_cChaName + "%'");
}
}
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
db.CommandTimeout = 10 * 60000;
IEnumerable<CardUserAccount_cua_Info> query = db.ExecuteQuery<CardUserAccount_cua_Info>(sbSQL.ToString(), new object[] { });
if (query != null)
{
cuaList = query.ToList<CardUserAccount_cua_Info>();
}
}
}
catch
{
}
return cuaList;
}
public bool UpdateRecord(Sys_RoleMaster_rlm_Info infoObject)
{
string sqlString = string.Empty;
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
Sys_RoleMaster_rlm rlm = new Sys_RoleMaster_rlm();
Sys_RoleMaster_rlm_Info info = new Sys_RoleMaster_rlm_Info();
try
{
rlm = db.Sys_RoleMaster_rlm.SingleOrDefault(t => t.rlm_iRecordID == infoObject.rlm_iRecordID);
for (int i = 0; i < rlm.Sys_UserRoles_usr.Count; i++)
{
Sys_UserRoles_usr sta = rlm.Sys_UserRoles_usr[i];
db.Sys_UserRoles_usr.DeleteOnSubmit(sta);
}
info = Common.General.CopyObjectValue<Sys_RoleMaster_rlm, Sys_RoleMaster_rlm_Info>(rlm);
IEnumerable<Sys_RoleMaster_rlm_Info> IErlm = null;
IEnumerable<Sys_UserRoles_usr_Info> IEusr = null;
sqlString += "UPDATE Sys_RoleMaster_rlm" + Environment.NewLine;
sqlString += " SET rlm_cRoleID='" + infoObject.rlm_cRoleID + "'," + Environment.NewLine;
sqlString += " rlm_cRoleDesc='" + infoObject.rlm_cRoleDesc + "'," + Environment.NewLine;
sqlString += " rlm_cLast='" + infoObject.rlm_cLast + "'," + Environment.NewLine;
sqlString += " rlm_dLastDate='" + infoObject.rlm_dLastDate.Value.ToString(DefineConstantValue.gc_DateTimeFormat) + "'" + Environment.NewLine;
sqlString += " WHERE rlm_iRecordID='" + infoObject.rlm_iRecordID + "'";
IErlm = db.ExecuteQuery<Sys_RoleMaster_rlm_Info>(sqlString, new object[] { });
sqlString = string.Empty;
sqlString += "DELETE FROM Sys_UserRoles_usr WHERE usr_cRoleID='" + infoObject.rlm_cRoleID + "'";
IEusr = db.ExecuteQuery<Sys_UserRoles_usr_Info>(sqlString, new object[] { });
if (infoObject.userMasterList != null && infoObject.userMasterList.Count > 0)
{
for (int i = 0; i < infoObject.userMasterList.Count; i++)
{
sqlString = string.Empty;
Sys_UserMaster_usm_Info usmInfo = infoObject.userMasterList[i];
sqlString += "INSERT INTO Sys_UserRoles_usr" + Environment.NewLine;
sqlString += "(usr_cUserLoginID, usr_cRoleID)" + Environment.NewLine;
sqlString += " VALUES " + Environment.NewLine;
sqlString += "('" + usmInfo.usm_cUserLoginID + "','" + infoObject.rlm_cRoleID + "')";
IEusr = db.ExecuteQuery<Sys_UserRoles_usr_Info>(sqlString, new object[] { });
}
}
//if (rlm != null)
//{
// //usm = Common.General.SetDataToLingQEntity(usm, infoObject);//时间溢出
// rlm.rlm_cRoleDesc = infoObject.rlm_cRoleDesc;
// rlm.rlm_cLast = infoObject.rlm_cLast;
// rlm.rlm_dLastDate = infoObject.rlm_dLastDate.Value;
// if (infoObject.userMasterList != null && infoObject.userMasterList.Count > 0)
// {
// for (int i = 0; i < infoObject.userMasterList.Count; i++)
// {
// Sys_UserMaster_usm_Info usmInfo = infoObject.userMasterList[i];
// Sys_UserRoles_usr item = new Sys_UserRoles_usr();
// item.usr_cUserLoginID = usmInfo.usm_cUserLoginID;
// item.usr_cRoleID = infoObject.rlm_cRoleID;
// //courseitem.cum_cNumber;
// rlm.Sys_UserRoles_usrs.Add(item);
// //db.Sys_UserRoles_usrs.InsertOnSubmit(item);
// }
// }
// db.SubmitChanges();
//}
return true;
}
catch (Exception Ex)
{
throw Ex;
}
}
}
public List<CardUserAccountDetail_cuad_Info> SearchRecords(IModelObject searchCondition)
{
List<CardUserAccountDetail_cuad_Info> listRecordInfo = null;
try
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine("SELECT TOP");
sbSQL.AppendLine(Common.DefineConstantValue.ListRecordMaxCount.ToString());
sbSQL.AppendLine(@"[cuad_cRecordID]
,[cuad_cCUAID]
,[cuad_fFlowMoney]
,[cuad_cFlowType]
,[cuad_cConsumeID]
,[cuad_cOpt]
,[cuad_dOptTime]
,csr_dConsumeDate
,csr_cMachineID
,csr_cCardID
,cmm_iMacNo as MacNo");
sbSQL.AppendLine("FROM CardUserAccountDetail_cuad ");
sbSQL.AppendLine("LEFT JOIN ConsumeRecord_csr ON cuad_cConsumeID = csr_cRecordID");
sbSQL.AppendLine("LEFT JOIN ConsumeMachineMaster_cmm ON csr_cMachineID = cmm_cRecordID");
sbSQL.AppendLine("WHERE 1 = 1");
CardUserAccountDetail_cuad_Info searchInfo = searchCondition as CardUserAccountDetail_cuad_Info;
if (searchInfo != null)
{
if (searchInfo.cuad_cCUAID != Guid.Empty)
sbSQL.AppendLine("AND cuad_cCUAID ='" + searchInfo.cuad_cCUAID + "'");
if (searchInfo.cuad_fFlowMoney != 0)
sbSQL.AppendLine("AND cuad_cFlowMoney =" + searchInfo.cuad_fFlowMoney.ToString());
if (!string.IsNullOrEmpty(searchInfo.cuad_cFlowType))
sbSQL.AppendLine("AND cuad_cFlowType ='" + searchInfo.cuad_cFlowType + "'");
if (searchInfo.cuad_dOptTime != DateTime.MinValue)
sbSQL.AppendLine("AND cuad_dOptTime ='" + searchInfo.cuad_dOptTime.ToString("yyyy-MM-dd HH:mm") + "'");
if (searchInfo.cuad_cConsumeID != null)
sbSQL.AppendLine("AND cuad_cConsumeID ='" + searchInfo.cuad_cConsumeID + "'");
if (searchInfo.OptTime_From != null && searchInfo.OptTime_To != null)
{
sbSQL.AppendLine("AND cuad_dOptTime between '" + ((DateTime)(searchInfo.OptTime_From)).ToString("yyyy-MM-dd HH:mm") + "' and '" + ((DateTime)(searchInfo.OptTime_To)).ToString("yyyy-MM-dd HH:mm") + "'");
}
}
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
IEnumerable<CardUserAccountDetail_cuad_Info> query = db.ExecuteQuery<CardUserAccountDetail_cuad_Info>(sbSQL.ToString(), new object[] { });
if (query != null)
{
listRecordInfo = query.ToList<CardUserAccountDetail_cuad_Info>();
}
}
}
catch (Exception ex)
{
throw ex;
}
return listRecordInfo;
}
public ReturnValueInfo SetOrtherIncome(DateTime dtQuery, decimal fIncome)
{
ReturnValueInfo rvInfo = new ReturnValueInfo();
try
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine("if exists(select top 1 1 from dbo.ReportIncomeDetail_rid where rid_cDateNo='" + dtQuery.ToString("yyyy-MM-dd") + "')");
sbSQL.AppendLine("begin");
sbSQL.AppendLine("UPDATE ReportIncomeDetail_rid SET rid_fOrtherIncome=" + Math.Round(fIncome, 2).ToString());
sbSQL.AppendLine("WHERE rid_cDateNo = '" + dtQuery.ToString("yyyy-MM-dd") + "'");
sbSQL.AppendLine("end");
sbSQL.AppendLine("else");
sbSQL.AppendLine("begin");
sbSQL.AppendLine("insert into ReportIncomeDetail_rid(rid_cDateNo,rid_fHotIncome,rid_fOrtherIncome,rid_dRecDate)values(");
sbSQL.AppendLine("'" + dtQuery.ToString("yyyy-MM-dd") + "',0," + fIncome.ToString() + ",'" + dtQuery.ToString("yyyy-MM-dd HH:mm:ss") + "')");
sbSQL.AppendLine("end");
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
var res = db.ExecuteQuery(typeof(string), sbSQL.ToString(), new object[] { });
rvInfo.boolValue = true;
}
}
catch (Exception ex)
{
rvInfo.isError = true;
rvInfo.messageText = ex.Message;
return rvInfo;
}
return rvInfo;
}
public decimal GetOrtherIncome(DateTime dtBegin, DateTime dtEnd)
{
decimal fReturn = decimal.Zero;
try
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine("SELECT SUM(rid_fOrtherIncome) FROM ReportIncomeDetail_rid with(nolock)");
sbSQL.AppendLine("WHERE rid_dRecDate between '" + dtBegin.ToString("yyyy-MM-dd 00:00") + "' and '" + dtEnd.ToString("yyyy-MM-dd 23:59") + "'");
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
decimal fSource = db.ExecuteQuery<decimal>(sbSQL.ToString(), new object[] { }).FirstOrDefault();
fReturn = fSource;
}
}
catch (Exception ex)
{
return decimal.Zero;
}
return fReturn;
}
public List<Model.HHZX.ConsumerDevice.ConsumeMachineMaster_cmm_Info> SearchRecords(Model.IModel.IModelObject searchCondition)
{
List<ConsumeMachineMaster_cmm_Info> listRecordInfo = null;
try
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine("SELECT TOP");
sbSQL.AppendLine(Common.DefineConstantValue.ListRecordMaxCount.ToString());
sbSQL.AppendLine("* FROM ConsumeMachineMaster_cmm with(nolock) WHERE 1 = 1");
ConsumeMachineMaster_cmm_Info searchInfo = searchCondition as ConsumeMachineMaster_cmm_Info;
if (searchInfo != null)
{
if (searchInfo.cmm_cRecordID != Guid.Empty)
sbSQL.AppendLine("AND cmm_cRecordID ='" + searchInfo.cmm_cRecordID.ToString() + "'");
if (searchInfo.cmm_iMacNo > 0)
sbSQL.AppendLine("AND cmm_iMacNo ='" + searchInfo.cmm_iMacNo.ToString() + "'");
if (!String.IsNullOrEmpty(searchInfo.cmm_cIPAddr))
sbSQL.AppendLine("AND cmm_cIPAddr like '%" + searchInfo.cmm_cIPAddr.ToString() + "%'");
if (searchInfo.cmm_iPort > 0)
sbSQL.AppendLine("AND cmm_iPort like '%" + searchInfo.cmm_iPort.ToString() + "%'");
if (!String.IsNullOrEmpty(searchInfo.cmm_cMacName))
sbSQL.AppendLine("AND cmm_cMacName like '%" + searchInfo.cmm_cMacName.ToString() + "%'");
if (!String.IsNullOrEmpty(searchInfo.cmm_cUsageType))
sbSQL.AppendLine("AND cmm_cUsageType = '" + searchInfo.cmm_cUsageType.ToString() + "'");
if (!String.IsNullOrEmpty(searchInfo.cmm_cStatus))
sbSQL.AppendLine("AND cmm_cStatus = '" + searchInfo.cmm_cStatus.ToString() + "'");
}
sbSQL.AppendLine("order by cmm_iMacNo");
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
IEnumerable<ConsumeMachineMaster_cmm_Info> query = db.ExecuteQuery<ConsumeMachineMaster_cmm_Info>(sbSQL.ToString(), new object[] { });
if (query != null)
{
listRecordInfo = query.ToList<ConsumeMachineMaster_cmm_Info>();
}
}
}
catch (Exception ex)
{
throw ex;
}
return listRecordInfo;
}
public List<Model.HHZX.Report.PaymentBalance_Info> SearchRecords(Model.IModel.IModelObject searchCondition, bool lIsWithMac, bool lIsWithPreCost)
{
List<PaymentBalance_Info> returnList = new List<PaymentBalance_Info>();
try
{
PaymentBalance_Info info = searchCondition as PaymentBalance_Info;
StringBuilder sbSQL = new StringBuilder();
StringBuilder sbSQLMac = new StringBuilder();
StringBuilder sbSQLPreCost = new StringBuilder();
StringBuilder sbSQLMacWhere = new StringBuilder();
StringBuilder sbSQLPreCostWhere = new StringBuilder();
StringBuilder sbSQLTotalWhere = new StringBuilder();
sbSQLMac.AppendLine("SELECT");
sbSQLMac.AppendLine("cus_cStudentID,cus_cChaName,csr_fConsumeMoney,");
sbSQLMac.AppendLine("csr_dConsumeDate,");
sbSQLMac.AppendLine("(Convert(varchar,cmm_iMacNo)+'--'+MacTypeName) as MacNo,");
sbSQLMac.AppendLine("csr_fCardBalance, ");
sbSQLMac.AppendLine("case cus_cIdentityNum when 'STAFF' then ISNULL( dpm_cName,'')");
sbSQLMac.AppendLine("when 'STUDENT' then ISNULL( csm_cClassName,'') else '' end as csm_cClassName");
sbSQLMac.AppendLine(" FROM ConsumeRecord_csr ");
sbSQLMac.AppendLine("join dbo.CardUserMaster_cus on cus_cRecordID = csr_cCardUserID ");
sbSQLMac.AppendLine("left join dbo.ClassMaster_csm on csm_cRecordID = cus_cClassID ");
sbSQLMac.AppendLine("left join dbo.DepartmentMaster_dpm on dpm_RecordID = cus_cClassID ");
sbSQLMac.AppendLine(@"left join
(select *,
(case cmm_cUsageType
when 'StuSetmeal' then N'定餐机'
when 'StuPay' then N'加菜机'
when 'TeachPay' then N'教师机'
when 'HotWaterPay' then N'热水机'
when 'DrinkPay' then N'饮料机'
else ''end) as MacTypeName
from dbo.ConsumeMachineMaster_cmm) as ProFileCMM on cmm_cRecordID = csr_cMachineID ");
sbSQLMac.AppendLine("where 1=1 ");
if (info.ClassID != Guid.Empty)
{
if (info.cus_cIdentityNum == Common.DefineConstantValue.CodeMasterDefine.KEY2_SIOT_CardUserIdentity_Staff)
{
sbSQLTotalWhere.AppendLine("and dpm_RecordID = '" + info.ClassID.ToString() + "' ");
}
else if (info.cus_cIdentityNum == Common.DefineConstantValue.CodeMasterDefine.KEY2_SIOT_CardUserIdentity_Student)
{
sbSQLTotalWhere.AppendLine("and csm_cRecordID = '" + info.ClassID.ToString() + "' ");
}
}
if (!String.IsNullOrEmpty(info.cus_cStudentID))
{
sbSQLTotalWhere.AppendLine("and cus_cStudentID = '" + info.cus_cStudentID + "'");
}
if (!String.IsNullOrEmpty(info.cus_cChaName))
{
sbSQLTotalWhere.AppendLine("and cus_cChaName like N'%" + info.cus_cChaName + "%'");
}
if (!string.IsNullOrEmpty(info.cus_cIdentityNum))
{
sbSQLTotalWhere.AppendLine("and cus_cIdentityNum = '" + info.cus_cIdentityNum + "'");
}
if (info.TimeFrom != null && info.TimeTo != null)
{
sbSQLMacWhere.AppendLine("and csr_dConsumeDate between '" + ((DateTime)(info.TimeFrom)).ToString("yyyy/MM/dd HH:mm:ss") + "' and '" + ((DateTime)(info.TimeTo)).ToString("yyyy/MM/dd HH:mm:ss") + "'");
sbSQLPreCostWhere.AppendLine("and pcs_dConsumeDate between '" + ((DateTime)(info.TimeFrom)).ToString("yyyy/MM/dd HH:mm:ss") + "' and '" + ((DateTime)(info.TimeTo)).ToString("yyyy/MM/dd HH:mm:ss") + "'");
//sbSQLPreCostWhere.AppendLine("and pcs_dSettleTime between '" + ((DateTime)(info.TimeFrom)).ToString("yyyy/MM/dd HH:mm:ss") + "' and '" + ((DateTime)(info.TimeTo)).ToString("yyyy/MM/dd HH:mm:ss") + "'");
}
if (!string.IsNullOrEmpty(info.MacNo) && info.MacNo != "0")
{
int iMacNo = int.Parse(info.MacNo);
sbSQLMacWhere.AppendLine("and cmm_iMacNo = '" + iMacNo + "'");
}
sbSQLPreCost.AppendLine("SELECT");
sbSQLPreCost.AppendLine("cus_cStudentID,cus_cChaName,pcs_fCost as csr_fConsumeMoney, ");
sbSQLPreCost.AppendLine("pcs_dConsumeDate as csr_dConsumeDate,");
sbSQLPreCost.AppendLine("case cuad_cFlowType when 'NewCardCost' then N'新卡工本费补扣' when 'ReplaceCardCost' then N'换卡工本费补扣' when 'SetMealCost' then N'定餐费补扣' else N'系统补扣' end as MacNo,");
sbSQLPreCost.AppendLine("CONVERT(decimal,0) as csr_fCardBalance,");
sbSQLPreCost.AppendLine("case cus_cIdentityNum when 'STAFF' then ISNULL( dpm_cName,'')");
sbSQLPreCost.AppendLine("when 'STUDENT' then ISNULL( csm_cClassName,'') else '' end as csm_cClassName");
sbSQLPreCost.AppendLine("from dbo.CardUserAccount_cua");
sbSQLPreCost.AppendLine("join dbo.CardUserAccountDetail_cuad on cuad_cCUAID = cua_cRecordID");
sbSQLPreCost.AppendLine("join dbo.PreConsumeRecord_pcs on pcs_cRecordID = cuad_cConsumeID");
sbSQLPreCost.AppendLine("join dbo.CardUserMaster_cus on cus_cRecordID = cua_cCUSID");
sbSQLPreCost.AppendLine("left join dbo.ClassMaster_csm on csm_cRecordID = cus_cClassID ");
sbSQLPreCost.AppendLine("left join dbo.DepartmentMaster_dpm on dpm_RecordID = cus_cClassID ");
sbSQLPreCost.AppendLine("where 1=1 and cuad_cFlowType <> 'HedgeFund'");
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
db.CommandTimeout = 10 * 60000;
if (lIsWithMac && lIsWithPreCost)//包含全部数据
{
sbSQL.AppendLine(sbSQLMac.ToString() + sbSQLMacWhere.ToString() + sbSQLTotalWhere.ToString());
sbSQL.AppendLine("union all");
sbSQL.AppendLine(sbSQLPreCost.ToString() + sbSQLPreCostWhere.ToString() + sbSQLTotalWhere.ToString());
}
else if (lIsWithMac && !lIsWithPreCost)//只有打卡数据
{
sbSQL.AppendLine(sbSQLMac.ToString() + sbSQLMacWhere.ToString() + sbSQLTotalWhere.ToString());
}
else if (!lIsWithMac && lIsWithPreCost)//只有补扣数据
{
sbSQL.AppendLine(sbSQLPreCost.ToString() + sbSQLPreCostWhere.ToString() + sbSQLTotalWhere.ToString());
}
IEnumerable<PaymentBalance_Info> query = db.ExecuteQuery<PaymentBalance_Info>(sbSQL.ToString(), new object[] { });
if (query != null)
{
returnList = query.ToList<PaymentBalance_Info>();
}
}
}
catch
{
}
return returnList;
}
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;
}
private Sys_FunctionMaster_fum_Info FindFormPurview(Sys_FunctionMaster_fum_Info info)
{
string sqlString = string.Empty;
sqlString += "SELECT fom_cFormNumber,fom_cFormDesc " + Environment.NewLine;
sqlString += "FROM Sys_FormMaster_fom " + Environment.NewLine;
sqlString += "LEFT JOIN Sys_FormPurview_frp" + Environment.NewLine;
sqlString += "ON fom_cFormNumber=frp_cFormNumber" + Environment.NewLine;
sqlString += "LEFT JOIN Sys_FunctionMaster_fum" + Environment.NewLine;
sqlString += "ON frp_cFunctionNumber=fum_cFunctionNumber WHERE fum_iRecordID='" + info.fum_iRecordID + "'";
IEnumerable<Sys_FormMaster_fom_Info> infos = null;
List<Sys_FormMaster_fom_Info> infoList = null;
try
{
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
infos = db.ExecuteQuery<Sys_FormMaster_fom_Info>(sqlString, new object[] { });
if (infos != null)
{
infoList = infos.ToList<Sys_FormMaster_fom_Info>();
}
foreach (Sys_FormMaster_fom_Info t in infoList)
{
info.formMaster.Add(t);
}
return info;
}
}
catch (Exception Ex)
{
throw Ex;
}
}
public List<SystemAccountDetail_sad_Info> SearchRecords(IModelObject searchCondition)
{
List<SystemAccountDetail_sad_Info> listRecordInfo = null;
try
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine("SELECT TOP");
sbSQL.AppendLine(Common.DefineConstantValue.ListRecordMaxCount.ToString());
sbSQL.AppendLine("* FROM SystemAccountDetail_sad WHERE 1 = 1");
SystemAccountDetail_sad_Info searchInfo = searchCondition as SystemAccountDetail_sad_Info;
if (searchInfo != null)
{
if (!string.IsNullOrEmpty(searchInfo.sad_cFlowType))
sbSQL.AppendLine("AND sad_cFlowType ='" + searchInfo.sad_cFlowType + "'");
if (searchInfo.sad_cConsumeID != Guid.Empty)
sbSQL.AppendLine("AND sad_cConsumeID ='" + searchInfo.sad_cConsumeID.ToString() + "'");
if (!string.IsNullOrEmpty(searchInfo.sad_cOpt))
if (searchInfo.sad_dOptTime != DateTime.MinValue)
sbSQL.AppendLine("AND sad_dOptTime ='" + searchInfo.sad_dOptTime.ToString("yyyy-MM-dd HH:mm") + "'");
if (searchInfo.sad_cFLowMoney != 0)
sbSQL.AppendLine("AND sad_cFLowMoney =" + searchInfo.sad_cFLowMoney);
}
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
IEnumerable<SystemAccountDetail_sad_Info> query = db.ExecuteQuery<SystemAccountDetail_sad_Info>(sbSQL.ToString(), new object[] { });
if (query != null)
{
listRecordInfo = query.ToList<SystemAccountDetail_sad_Info>();
}
}
}
catch (Exception ex)
{
throw ex;
}
return listRecordInfo;
}
public List<ClassMaster_csm_Info> SearchRecords(IModelObject searchCondition)
{
List<ClassMaster_csm_Info> listReturn = null;
try
{
ClassMaster_csm_Info info = searchCondition as ClassMaster_csm_Info;
string strSql = "select * from dbo.ClassMaster_csm where 1 = 1 " + Environment.NewLine;
if (info != null)
{
if (info.csm_cGDMID != Guid.Empty)
{
strSql += " and csm_cGDMID = '" + info.csm_cGDMID.ToString() + "'" + Environment.NewLine;
}
if (info.csm_cMasterID != Guid.Empty)
{
strSql += " and csm_cMasterID = '" + info.csm_cMasterID.ToString() + "'" + Environment.NewLine;
}
if (!string.IsNullOrEmpty(info.csm_cClassName))
{
strSql += "and csm_cClassName like N'%" + info.csm_cClassName + "%'" + Environment.NewLine;
}
if (!string.IsNullOrEmpty(info.csm_cClassNum))
{
strSql += "and csm_cClassNum like '%" + info.csm_cClassNum + "%'" + Environment.NewLine;
}
}
IEnumerable<ClassMaster_csm_Info> infos = null;
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
infos = db.ExecuteQuery<ClassMaster_csm_Info>(strSql, new object[] { });
if (infos != null)
{
listReturn = infos.ToList();
}
}
}
catch
{
throw;
}
return listReturn;
}
public List<StudentCostSummary_scs_Info> SearchRecords(StudentCostSummary_scs_Info searchInfo)
{
if (searchInfo == null)
{
return null;
}
List<StudentCostSummary_scs_Info> listRes = new List<StudentCostSummary_scs_Info>();
try
{
#region SQL-- 统计用户的消费金额
string strPublicDate = string.Empty;
string strDateFrom = string.Empty;
string strDateTo = string.Empty;
if (searchInfo.scs_dStartDate != DateTime.MinValue && searchInfo.scs_dEndDate != DateTime.MinValue)
{
strDateFrom = searchInfo.scs_dStartDate.ToString("yyyy-MM-dd 00:00:00");
strDateTo = searchInfo.scs_dEndDate.ToString("yyyy-MM-dd 23:59:59");
}
else
{
if (searchInfo.scs_dStartDate != DateTime.MinValue)
{
strDateFrom = searchInfo.scs_dStartDate.ToString("yyyy-MM-dd 00:00:00");
strDateTo = searchInfo.scs_dEndDate.ToString("2099-01-01 23:59:59");
}
else if (searchInfo.scs_dEndDate != DateTime.MinValue)
{
strDateFrom = searchInfo.scs_dStartDate.ToString("1900-01-01 00:00:00");
strDateTo = searchInfo.scs_dEndDate.ToString("yyyy-MM-dd 23:59:59");
}
else
{
strDateFrom = searchInfo.scs_dStartDate.ToString("1900-01-01 00:00:00");
strDateTo = searchInfo.scs_dEndDate.ToString("2099-01-01 23:59:59");
}
}
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine(@"set nocount on;
if object_id('tempdb..#CostDetail')<>'' drop table #CostDetail
if object_id('tempdb..#UnMealMemberList')<>'' drop table #UnMealMemberList
declare @dateFrom datetime
declare @dateTo datetime
set @dateFrom = '" + strDateFrom + @"'
set @dateTo = '" + strDateTo + @"'
select targetID,targetLevel into #UnMealMemberList from
(select targetID=AAA.targetID,targetLevel=AAA.targetLevel from(
select * from(
select targetID,targetLevel,(SUM(cBreakfast)+SUM(cLunch)+SUM(cDinner))GenNum from (
select targetID=ISNULL(pus_cGradeID,ISNULL(pus_cClassID,pus_cCardUserID))
,convert(int,pus_cBreakfast)cBreakfast,convert(int,pus_cLunch)cLunch,convert(int,pus_cDinner)cDinner
,targetLevel=(case when pus_cGradeID is not null then 'Grade' else(case when pus_cClassID is not null then 'Class' else 'User' end) end)
from PaymentUDGeneralSetting_pus)A
group by targetID,targetLevel)AA where GenNum=0)AAA
left join(
select * from(
select targetID,targetLevel,(SUM(cBreakfast)+SUM(cLunch)+SUM(cDinner))GenNum from(
select targetID=ISNULL(pms_cGradeID,ISNULL(pms_cClassID,pms_cCardUserID))
,convert(int,pms_cBreakfast)cBreakfast,convert(int,pms_cLunch)cLunch,convert(int,pms_cDinner)cDinner
,targetLevel=(case when pms_cGradeID is not null then 'Grade' else(case when pms_cClassID is not null then 'Class' else 'User' end) end)
from dbo.PaymentUDMealState_pms
where pms_dStartDate>=@dateFrom and pms_dEndDate<=@dateTo)B
group by targetID,targetLevel)BB where GenNum>0)BBB
on AAA.targetID=BBB.targetID and AAA.targetLevel=BBB.targetLevel
where BBB.targetID is null)UnMealMemberList
select UserID,CostMoney
into #CostDetail from (
--无打卡补扣的定餐费用
select pcs_cUserID as UserID,cuad_fFlowMoney as CostMoney from CardUserAccountDetail_cuad
left join PreConsumeRecord_pcs on cuad_cConsumeID = pcs_cRecordID
where cuad_cFlowType = '" + Common.DefineConstantValue.ConsumeMoneyFlowType.SetMealCost.ToString() + @"'
and pcs_dConsumeDate between @dateFrom and @dateTo
union all
--打卡的定餐费用
select csr_cCardUserID as UserID,cuad_fFlowMoney as CostMoney from CardUserAccountDetail_cuad
left join ConsumeRecord_csr on cuad_cConsumeID = csr_cRecordID
where cuad_cFlowType = '" + Common.DefineConstantValue.ConsumeMoneyFlowType.SetMealCost.ToString() + @"'
and csr_dConsumeDate between @dateFrom and @dateTo
union all
--未结算的定餐费用
select pcs_cUserID as UserID,pcs_fCost as CostMoney from PreConsumeRecord_pcs
where pcs_lIsSettled = 0 and pcs_cConsumeType = '" + Common.DefineConstantValue.ConsumeMoneyFlowType.SetMealCost.ToString() + @"'
and pcs_dConsumeDate between @dateFrom and @dateTo
)as CostDetail
select cus_cRecordID as UserID,
cus_cChaName as scs_cUserName,
cus_cStudentID as scs_cUserNum,
csm_cClassName as scs_cClassName,
cus_cContractName as scs_cParentName,
cus_cContractPhone as scs_cParentPhone,
ISNULL(cus_cBankAccount,'') as scs_cAccountNum,
ISNULL(SumMealCost,0.00) + ISNULL(SumFreeCost,0.00) as scs_fTotalCost,
ISNULL(SumMealCost,0.00) as scs_fHistoryMealCost,
ISNULL(SumFreeCost,0.00) as scs_fHistoryFreeCost,
(ISNULL(cua_fCurrentBalance,0) - ISNULL(UnPayCost,0) + ISNULL(UnGetMoney,0)) as scs_fAccountBalance
,ISNULL(SumMealCost,0)SumFreeCost,ISNULL(SumFreeCost,0)SumMealCost
from (select cus_cRecordID as UserID,isnull(SumMealCost,0)SumMealCost from CardUserMaster_cus
left join (select UserID,SUM(CostMoney)as SumMealCost from #CostDetail group by UserID) AA on AA.UserID=cus_cRecordID
left join UserCardPair_ucp on ucp_cCUSID=cus_cRecordID and ucp_dReturnTime is null
where cus_lValid=1 and cus_lIsGraduate=0 and cus_cIdentityNum='STUDENT')as A
left join
(select UserID,SUM(CostMoney)as SumFreeCost from(--統計上月餐外消費總額_2_HistoryFreeCost
--餐外消费（加菜、饮料、热水）
select csr_cCardUserID as UserID,csr_fConsumeMoney as CostMoney from ConsumeRecord_csr
left join CardUserMaster_cus on csr_cCardUserID = cus_cRecordID
where csr_dConsumeDate between @dateFrom and @dateTo
and csr_cConsumeType <> '" + Common.DefineConstantValue.ConsumeMachineType.StuSetmeal.ToString() + @"'
and cus_cIdentityNum = '" + Common.DefineConstantValue.CodeMasterDefine.KEY2_SIOT_CardUserIdentity_Student + @"'
union all
--换卡、新发卡补扣费
select pcs_cUserID as UserID,pcs_fCost as CostMoney from PreConsumeRecord_pcs
left join CardUserMaster_cus on cus_cRecordID = pcs_cUserID
where (pcs_cConsumeType = '" + Common.DefineConstantValue.ConsumeMoneyFlowType.ReplaceCardCost.ToString() + @"'
or pcs_cConsumeType = '" + Common.DefineConstantValue.ConsumeMoneyFlowType.NewCardCost.ToString() + @"')
and pcs_fCost > 0
and pcs_dConsumeDate between @dateFrom and @dateTo
and cus_cIdentityNum = '" + Common.DefineConstantValue.CodeMasterDefine.KEY2_SIOT_CardUserIdentity_Student + @"'
) as _2_HistoryFreeCost group by UserID) as B
on A.UserID = B.UserID
left join CardUserMaster_cus on cus_cRecordID=A.UserID
left join ClassMaster_csm on csm_cRecordID=cus_cClassID
left join #UnMealMemberList MealA on MealA.targetID=csm_cGDMID and MealA.targetLevel='Grade'
left join #UnMealMemberList MealB on MealB.targetID=csm_cRecordID and MealB.targetLevel='Class'
left join #UnMealMemberList MealC on MealC.targetID=cus_cRecordID and MealC.targetLevel='User'
left join
(
select cua_cCUSID,cua_fCurrentBalance,ISNULL(UnPayCost,0.00) as UnPayCost,ISNULL(UnGetMoney,0.00) as UnGetMoney from CardUserAccount_cua
left join (
select SUM(pcs_fCost) as UnPayCost,pcs_cUserID from PreConsumeRecord_pcs
where pcs_lIsSettled = 0
and (pcs_cConsumeType = '" + Common.DefineConstantValue.ConsumeMoneyFlowType.SetMealCost.ToString() + @"'
or pcs_cConsumeType = '" + Common.DefineConstantValue.ConsumeMoneyFlowType.NewCardCost.ToString() + @"'
or pcs_cConsumeType = '" + Common.DefineConstantValue.ConsumeMoneyFlowType.ReplaceCardCost.ToString() + @"')
group by pcs_cUserID
) as SubTB_UnpayRecord on pcs_cUserID = cua_cCUSID
left join (
select SUM(prr_fRechargeMoney) as UnGetMoney,prr_cUserID from PreRechargeRecord_prr
where prr_cStatus <> '" + Common.DefineConstantValue.ConsumeMoneyFlowStatus.Finished.ToString() + @"'
group by prr_cUserID
) as SubTB_UnGetRecharge on prr_cUserID = cua_cCUSID
) as SubTB_AccountInfo on cua_cCUSID = cus_cRecordID
where 1=1
and (MealA.targetID is null and MealB.targetID is null and MealC.targetID is null)");
if (!string.IsNullOrEmpty(searchInfo.scs_cUserName))
{
sbSQL.AppendLine("and cus_cChaName like N'%" + searchInfo.scs_cUserName + "%'");
}
if (!string.IsNullOrEmpty(searchInfo.scs_cUserNum))
{
sbSQL.AppendLine("and cus_cStudentID like '%" + searchInfo.scs_cUserNum + "%'");
}
if (searchInfo.ClassID != null && searchInfo.ClassID.Value != Guid.Empty)
{
sbSQL.AppendLine("and cus_cClassID = '" + searchInfo.ClassID + "'");
}
if (searchInfo.GradeID != null && searchInfo.GradeID.Value != Guid.Empty)
{
sbSQL.AppendLine("and csm_cGDMID = '" + searchInfo.GradeID + "'");
}
sbSQL.AppendLine("if object_id('tempdb..#CostDetail')<>'' drop table #CostDetail");
#endregion
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
db.CommandTimeout = 10 * 60000;
IEnumerable<StudentCostSummary_scs_Info> query = db.ExecuteQuery<StudentCostSummary_scs_Info>(sbSQL.ToString(), new object[] { });
listRes = query.OrderBy(x => x.scs_cClassName).ToList();
#region 执行查询用户预计定餐消费额
DataTable dtAll = new DataTable("TableAll");
SqlConnection sqlconn = new SqlConnection(db.Connection.ConnectionString);
DateTime dtStart = DateTime.Parse(searchInfo.scs_dStartDate.ToString("yyyy-MM-01 00:00")).AddMonths(1);
int iDays = DateTime.DaysInMonth(dtStart.Year, dtStart.Month);
#region 循环获取用户每天的开餐情况
for (int i = 0; i < iDays; i++)
{
DateTime dtUse = dtStart.AddDays(i);
SqlCommand cmd = new SqlCommand("usp_GetUDMealSetting", sqlconn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300;
SqlParameter[] parameters = {
new SqlParameter("@StartDate", SqlDbType.DateTime),
new SqlParameter("@GradeID", SqlDbType.UniqueIdentifier),
new SqlParameter("@ClassID", SqlDbType.UniqueIdentifier),
new SqlParameter("@UserID", SqlDbType.UniqueIdentifier),
new SqlParameter("@UserName", SqlDbType.NVarChar,50),
new SqlParameter("@UserNum", SqlDbType.VarChar,30)};
parameters[0].Value = dtUse;
if (searchInfo.GradeID == null || (searchInfo.GradeID != null && searchInfo.GradeID.Value == Guid.Empty))
{
parameters[1].Value = Guid.Empty;
}
else
{
parameters[1].Value = searchInfo.GradeID;
}
if (searchInfo.ClassID == null || (searchInfo.ClassID != null && searchInfo.ClassID.Value == Guid.Empty))
{
parameters[2].Value = Guid.Empty;
}
else
{
parameters[2].Value = searchInfo.ClassID;
}
if (searchInfo.UserID == null || (searchInfo.UserID != null && searchInfo.UserID.Value == Guid.Empty))
{
parameters[3].Value = Guid.Empty;
}
else
{
parameters[3].Value = searchInfo.UserID;
}
if (!string.IsNullOrEmpty(searchInfo.scs_cUserName))
{
parameters[4].Value = searchInfo.scs_cUserName;
}
else
{
parameters[4].Value = string.Empty;
}
if (!string.IsNullOrEmpty(searchInfo.scs_cUserNum))
{
parameters[5].Value = searchInfo.scs_cUserNum;
}
else
{
parameters[5].Value = string.Empty;
}
cmd.Parameters.Add(parameters[0]);
cmd.Parameters.Add(parameters[1]);
cmd.Parameters.Add(parameters[2]);
cmd.Parameters.Add(parameters[3]);
cmd.Parameters.Add(parameters[4]);
cmd.Parameters.Add(parameters[5]);
sqlconn.Open();
SqlDataAdapter dp = new SqlDataAdapter(cmd);
dp.Fill(dtAll);
int iCount = dtAll.Rows.Count;
sqlconn.Close();
}
#endregion
List<UserMealDetail_Tmp> listMealDetail = new List<UserMealDetail_Tmp>();
if (dtAll != null && dtAll.Rows.Count > 0)
{
for (int i = 0; i < dtAll.Rows.Count; i++)
{
UserMealDetail_Tmp detail_Info = new UserMealDetail_Tmp();
detail_Info.UserID = new Guid(dtAll.Rows[i]["UserID"].ToString());
detail_Info.Breakfast = Convert.ToInt32(dtAll.Rows[i]["UD_Breakfast"]);
detail_Info.Lunch = Convert.ToInt32(dtAll.Rows[i]["UD_Lunch"]);
detail_Info.Dinner = Convert.ToInt32(dtAll.Rows[i]["UD_Dinner"]);
listMealDetail.Add(detail_Info);
}
}
var groupMealDetail = listMealDetail.GroupBy(x => x.UserID);
foreach (var mealItem in groupMealDetail)
{
Guid userID = mealItem.Key;
StudentCostSummary_scs_Info sumInfo = listRes.Find(x => x.UserID == userID);
if (sumInfo != null)
{
int iBreakfast = mealItem.Sum(x => x.Breakfast);
int iLunch = mealItem.Sum(x => x.Lunch);
int iDinner = mealItem.Sum(x => x.Dinner);
sumInfo.scs_fPreMealCost = iBreakfast * 2.00M + iLunch * 4.00M + iDinner * 4.00M;
}
}
#endregion
}
}
catch (Exception ex)
{
throw ex;
}
return listRes;
}
public decimal GetAccountTotalBalance(DateTime dtDeadline)
{
decimal fTotalAccount = decimal.Zero;
try
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine("SELECT SUM(cua_fCurrentBalance) FROM CardUserAccount_cua WHERE 1= 1");
sbSQL.AppendLine("AND cua_dLastSyncTime <= '" + dtDeadline.ToString("yyyy-MM-dd ") + "23:59:59" + "'");
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
var query = db.ExecuteQuery(typeof(decimal), sbSQL.ToString(), new object[] { });
foreach (decimal item in query)
{
fTotalAccount = item;
break;
}
}
}
catch (Exception ex)
{
throw ex;
}
return fTotalAccount;
}
public List<CardUserAccountDetail_cuad_Info> SearchRecords(IModelObject itemEntity, DateTime dtBegin, DateTime dtEnd)
{
List<CardUserAccountDetail_cuad_Info> listRecordInfo = null;
try
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine(@"select *
,(case [cuad_cFlowType]
when 'Recharge_AdvanceMoney' then [cuad_fFlowMoney]
when 'Recharge_PersonalRealTime' then [cuad_fFlowMoney]
when 'Recharge_PersonalTransfer' then [cuad_fFlowMoney]
when 'Recharge_BatchTransfer' then [cuad_fFlowMoney]
when 'Refund_PersonalCash' then [cuad_fFlowMoney] * -1
when 'Refund_CardPersonalRealTime' then [cuad_fFlowMoney] * -1
when 'Refund_PersonalTransfer' then [cuad_fFlowMoney] * -1
when 'Refund_BatchTransfer' then [cuad_fFlowMoney] * -1
else '' end) as FlowMoney
,(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 ''
end) as RechargeType
from
(
select [cuad_cRecordID],[cuad_cFlowType],[cuad_dOptTime],[cuad_fFlowMoney],cua_cCUSID,
[cus_cChaName] as [UserName],isnull([csm_cClassName],dpm_cName) as GroupName
from(
select [cuad_cRecordID],[cuad_cCUAID],[cuad_cFlowType],[cuad_dOptTime],[cuad_fFlowMoney]
from CardUserAccountDetail_cuad
where 1= 1");
CardUserAccountDetail_cuad_Info searchInfo = itemEntity as CardUserAccountDetail_cuad_Info;
if (searchInfo != null)
{
if (searchInfo.cuad_cCUAID != Guid.Empty)
sbSQL.AppendLine("AND cuad_cCUAID ='" + searchInfo.cuad_cCUAID + "'");
if (searchInfo.cuad_fFlowMoney != 0)
sbSQL.AppendLine("AND cuad_cFlowMoney =" + searchInfo.cuad_fFlowMoney.ToString());
if (!string.IsNullOrEmpty(searchInfo.cuad_cFlowType))
sbSQL.AppendLine("AND cuad_cFlowType ='" + searchInfo.cuad_cFlowType + "'");
if (searchInfo.cuad_cConsumeID != null)
sbSQL.AppendLine("AND cuad_cConsumeID ='" + searchInfo.cuad_cConsumeID + "'");
sbSQL.AppendLine("AND cuad_dOptTime >= '" + dtBegin.ToString("yyyy-MM-dd 00:00:00") + "'");
sbSQL.AppendLine("AND cuad_dOptTime <= '" + dtEnd.ToString("yyyy-MM-dd 23:59:59") + "'");
}
sbSQL.AppendLine(@"and cuad_cFlowType in('Recharge_PersonalRealTime'
,'Recharge_PersonalTransfer'
,'Recharge_BatchTransfer'
,'Refund_PersonalCash'
,'Refund_CardPersonalRealTime'
,'Refund_PersonalTransfer'
,'Refund_BatchTransfer'))CUAD inner join CardUserAccount_cua on cua_cRecordID = cuad_cCUAID
inner join CardUserMaster_cus on cua_cCUSID = cus_cRecordID
left join ClassMaster_csm on csm_cRecordID = cus_cClassID
left join DepartmentMaster_dpm on dpm_RecordID = cus_cClassID
)as A ORDER BY cuad_dOptTime DESC");
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
IEnumerable<CardUserAccountDetail_cuad_Info> query = db.ExecuteQuery<CardUserAccountDetail_cuad_Info>(sbSQL.ToString(), new object[] { });
if (query != null)
{
listRecordInfo = query.ToList<CardUserAccountDetail_cuad_Info>();
}
}
}
catch (Exception ex)
{
throw ex;
}
return listRecordInfo;
}
public List<CardUserAccount_cua_Info> GetRemindList(string identityNum, Guid departmentID, decimal Balance, bool lWithFilter, string userName)
{
List<CardUserAccount_cua_Info> reminList = new List<CardUserAccount_cua_Info>();
string sqlStr = string.Empty;
sqlStr += "select * from (" + Environment.NewLine;
sqlStr += "select cua_cCUSID,ISNULL( cus_cChaName,'') as cua_cUserName,cus_cStudentID," + Environment.NewLine;
sqlStr += "case cus_cIdentityNum " + Environment.NewLine;
sqlStr += "when '" + Common.DefineConstantValue.CodeMasterDefine.KEY2_SIOT_CardUserIdentity_Staff + "' then ISNULL( dpm_cName,'')" + Environment.NewLine;
sqlStr += "when '" + Common.DefineConstantValue.CodeMasterDefine.KEY2_SIOT_CardUserIdentity_Student + "' then ISNULL( csm_cClassName,'')" + Environment.NewLine;
sqlStr += "else ''" + Environment.NewLine;
sqlStr += "end as cua_cClassName," + Environment.NewLine;
sqlStr += "ISNULL( cua_fCurrentBalance,0.00)-ISNULL( lco.pcscost,0.00)+ISNULL(prrRecharge,0.00) as TotalBalance," + Environment.NewLine;
sqlStr += "ISNULL(cua_fCurrentBalance,0.00) as cua_fCurrentBalance,";
sqlStr += "ISNULL(lco.pcscost,0.00) as Pcscost,";
sqlStr += "ISNULL(prrRecharge,0.00) as PreRechargeMoney,";
sqlStr += "cua_dLastSyncTime" + Environment.NewLine;
sqlStr += ",cus_cBankAccount as BankAccount,cus_cContractName as ParentName,cus_cContractPhone as ParentPhone" + Environment.NewLine;
sqlStr += "from dbo.CardUserAccount_cua with(nolock)" + Environment.NewLine;
sqlStr += "left join dbo.CardUserMaster_cus with(nolock)" + Environment.NewLine;
sqlStr += "on cus_cRecordID=cua_cCUSID" + Environment.NewLine;
sqlStr += "left join dbo.ClassMaster_csm with(nolock)" + Environment.NewLine;
sqlStr += "on cus_cClassID=csm_cRecordID" + Environment.NewLine;
sqlStr += "left join dbo.DepartmentMaster_dpm with(nolock)" + Environment.NewLine;
sqlStr += "on cus_cClassID=dpm_RecordID" + Environment.NewLine;
sqlStr += "left join " + Environment.NewLine;
sqlStr += "(select pcs_cUserID,Sum(pcs_fCost) as pcscost from PreConsumeRecord_pcs with(nolock) where " + Environment.NewLine;
sqlStr += "(pcs_cConsumeType = 'NewCardCost' or pcs_cConsumeType = 'SetMealCost' or pcs_cConsumeType = 'ReplaceCardCost')" + Environment.NewLine;
sqlStr += "and pcs_lIsSettled = 0 group by pcs_cUserID ) lco " + Environment.NewLine;
sqlStr += "on lco.pcs_cUserID = cua_cCUSID" + Environment.NewLine;
sqlStr += "left join" + Environment.NewLine;
sqlStr += "(select prr_cUserID,SUM(prr_fRechargeMoney)as prrRecharge from PreRechargeRecord_prr with(nolock)" + Environment.NewLine;
sqlStr += "where prr_cRCRID is null and prr_cStatus= '" + Common.DefineConstantValue.ConsumeMoneyFlowStatus.WaitForAcceptTransfer.ToString() + "'" + Environment.NewLine;
sqlStr += "group by prr_cUserID) PreRec on PreRec.prr_cUserID = cua_cCUSID" + Environment.NewLine;//预充值款
sqlStr += "where 1=1 and cus_lValid=1 and cus_lIsGraduate=0" + Environment.NewLine;
//sqlStr += "and (cua_fCurrentBalance - ISNULL( lco.pcscost,0.00))<" + Balance.ToString() + Environment.NewLine;
if (identityNum != "")
{
sqlStr += "and cus_cIdentityNum='" + identityNum + "'" + Environment.NewLine;
}
if (departmentID != Guid.Empty)
{
sqlStr += "and cus_cClassID='" + departmentID.ToString() + "'" + Environment.NewLine;
}
if (!string.IsNullOrEmpty(userName))
{
sqlStr += "and cus_cChaName like N'%" + userName + "%'" + Environment.NewLine;
}
if (lWithFilter)
{
sqlStr += @"and cus_cRecordID not in
(
select pus_cCardUserID from (
select pus_cCardUserID,COUNT(pus_cCardUserID) as UserCount from PaymentUDGeneralSetting_pus with(nolock)
where pus_cBreakfast = 0 and pus_cLunch = 0 and pus_cDinner = 0
group by pus_cCardUserID
)as UserItem where UserCount = 7 and pus_cCardUserID is not null
union
select cus_cRecordID from CardUserMaster_cus with(nolock) where cus_cClassID in(
select pus_cClassID from
(
select pus_cClassID,COUNT(pus_cClassID)as ClassCount from PaymentUDGeneralSetting_pus with(nolock)
where pus_cBreakfast = 0 and pus_cLunch = 0 and pus_cDinner = 0
group by pus_cClassID
) as ClassItem where ClassCount = 7 and pus_cClassID is not null
union
(
select csm_cRecordID from ClassMaster_csm with(nolock) where csm_cGDMID in
(
select pus_cGradeID from (
select pus_cGradeID,COUNT(pus_cGradeID) as GradeCount from PaymentUDGeneralSetting_pus with(nolock)
where pus_cBreakfast = 0 and pus_cLunch = 0 and pus_cDinner = 0
group by pus_cGradeID
) as GradeItem where GradeCount = 7 and pus_cGradeID is not null))))
and cus_lValid=1 and cus_lIsGraduate=0" + Environment.NewLine;
}
sqlStr += ") as AAA where TotalBalance < " + Balance.ToString() + Environment.NewLine;
try
{
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
db.CommandTimeout = 10 * 60000;
IEnumerable<CardUserAccount_cua_Info> query = db.ExecuteQuery<CardUserAccount_cua_Info>(sqlStr.ToString(), new object[] { });
if (query != null)
{
reminList = query.ToList<CardUserAccount_cua_Info>();
#region 执行查询用户预计定餐消费额
DataTable dtAll = new DataTable("TableAll");
SqlConnection sqlconn = new SqlConnection(db.Connection.ConnectionString);
DateTime dtStart = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01 00:00")).AddMonths(1);
int iDays = DateTime.DaysInMonth(dtStart.Year, dtStart.Month);
#region 循环获取用户每天的开餐情况
for (int i = 0; i < iDays; i++)
{
DateTime dtUse = dtStart.AddDays(i);
SqlCommand cmd = new SqlCommand("usp_GetUDMealSetting", sqlconn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300;
SqlParameter[] parameters = {
new SqlParameter("@StartDate", SqlDbType.DateTime),
new SqlParameter("@GradeID", SqlDbType.UniqueIdentifier),
new SqlParameter("@ClassID", SqlDbType.UniqueIdentifier),
new SqlParameter("@UserID", SqlDbType.UniqueIdentifier),
new SqlParameter("@UserName", SqlDbType.NVarChar,50),
new SqlParameter("@UserNum", SqlDbType.VarChar,30)};
parameters[0].Value = dtUse;//查询日期
parameters[1].Value = Guid.Empty;//年级ID
parameters[2].Value = Guid.Empty;//班级ID
parameters[3].Value = Guid.Empty;//人员ID
if (!string.IsNullOrEmpty(userName))//人员姓名
{
parameters[4].Value = userName;
}
else
{
parameters[4].Value = string.Empty;
}
parameters[5].Value = string.Empty;//人员编号
cmd.Parameters.Add(parameters[0]);
cmd.Parameters.Add(parameters[1]);
cmd.Parameters.Add(parameters[2]);
cmd.Parameters.Add(parameters[3]);
cmd.Parameters.Add(parameters[4]);
cmd.Parameters.Add(parameters[5]);
sqlconn.Open();
SqlDataAdapter dp = new SqlDataAdapter(cmd);
dp.Fill(dtAll);
int iCount = dtAll.Rows.Count;
sqlconn.Close();
}
#endregion
List<UserMealDetail_Tmp> listMealDetail = new List<UserMealDetail_Tmp>();
if (dtAll != null && dtAll.Rows.Count > 0)
{
for (int i = 0; i < dtAll.Rows.Count; i++)
{
UserMealDetail_Tmp detail_Info = new UserMealDetail_Tmp();
detail_Info.UserID = new Guid(dtAll.Rows[i]["UserID"].ToString());
detail_Info.Breakfast = Convert.ToInt32(dtAll.Rows[i]["UD_Breakfast"]);
detail_Info.Lunch = Convert.ToInt32(dtAll.Rows[i]["UD_Lunch"]);
detail_Info.Dinner = Convert.ToInt32(dtAll.Rows[i]["UD_Dinner"]);
listMealDetail.Add(detail_Info);
}
}
#endregion
var groupMealDetail = listMealDetail.GroupBy(x => x.UserID);
foreach (var mealItem in groupMealDetail)
{
Guid userID = mealItem.Key;
CardUserAccount_cua_Info sumInfo = reminList.Find(x => x.cua_cCUSID == userID);
if (sumInfo != null)
{
int iBreakfast = mealItem.Sum(x => x.Breakfast);
int iLunch = mealItem.Sum(x => x.Lunch);
int iDinner = mealItem.Sum(x => x.Dinner);
sumInfo.NextMonthPreRecharge = iBreakfast * 2.00M + iLunch * 4.00M + iDinner * 4.00M;
}
}
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return reminList;
}
public List<Sys_RoleMaster_rlm_Info> SearchRecords(Model.IModel.IModelObject searchCondition)
{
string sqlString = string.Empty;
string whereString = string.Empty;
sqlString = "SELECT TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString() + Environment.NewLine;
sqlString += " rlm_iRecordID," + Environment.NewLine;
sqlString += " rlm_cRoleID," + Environment.NewLine;
sqlString += " rlm_cRoleDesc," + Environment.NewLine;
//sqlString += " usm_cPasswork," + Environment.NewLine;
//sqlString += " usm_cEMail," + Environment.NewLine;
//sqlString += " (CASE usm_iLock WHEN 1 THEN N'已锁' WHEN 0 THEN N'未锁' END) AS iLock," + Environment.NewLine;
//sqlString += " usm_cRemark," + Environment.NewLine;
sqlString += " rlm_cAdd," + Environment.NewLine;
sqlString += " rlm_dAddDate," + Environment.NewLine;
sqlString += " rlm_cLast," + Environment.NewLine;
sqlString += " rlm_dLastDate " + Environment.NewLine;
sqlString += " FROM Sys_RoleMaster_rlm" + Environment.NewLine;
Sys_RoleMaster_rlm_Info info = null;
info = searchCondition as Sys_RoleMaster_rlm_Info;
if (info != null)
{
whereString = " WHERE 1=1 ";
if (info.rlm_iRecordID > 0)
{
whereString += "AND rlm_iRecordID = " + info.rlm_iRecordID.ToString().Trim() + " ";
}
else
{
if (info.rlm_cRoleID.Trim() != "")
{
if (info.rlm_cRoleID.ToString().Contains("*") || info.rlm_cRoleID.ToString().Contains("?"))
{
whereString += " AND rlm_cRoleID LIKE N'" + LocalDefine.General.ReplaceSQLLikeCondition(info.rlm_cRoleID) + "'";
}
else
{
whereString += "AND rlm_cRoleID = N'" + info.rlm_cRoleID.ToString().Trim() + "'";
}
}
}
}
sqlString += whereString;
IEnumerable<Sys_RoleMaster_rlm_Info> infos = null;
List<Sys_RoleMaster_rlm_Info> infoList = null;
try
{
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
infos = db.ExecuteQuery<Sys_RoleMaster_rlm_Info>(sqlString, new object[] { });
if (infos != null)
{
infoList = infos.ToList<Sys_RoleMaster_rlm_Info>();
}
foreach (Sys_RoleMaster_rlm_Info roleInfo in infoList)
{
List<Sys_UserRoles_usr> listUR = db.Sys_UserRoles_usr.Where(x => x.usr_cRoleID == roleInfo.rlm_cRoleID).ToList();
if (listUR != null)
{
roleInfo.userMasterList = new List<Sys_UserMaster_usm_Info>();
foreach (Sys_UserRoles_usr urItem in listUR)
{
List<Sys_UserMaster_usm> listUser = db.Sys_UserMaster_usm.Where(x => x.usm_cUserLoginID == urItem.usr_cUserLoginID).ToList();
if (listUser != null && listUser.Count > 0)
{
Sys_UserMaster_usm_Info userInfo = Common.General.CopyObjectValue<Sys_UserMaster_usm, Sys_UserMaster_usm_Info>(listUser[0]);
if (userInfo != null)
{
roleInfo.userMasterList.Add(userInfo);
}
}
}
}
}
}
}
catch (Exception Ex)
{
throw Ex;
}
return infoList;
}
public List<CardUserAccount_cua_Info> SearchRecords(IModelObject searchCondition)
{
List<CardUserAccount_cua_Info> listRecordInfo = null;
try
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine("SELECT TOP");
sbSQL.AppendLine(Common.DefineConstantValue.ListRecordMaxCount.ToString());
sbSQL.AppendLine("* FROM CardUserAccount_cua WHERE 1 = 1");
CardUserAccount_cua_Info searchInfo = searchCondition as CardUserAccount_cua_Info;
if (searchInfo != null)
{
if (searchInfo.cua_cCUSID != Guid.Empty)
sbSQL.AppendLine("AND cua_cCUSID ='" + searchInfo.cua_cCUSID.ToString() + "'");
if (searchInfo.cua_dLastSyncTime != DateTime.MinValue)
sbSQL.AppendLine("AND cua_dLastSyncTime ='" + searchInfo.cua_dLastSyncTime.ToString("yyyy-MM-dd HH:mm") + "'");
if (!string.IsNullOrEmpty(searchInfo.cua_cAdd))
sbSQL.AppendLine("AND cua_cAdd ='" + searchInfo.cua_cAdd + "'");
if (searchInfo.cua_dAddDate != DateTime.MinValue)
sbSQL.AppendLine("AND cua_dAddDate ='" + searchInfo.cua_dAddDate.ToString("yyyy-MM-dd HH:mm") + "'");
if (searchInfo.cua_fCurrentBalance != 0)
sbSQL.AppendLine("AND cua_fCurrentBalance =" + searchInfo.cua_fCurrentBalance);
if (searchInfo.cua_fOriginalBalance != 0)
sbSQL.AppendLine("AND cua_fOriginalBalance =" + searchInfo.cua_fOriginalBalance);
}
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
IEnumerable<CardUserAccount_cua_Info> query = db.ExecuteQuery<CardUserAccount_cua_Info>(sbSQL.ToString(), new object[] { });
if (query != null)
{
listRecordInfo = query.ToList<CardUserAccount_cua_Info>();
}
}
}
catch (Exception ex)
{
throw ex;
}
return listRecordInfo;
}
private Sys_RoleMaster_rlm_Info FindUserRole(Sys_RoleMaster_rlm_Info info)
{
string sqlString = string.Empty;
sqlString += "SELECT usm_cUserLoginID,usm_cChaName " + Environment.NewLine;
sqlString += "FROM Sys_UserMaster_usm " + Environment.NewLine;
sqlString += "LEFT JOIN Sys_UserRoles_usr" + Environment.NewLine;
sqlString += "ON usm_cUserLoginID=usr_cUserLoginID" + Environment.NewLine;
sqlString += "LEFT JOIN Sys_RoleMaster_rlm" + Environment.NewLine;
sqlString += "ON rlm_cRoleID=usr_cRoleID WHERE rlm_cRoleID='" + info.rlm_cRoleID + "'";
IEnumerable<Sys_UserMaster_usm_Info> infos = null;
List<Sys_UserMaster_usm_Info> infoList = null;
try
{
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
infos = db.ExecuteQuery<Sys_UserMaster_usm_Info>(sqlString, new object[] { });
if (infos != null)
{
infoList = infos.ToList<Sys_UserMaster_usm_Info>();
}
foreach (Sys_UserMaster_usm_Info t in infoList)
{
info.userMasterList.Add(t);
}
return info;
}
}
catch (Exception Ex)
{
throw Ex;
}
}
public CardUserAccount_cua_Info SyncAccount(IModelObject KeyObject, DateTime dtDeadline)
{
CardUserAccount_cua_Info resInfo = null;
try
{
CardUserAccount_cua_Info infoObject = KeyObject as CardUserAccount_cua_Info;
if (infoObject != null)
{
using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
{
db.CommandTimeout = 10 * 60000;
CardUserAccount_cua record = db.CardUserAccount_cua.Where(x => x.cua_cRecordID == infoObject.cua_cRecordID).FirstOrDefault();
if (record != null)
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendLine(@"SELECT cua_cRecordID,cua_cCUSID,cua_fOriginalBalance,");
sbSQL.AppendLine(@"ISNULL(TotalCost.cua_fCurrentBalance,0.00) as cua_fCurrentBalance,");
sbSQL.AppendLine(@"ISNULL(TotalCost.cua_dLastSyncTime,GETDATE()) as cua_dLastSyncTime,");
sbSQL.AppendLine(@"cua_lIsActive, cua_cAdd, cua_dAddDate FROM dbo.CardUserAccount_cua");
sbSQL.AppendLine(@"INNER JOIN (SELECT * FROM(");
sbSQL.AppendLine(@"SELECT SUM(FormulaRes) as cua_fCurrentBalance,MAX(cuad_dOptTime) AS cua_dLastSyncTime");
sbSQL.AppendLine(@"FROM (SELECT *,(cuad_fFlowMoney * cmt_fNumber) AS FormulaRes FROM CardUserAccountDetail_cuad ");
sbSQL.AppendLine(@"INNER JOIN CardUserAccount_cua ON cua_cRecordID = cuad_cCUAID");
sbSQL.AppendLine(@"AND cua_cCUSID = '" + record.cua_cCUSID + "'");
sbSQL.AppendLine(@"AND cuad_dOptTime <='" + dtDeadline + "'");
sbSQL.AppendLine(@"LEFT JOIN CodeMaster_cmt ON cmt_cKey1 = 'ACCOUNTFORMULA_USER' AND cmt_cKey2 = cuad_cFlowType) AS A) AS AA) AS TotalCost");
sbSQL.AppendLine(@"on cua_cCUSID ='" + record.cua_cCUSID + "'");
IEnumerable<CardUserAccount_cua_Info> query = db.ExecuteQuery<CardUserAccount_cua_Info>(sbSQL.ToString(), new object[] { });
if (query != null)
{
List<CardUserAccount_cua_Info> listRes = query.ToList();
if (listRes.Count > 0)
{
CardUserAccount_cua_Info accRev = listRes[0];
record.cua_fCurrentBalance = accRev.cua_fCurrentBalance;
record.cua_dLastSyncTime = dtDeadline;
if (dtDeadline.Date == DateTime.Now.AddDays(-1))
{
db.SubmitChanges();
}
}
}
resInfo = Common.General.CopyObjectValue<CardUserAccount_cua, CardUserAccount_cua_Info>(record);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return resInfo;
}