private void LoadMonthlyData(monthInYear type, int year)
{
saleRecordModelBindingSource.Clear();
SaleReportController controller = new SaleReportController();
MySqlConnection connection =
new MySqlConnection("server = " + connectData.server()
+ "; database = " + connectData.database()
+ "; username = "******"; password="******";");
string txt = "select sum(totalprice) from salesrecord group by year(date)='" + year.ToString() + "'" + ", month(date)='" + type.ToString() + "'";
MySqlCommand command = new MySqlCommand(txt, connection);
connection.Open();
MySqlDataReader r = command.ExecuteReader();
MonthlyResult monthlyResult = new MonthlyResult((int)type, year);
while (r.Read())
{
int total = int.Parse(r[0].ToString());
int monthly = Convert.ToInt32(type);
//int totalItems = int.Parse(r[1].ToString());
monthlyResult.LoadData(year, monthly, total);
}
monthlyResult.Show();
}
/// <summary>
/// Пресмята първоначалните такси
/// </summary>
/// <param name="p"></param>
/// <param name="m"></param>
/// <returns></returns>
private decimal InitialFeesCalc(CreditCalcParams p, MonthlyResult m)
{
m.Fees = getFeeAmount(p.Amount, p.ApplicationFee, p.TreatApplicationFeeAsPercent);
m.Fees += getFeeAmount(p.Amount, p.ProcessingFee, p.TreatProcessingFeeAsPercent);
m.Fees += getFeeAmount(p.Amount, p.OtherInitialFees, p.TreatOtherInitialFeesAsPercent);
return(Math.Round(m.Fees, 2));
}
/// <summary>
/// Пресмята и запълва първия ред от погасителния план
/// </summary>
/// <param name="p"></param>
/// <returns></returns>
private MonthlyResult GetZeroMonth(CreditCalcParams p)
{
MonthlyResult m = new MonthlyResult();
m.RowNumber = 0;
m.Date = DateTime.Now;
m.Installment = m.PrinicpalInstallment = m.RateInstallment = 0;
m.PrincipalRemainder = p.Amount;
m.Fees = InitialFeesCalc(p, m);
m.TotalInstallment = p.Amount - m.Fees;
return(m);
}
/// <summary>
/// Функция, пресмятаща резултатите от погасителния план при намаляващи месечни вноски
/// </summary>
/// <param name="p"></param>
/// <returns></returns>
private CreditCalcResults CalculateDescending(CreditCalcParams p)
{
CreditCalcResults res = new CreditCalcResults();
res.MonthlyInstallments = new List <MonthlyResult>();
res.MonthlyInstallments.Add(GetZeroMonth(p));
decimal principalInstallment = Math.Round(p.Amount / (p.Period - (p.GratisPeriod ?? 0)), 2);
for (int i = 1; i <= p.Period; i++)
{
MonthlyResult m = new MonthlyResult();
if (i == 1)
{
m.PrincipalRemainder = p.Amount;
}
else
{
m.PrincipalRemainder = res.MonthlyInstallments[i - 1].CurrentPrincipallRemainder();
}
m.RowNumber = i;
m.Date = DateTime.Now.AddMonths(i);
bool isInPromotion = (i <= (p.PromotionPeriod ?? 0));
bool isInGratis = (i <= (p.GratisPeriod ?? 0));
m.RateInstallment = Math.Round((decimal)(m.PrincipalRemainder * (isInPromotion ? p.PromotionRate : p.Rate) / 100) / 12, 2);
if (isInGratis)
{
m.PrinicpalInstallment = 0;
}
else
{
m.PrinicpalInstallment = (i == p.Period) ? Math.Round(p.Amount - (i - 1 - (p.GratisPeriod ?? 0)) * principalInstallment, 2) : principalInstallment;
}
m.Installment = (isInGratis ? 0 : m.PrinicpalInstallment) + m.RateInstallment;
if (i % 12 == 1 && i != 1)
{
m.Fees = MonthlyFeesCalc(p, m) + AnnualFeesCalc(p, m);
}
else
{
m.Fees = MonthlyFeesCalc(p, m);
}
m.TotalInstallment = m.Installment + m.Fees;
res.MonthlyInstallments.Add(m);
}
return(res);
}
public void CalculateMonthlyResult(int Year, int Month)
{
MonthlyResult mr = new MonthlyResult();
mr.MR_Year = Year;
mr.MR_Month = Month;
#region 得到迟到的人
StringBuilder sb = new StringBuilder();
sb.Append("select MS_UserID from [MonthlyStatistics] where MS_Year=" + Year + " and MS_Month=" + Month + " and MS_ComeLateCount>0");
//加入日志
writestr("得到迟到的人的Sql:" + sb.ToString());
var LateUsers = smoONEContext.Database.SqlQuery <string>(sb.ToString()).ToList();
if (LateUsers != null && LateUsers.Count > 0)
{
mr.MR_ComeLateUserCount = LateUsers.Count;
StringBuilder Late = new StringBuilder();
foreach (string lateName in LateUsers)
{
Late.Append(lateName + ",");
}
mr.MR_ComeLateUser = Late.ToString().Substring(0, Late.Length - 1);
}
else
{
mr.MR_ComeLateUser = "";
mr.MR_ComeLateUserCount = 0;
}
#endregion
#region 得到早退的人
StringBuilder sb2 = new StringBuilder();
sb2.Append("select MS_UserID from [MonthlyStatistics] where MS_Year=" + Year + " and MS_Month=" + Month + " and MS_LeaveEarlyCount>0");
//加入日志
writestr("得到早退的人的Sql:" + sb2.ToString());
var EarlyUsers = smoONEContext.Database.SqlQuery <string>(sb2.ToString()).ToList();
if (EarlyUsers != null && EarlyUsers.Count > 0)
{
mr.MR_LeaveEarlyUserCount = EarlyUsers.Count;
StringBuilder Early = new StringBuilder();
foreach (string earlyName in EarlyUsers)
{
Early.Append(earlyName + ",");
}
mr.MR_LeaveEarlyUser = Early.ToString().Substring(0, Early.Length - 1);
}
else
{
mr.MR_LeaveEarlyUser = "";
mr.MR_LeaveEarlyUserCount = 0;
}
#endregion
#region 得到未签的人
StringBuilder sb3 = new StringBuilder();
sb3.Append("select MS_UserID from [MonthlyStatistics] where MS_Year=" + Year + " and MS_Month=" + Month + " and (MS_NoSignInCount>0 or MS_NoSignOutCount>0) and MS_AllCount*DS_AbsentDayCount/DS_AllDayCount<MS_NoSignInCount+MS_NoSignOutCount");
//加入日志
writestr("得到未签的人的Sql:" + sb3.ToString());
var NoSignUsers = smoONEContext.Database.SqlQuery <string>(sb3.ToString()).ToList();
if (NoSignUsers != null && NoSignUsers.Count > 0)
{
mr.MR_NoSignUserCount = NoSignUsers.Count;
StringBuilder NoSign = new StringBuilder();
foreach (string nosignName in NoSignUsers)
{
NoSign.Append(nosignName + ",");
}
mr.MR_NoSignUser = NoSign.ToString().Substring(0, NoSign.Length - 1);
}
else
{
mr.MR_NoSignUser = "";
mr.MR_NoSignUserCount = 0;
}
#endregion
#region 得到正常签到的人
StringBuilder sb4 = new StringBuilder();
sb4.Append("select MS_UserID from [MonthlyStatistics] where MS_Year=" + Year + " and MS_Month=" + Month + " and DS_NormalDayCount=DS_AllDayCount");
//加入日志
writestr("得到正常签到的人的Sql:" + sb4.ToString());
var NormalUsers = smoONEContext.Database.SqlQuery <string>(sb4.ToString()).ToList();
if (NormalUsers != null && NormalUsers.Count > 0)
{
mr.MR_InTimeUserCount = NormalUsers.Count;
StringBuilder InTime = new StringBuilder();
foreach (string normalName in NormalUsers)
{
InTime.Append(normalName + ",");
}
mr.MR_InTimeUser = InTime.ToString().Substring(0, InTime.Length - 1);
}
else
{
mr.MR_InTimeUser = "";
mr.MR_InTimeUserCount = 0;
}
#endregion
#region 得到全天旷工的人
StringBuilder sb5 = new StringBuilder();
sb5.Append("select MS_UserID from [MonthlyStatistics] where MS_Year=" + Year + " and MS_Month=" + Month + " and DS_AbsentDayCount>0");
//加入日志
writestr("得到全天旷工的人的Sql:" + sb5.ToString());
var AbsentUsers = smoONEContext.Database.SqlQuery <string>(sb5.ToString()).ToList();
if (AbsentUsers != null && AbsentUsers.Count > 0)
{
mr.MR_AbsentUserCount = AbsentUsers.Count;
StringBuilder Absent = new StringBuilder();
foreach (string absentName in AbsentUsers)
{
Absent.Append(absentName + ",");
}
mr.MR_AbsentUser = Absent.ToString().Substring(0, Absent.Length - 1);
}
else
{
mr.MR_AbsentUser = "";
mr.MR_AbsentUserCount = 0;
}
#endregion
#region 得到应考勤人数
StringBuilder sb6 = new StringBuilder();
sb6.Append("select MS_UserID from [MonthlyStatistics] where MS_Year=" + Year + " and MS_Month=" + Month);
//加入日志
writestr("得到应考勤人数的人的Sql:" + sb6.ToString());
var AllUsers = smoONEContext.Database.SqlQuery <string>(sb6.ToString()).ToList();
if (AllUsers != null && AllUsers.Count > 0)
{
mr.MR_AllUserCount = AllUsers.Count;
}
else
{
mr.MR_AllUserCount = 0;
}
#endregion
#region 保存月总统计
try
{
_unitOfWork.RegisterNew(mr);
bool result = _unitOfWork.Commit();
}
catch (Exception ex)
{
_unitOfWork.Rollback();
}
#endregion
}
/// <summary>
/// 执行统计（主要包括补全昨天及之前的排班,补全昨天及之前的未签记录和用户日统计,补全上个月及之前的用户月统计,补全上个月及之前的月总统计）
/// </summary>
public void RunStatistics()
{
try
{
writestr("服务运行.");
//这里执行你的东西
#region 计算模板当天的排班以及补全之前的排班
//得到今天所有的模板
List <string> ATIDs = _AT_UserLogRepository.GetATIDByDate(DateTime.Now.Date).AsNoTracking().ToList();
StringBuilder ATids = new StringBuilder();
if (ATIDs != null && ATIDs.Count > 0)
{
foreach (string ATID in ATIDs)
{
ATids.Append(ATID.ToString() + ",");
//得到每个模板某天的排班
AttendanceScheduling ass = _attendanceSchedulingRepository.GetByATIDandDate(ATID, DateTime.Now.Date).FirstOrDefault();
if (ass != null)
{
}
else
{
AttendanceTemplate at = _AttendanceTemplateRepository.GetByID(ATID).AsNoTracking().FirstOrDefault();
//当天无排班,则先添加排班
if (at != null)
{
AddHisAttendanceScheduling(at);
}
}
}
writestr(ATids.ToString());
}
#endregion
#region 计算用户的所有日统计
//得到有过模板的所有用户
List <string> UserNames = _AT_UserLogRepository.GetAll().Select(o => o.AT_UL_UserID).Distinct().ToList();
//用于日志判断
StringBuilder uName = new StringBuilder();
foreach (string User in UserNames)
{
uName.Append(User + ",");
}
writestr(uName.ToString());
foreach (string User in UserNames)
{
//判断用户昨天的日统计是否存在
DailyStatistics ds = _dailyStatisticsRepository.GetDSByUser(User, DateTime.Now.AddDays(-1).Date).AsNoTracking().FirstOrDefault();
if (ds == null)
{
//用户昨天没有日统计数据,则可能需要补日统计数据
//先得到用户最后的日统计数据日期
//用于日志判断
writestr(User + "昨天的ds为null");
ds = _dailyStatisticsRepository.GetNewestByUser(User);
DateTime Newest = DateTime.MinValue.Date;
if (ds != null)
{
Newest = ds.DS_Date.AddDays(1).Date;
}
writestr("Newest日期:" + Newest.ToString());
DateTime EndDate = DateTime.Now.Date;
AT_UserLog au = _AT_UserLogRepository.GetNewestByUser(User);
if (au != null && au.AT_UL_EndTime != DateTime.MaxValue)
{
writestr(au.AT_UL_EndTime.ToString());
EndDate = au.AT_UL_EndTime.AddDays(1).Date;
}
writestr("EndDate日期:" + EndDate.ToString());
//得到可能需要补全日统计数据的日期
List <DateTime> dates = _attendanceSchedulingRepository.GetByUserIDAndPeriod(User, Newest, EndDate).Select(o => o.AS_DateTime).ToList();
//记录日志
StringBuilder d = new StringBuilder();
if (dates != null && dates.Count > 0)
{
d.Append("需要补全的日期:");
foreach (DateTime dt in dates)
{
d.Append(dt.ToString() + " ");
}
}
if (dates != null && dates.Count > 0)
{
foreach (DateTime dt in dates)
{
CalculateDailyStatistics(User, dt.Date);
}
}
}
}
#endregion
#region 计算用户的月统计和总的月统计
foreach (string User in UserNames)
{
//判断用户上个月的统计是否存在
writestr("进入" + User + "月统计");
MonthlyStatistics ms = _monthlyStatisticsRepository.GetUserMonthlyStatistics(User, DateTime.Now.AddMonths(-1).Year, DateTime.Now.AddMonths(-1).Month).AsNoTracking().FirstOrDefault();
if (ms == null)
{
//从排班中获取需要统计的月份
//得到用户最近的月统计月份
writestr(User + "上个月不存在月统计.");
ms = _monthlyStatisticsRepository.GetNewestUserMonthlyStatistics(User);
//加入日志
writestr("ms没报错.");
int StartYear = 0;
int StartMonth = 0;
if (ms != null)
{
StartYear = ms.MS_Year;
StartMonth = ms.MS_Month;
}
else
{
DailyStatistics ds = _dailyStatisticsRepository.GetOldestByUser(User);
if (ds != null)
{
StartYear = ds.DS_Date.AddMonths(-1).Year;
StartMonth = ds.DS_Date.AddMonths(-1).Month;
}
}
//加入日志
writestr("StartYear:" + StartYear.ToString() + ";StartMonth:" + StartMonth.ToString());
if (StartYear != 0)
{
//用户至少有一条日统计数据才可能需要计算月统计数据
//得到用户需要统计的所有年和月
StringBuilder s = new StringBuilder();
s.Append(StartYear);
if (StartMonth < 10)
{
s.Append("0");
}
s.Append(StartMonth);
s.Append("01");
string temp = s.ToString();
DateTime StartTemp = DateTime.ParseExact(temp, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);
DateTime StartDate = StartTemp.AddMonths(1).Date;
StringBuilder sb = new StringBuilder();
sb.Append("select DS_UserID MS_UserID, year(DS_Date) MS_Year, month(DS_Date) MS_Month ,");
sb.Append("sum(DS_AllCount) MS_AllCount,sum(DS_RealCount) MS_RealCount,sum(DS_InTimeCount) MS_InTimeCount,");
sb.Append("sum(DS_ComeLateCount) MS_ComeLateCount,sum(DS_LeaveEarlyCount) MS_LeaveEarlyCount,");
sb.Append("sum(DS_NoSignInCount) MS_NoSignInCount,sum(DS_NoSignOutCount) MS_NoSignOutCount,");
sb.Append("sum(DS_IsNormal) DS_NormalDayCount,sum(DS_IsAbsent) DS_AbsentDayCount,count(DS_Date) DS_AllDayCount");
sb.Append(" from DailyStatistics");
sb.Append(" where DS_UserID='" + User + "' and DS_Date>='" + StartDate + "' group by year(DS_Date), month(DS_Date),DS_UserID");
//加入日志
writestr("Sql语句:" + sb.ToString());
var MonthlyStatisticsDto = smoONEContext.Database.SqlQuery <MonthlyStatisticsDto>(sb.ToString()).ToList();
if (MonthlyStatisticsDto != null && MonthlyStatisticsDto.Count > 0)
{
//加入日志
writestr("MonthlyStatisticsDto未报错.");
foreach (MonthlyStatisticsDto msd in MonthlyStatisticsDto)
{
if (msd.MS_Year == DateTime.Now.Year && msd.MS_Month == DateTime.Now.Month)
{
//当月的不记入数据库
}
else
{
//保存月统计记录
try
{
MonthlyStatistics ms2 = new MonthlyStatistics();
ms2.DS_AbsentDayCount = msd.DS_AbsentDayCount;
ms2.DS_AllDayCount = msd.DS_AllDayCount;
ms2.DS_NormalDayCount = msd.DS_NormalDayCount;
ms2.MS_AllCount = msd.MS_AllCount;
ms2.MS_ComeLateCount = msd.MS_ComeLateCount;
ms2.MS_InTimeCount = msd.MS_InTimeCount;
ms2.MS_LeaveEarlyCount = msd.MS_LeaveEarlyCount;
ms2.MS_Month = msd.MS_Month;
ms2.MS_NoSignInCount = msd.MS_NoSignInCount;
ms2.MS_NoSignOutCount = msd.MS_NoSignOutCount;
ms2.MS_RealCount = msd.MS_RealCount;
ms2.MS_UserID = msd.MS_UserID;
ms2.MS_Year = msd.MS_Year;
_unitOfWork.RegisterNew(ms2);
bool result = _unitOfWork.Commit();
//加入日志
if (result == true)
{
writestr(ms2.MS_Year.ToString() + ms2.MS_Month.ToString() + "的ms2成功添加.");
}
}
catch (Exception ex)
{
_unitOfWork.Rollback();
}
}
}
}
}
}
}
//计算总的月统计
//上个月的月统计是否存在
MonthlyResult mr = _monthlyResultRepository.GetMonthlyResult(DateTime.Now.AddMonths(-1).Year, DateTime.Now.AddMonths(-1).Month).AsNoTracking().FirstOrDefault();
if (mr == null)
{
//上个月总统计不存在
//先得到最近的总统计
//加入日志
writestr("上个月的总月统计不存在.");
mr = _monthlyResultRepository.GetAll().OrderByDescending(o => o.MR_Year).ThenByDescending(o => o.MR_Month).FirstOrDefault();
int StartYear = 0;
int StartMonth = 0;
if (mr == null)
{
//之前都不存在月总统计
MonthlyStatistics ms = _monthlyStatisticsRepository.GetAll().OrderBy(o => o.MS_Year).ThenBy(o => o.MS_Month).AsNoTracking().FirstOrDefault();
if (ms != null)
{
StartYear = ms.MS_Year;
StartMonth = ms.MS_Month;
}
}
else
{
//之前存在月总统计
if (mr.MR_Month < 12)
{
StartYear = mr.MR_Year;
StartMonth = mr.MR_Month + 1;
}
else
{
StartYear = mr.MR_Year + 1;
StartMonth = 1;
}
}
//加入日志
writestr("月总统计的StartYear:" + StartYear.ToString() + ",StartMonth:" + StartMonth.ToString());
if (StartYear != 0)
{
StringBuilder sb = new StringBuilder();
sb.Append("select distinct MS_Year, MS_Month from MonthlyStatistics ");
sb.Append(" where (MS_Year=" + StartYear + " and MS_Month>=" + StartMonth + ") or MS_Year>" + StartYear);
//加入日志
writestr("得到需要月总统计的年月的Sql:" + sb.ToString());
var YearAndMonths = smoONEContext.Database.SqlQuery <MonthlyStatisticsDto>(sb.ToString()).ToList();
if (YearAndMonths != null && YearAndMonths.Count > 0)
{
//加入日志
writestr("YearAndMonths未报错.");
foreach (MonthlyStatisticsDto yam in YearAndMonths)
{
//加入日志
writestr("YearAndMonth:" + yam.MS_Year.ToString() + yam.MS_Month.ToString());
if (yam.MS_Year == DateTime.Now.Year && yam.MS_Month == DateTime.Now.Month)
{
//当月的不保存
}
else
{
//加入日志
writestr("进入月总统计的计算.");
CalculateMonthlyResult(yam.MS_Year, yam.MS_Month);
}
}
}
}
}
#endregion
Thread.Sleep(10000);
}
catch (Exception err)
{
writestr(err.Message);
}
}
/// <summary>
/// Функция, пресмятаща резултатите от погасителния план при анюитетни вноски
/// </summary>
/// <param name="p"></param>
/// <returns></returns>
private CreditCalcResults CalculateAnnuity(CreditCalcParams p)
{
CreditCalcResults res = new CreditCalcResults();
res.MonthlyInstallments = new List <MonthlyResult>();
res.MonthlyInstallments.Add(GetZeroMonth(p));
bool hasPromotion = p.PromotionPeriod.HasValue;
decimal pmtPromotional = hasPromotion ? PMT(p.PromotionRate.Value, p.Period - (p.GratisPeriod ?? 0), p.Amount) : 0;
decimal pmtNormal = hasPromotion ? 0 : PMT(p.Rate, p.Period - (p.GratisPeriod ?? 0), p.Amount);
decimal promotionalInstallment = 0;
for (int i = 1; i <= p.Period; i++)
{
MonthlyResult m = new MonthlyResult();
if (i == 1)
{
m.PrincipalRemainder = p.Amount;
}
else
{
m.PrincipalRemainder = res.MonthlyInstallments[i - 1].CurrentPrincipallRemainder();
}
m.RowNumber = i;
m.Date = DateTime.Now.AddMonths(i);
if (i <= (p.PromotionPeriod ?? 0))
{
m.RateInstallment = Math.Round((decimal)(m.PrincipalRemainder * p.PromotionRate / 100) / 12, 2);
if (i <= (p.GratisPeriod ?? 0))
{
m.Installment = m.RateInstallment;
}
else
{
m.Installment = pmtPromotional;
}
m.PrinicpalInstallment = m.Installment - m.RateInstallment;
promotionalInstallment += m.PrinicpalInstallment;
}
else
{
m.RateInstallment = Math.Round((decimal)(m.PrincipalRemainder * p.Rate / 100) / 12, 2);
if (i <= (p.GratisPeriod ?? 0))
{
m.Installment = m.RateInstallment;
}
else
{
if (pmtNormal == 0)
{
pmtNormal = PMT(p.Rate, p.Period - Math.Max(p.PromotionPeriod.Value, p.GratisPeriod ?? 0), p.Amount - promotionalInstallment);
}
if (i == p.Period)
{
pmtNormal = PMT(p.Rate, 1, m.PrincipalRemainder);
}
m.Installment = pmtNormal;
}
m.PrinicpalInstallment = m.Installment - m.RateInstallment;
}
if (i % 12 == 1 && i != 1)
{
m.Fees = MonthlyFeesCalc(p, m) + AnnualFeesCalc(p, m);
}
else
{
m.Fees = MonthlyFeesCalc(p, m);
}
m.TotalInstallment = m.Installment + m.Fees;
res.MonthlyInstallments.Add(m);
}
return(res);
}
/// <summary>
/// Функция, пресмятаща годишните такси
/// </summary>
/// <param name="p"></param>
/// <param name="m"></param>
/// <returns></returns>
private decimal AnnualFeesCalc(CreditCalcParams p, MonthlyResult m)
{
m.Fees = getFeeAmount(m.PrincipalRemainder, p.AnnualManagementFee, p.TreatAnnualManagementFeeAsPercent);
m.Fees += getFeeAmount(m.PrincipalRemainder, p.OtherAnnualFees, p.TreatOtherAnnualFeesAsPercent);
return(Math.Round(m.Fees, 2));
}
