public static decimal AccountSummaryReport(DateTime startDate, DateTime endDate, int userId, int month, int year)
{
decimal totalScore = 0; //合计提成金额
try
{
object obj = CommissionBusiness.isExistCommissionValueByMontAndSaler(userId, startDate, endDate, year, month);
if (obj != null)
{
DataTable dt = (DataTable)obj;
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
try
{
int flag = Utils.NvInt(dt.Rows[i]["flag"].ToString()); //支付方式
Cable c = CableBusiness.getCalbeByCableId(int.Parse(dt.Rows[i]["电路id"].ToString()));
Dateinterval interval = new Dateinterval();
//计算时间间隔
interval.StartDate = DateTime.Parse(dt.Rows[i]["账单开始时间"].ToString());
interval.EndDate = DateTime.Parse(dt.Rows[i]["账单结束时间"].ToString());
Nullable <DateTime> begindate = DateTime.Parse(dt.Rows[i]["提成开始时间"].ToString());
Nullable <DateTime> enddate = null;
if (dt.Rows[i]["提成结束时间"] != null && !string.IsNullOrEmpty(dt.Rows[i]["提成结束时间"].ToString()))
{
enddate = DateTime.Parse(dt.Rows[i]["提成结束时间"].ToString());
}
int days = ScoreBusiness.calCommissionDate(interval, begindate, enddate);//根据时间区间和提成日期计算有效天数
if (days <= 0)
{
continue;
}
int daysInBill = new TimeSpan(DateTime.Parse(dt.Rows[i]["账单开始时间"].ToString()).Ticks).Subtract(new TimeSpan(DateTime.Parse(dt.Rows[i]["账单结束时间"].ToString()).Ticks)).Duration().Days + 1; //计算间隔天数//计算账单结算区间天数
decimal ratio = decimal.Parse(dt.Rows[i]["提成比例"].ToString()) / 100;
decimal tax = decimal.Parse(dt.Rows[i]["税率"].ToString()) / 100;
decimal money = decimal.Parse(dt.Rows[i]["提成金额"].ToString());
if (money == 0)
{
continue;
}
decimal score = 0; //业绩计算
if (flag == (int)EnmWriteOffFlag.正常结算)
{
score = ScoreBusiness.calCommissionMoney(daysInBill, days, money, ratio, tax); //业绩计算
}
else if (flag == (int)EnmWriteOffFlag.补结算)
{
score = SuppmentWriteoffScore(interval.StartDate, interval.EndDate, c, money, userId, year, month);
}
totalScore += score;
}
catch (Exception ex)
{
Log.Warning(ex.Message);
continue;
}
}
}
}
return(totalScore);
}
catch (Exception ex)
{
Log.Warning(ex.Message);
return(-1);
}
}
public static decimal SuppmentWriteoffScore(DateTime startDate, DateTime endDate, Cable cable, decimal money, int userId, int year, int month)
{
try
{
string strPayType; //支付方式
List <Dateinterval> list = new List <Dateinterval>();
Dateinterval d;
switch (cable.Paytype)
{
case (int)EnmPayType.月付:
strPayType = "月付";
d = new Dateinterval();
d.StartDate = startDate;
d.EndDate = endDate;
d.Money = money;
list.Add(d);
break;
case (int)EnmPayType.季付:
strPayType = "季付";
for (DateTime tmpDate = startDate; tmpDate < endDate; tmpDate = tmpDate.AddMonths(3))
{
int tmp_year = tmpDate.Year;
int tmp_month = tmpDate.Month;
d = new Dateinterval();
if (tmpDate == startDate)
{
d.StartDate = tmpDate;
d.EndDate = DateTime.Parse(tmp_year.ToString() + "-" + tmp_month.ToString() + "-01").Date.AddMonths(1).AddDays(-1);
}
else
{
d.StartDate = DateTime.Parse(tmp_year.ToString() + "-" + tmp_month.ToString() + "-01").Date;
d.EndDate = startDate.AddMonths(1).AddDays(-1).Date;
if (d.EndDate > endDate)
{
d.EndDate = endDate;
}
}
d.Money = money / 4;
list.Add(d);
}
break;
case (int)EnmPayType.半年付:
strPayType = "半年付";
for (DateTime tmpDate = startDate; tmpDate < endDate; tmpDate = tmpDate.AddMonths(6))
{
int tmp_year = tmpDate.Year;
int tmp_month = tmpDate.Month;
d = new Dateinterval();
if (tmpDate == startDate)
{
d.StartDate = tmpDate;
d.EndDate = DateTime.Parse(tmp_year.ToString() + "-" + tmp_month.ToString() + "-01").Date.AddMonths(1).AddDays(-1);
}
else
{
d.StartDate = DateTime.Parse(tmp_year.ToString() + "-" + tmp_month.ToString() + "-01").Date;
d.EndDate = startDate.AddMonths(1).AddDays(-1).Date;
if (d.EndDate > endDate)
{
d.EndDate = endDate;
}
}
d.Money = money / 2;
list.Add(d);
}
break;
case (int)EnmPayType.一次性付:
strPayType = "一次性付";
d = new Dateinterval();
d.StartDate = startDate;
d.EndDate = DateTime.Parse(startDate.Year.ToString() + "-" + startDate.Month.ToString() + "-01").Date.AddMonths(1).AddDays(-1);
d.Money = money;
list.Add(d);
break;
default:
return(0);
}
//根据区间历史计算补结金额
for (int i = 0; i < list.Count; i++)
{
object value = CommissionBusiness.isExistCommissionValueByMontAndSaler(userId, list[i].StartDate, list[i].EndDate, year, month);
if (value != null)
{
DataTable dt = (DataTable)value;
DataRow[] rows = dt.Select("电路id=" + cable.Id + " and flag=" + (int)EnmWriteOffFlag.补结算 + "");
for (int j = 0; j < rows.Length; j++)
{
Nullable <DateTime> begindate = DateTime.Parse(rows[j]["提成开始时间"].ToString());
Nullable <DateTime> enddate = null;
if (dt.Rows[j]["提成结束时间"] != null && !string.IsNullOrEmpty(rows[j]["提成结束时间"].ToString()))
{
enddate = DateTime.Parse(rows[j]["提成结束时间"].ToString());
}
int days = ScoreBusiness.calCommissionDate(list[i], begindate, enddate);//根据时间区间和提成日期计算有效天数
if (days <= 0)
{
continue;
}
int daysInBill = new TimeSpan(list[i].StartDate.Ticks).Subtract(new TimeSpan(list[i].EndDate.Ticks)).Duration().Days + 1; //计算间隔天数//计算账单结算区间天数
decimal ratio = decimal.Parse(rows[i]["提成比例"].ToString()) / 100;
decimal tax = decimal.Parse(rows[i]["税率"].ToString()) / 100;
//decimal money = decimal.Parse(dt.Rows[i]["提成金额"].ToString());
decimal score = ScoreBusiness.calCommissionMoney(daysInBill, days, money, ratio, tax); //业绩计算
//int year = date.Year; //年度
//int month = date.Month; //月度
//TB_User saler = UserBusiness
//writeScoreTable(year, month, cusName, cablenumber, UserBusiness.findUserById(userId).LogName, Math.Round(score, 2), tbScore,
// calbeType, contractMoney, strPayType, money, contractStartTime, contractEndTime, proxy, receivable);
return(score);
}
}
}
return(0);
}
catch (Exception ex)
{
return(0);
}
}
/// <summary>
/// 计算预计提成金额
/// </summary>
/// <param name="startDate">查询起始日期</param>
/// <param name="endDate">查询截止日期</param>
/// <param name="cableNumber">电路代码</param>
/// <param name="salerId">渠道工号</param>
/// <param name="tbScore">数据表模板</param>
public static DataTable makeExpectedScore(DateTime startDate, DateTime endDate, string cableNumber, int salerId)
{
DataTable tbScore = createCol();
try
{
for (DateTime date = startDate; date <= endDate; date = date.AddMonths(1))
{
//Cable[] cables = CableBusiness.getCompleteCalbe(date.Year, date.Month, cableNumber); //查询电路代码信息
DataTable cables = CableBusiness.getCompleteCalbe(date.Year, date.Month, cableNumber, salerId); //查询电路代码信息
if (cables != null && cables.Rows.Count > 0) //判断是否为空或没值
{
for (int i = 0; i < cables.Rows.Count; i++) //循环电路合同
{
Cable tmpCalbe = CableBusiness.getCalbeByCableId(Utils.NvInt(cables.Rows[i]["id"]));
if (tmpCalbe == null)
{
continue;
}
string cusName = CustomerBusiness.findCustomerById(tmpCalbe.Customerid).Customername; //cables[i].Customer.Customername; //客户名称
string cablenumber = tmpCalbe.Cablenumber; //电路代码
string calbeType = tmpCalbe.Contracttype; //电路类型
string paytype = GlobalBusiness.getPayType(tmpCalbe.Paytype); //合同付款方式
if (tmpCalbe.Cablestatus == (int)EnmCableStatus.取消)
{
continue;
}
Nullable <DateTime> contractStartTime = tmpCalbe.Startdate; //合同起始日期
Nullable <DateTime> contractEndTime = tmpCalbe.Enddate; //合同截止日期
if (tmpCalbe.Removetime != null)
{
contractEndTime = tmpCalbe.Removetime;
}
decimal contractMoney = ScoreBusiness.isHaveWriteoff(DateTime.Parse(contractStartTime.ToString()), DateTime.Parse(contractEndTime.ToString()), tmpCalbe.Paytype, tmpCalbe.Money, date.Year, date.Month); //cables[i].Money; //合同金额
if (contractMoney == 0)
{
continue;
}
if (contractStartTime == null || contractEndTime == null) //如果合同没有起始或结束时间直接跳过进入下一个循环
{
continue;
}
Dateinterval interval = CableBusiness.anaylisContractMonth(contractStartTime, contractEndTime, date); //获取时间区间
decimal money = tmpCalbe.Money; //电路合同金额
int months = GlobalBusiness.getPayTypeForMonth(tmpCalbe.Paytype); //根据合同类型计算使用月数
decimal avgmoney = money / months; //平均每月费用
int cableId = tmpCalbe.Id; //电路代码ID
Commission[] cs = CommissionBusiness.getCommissions(cableId, salerId); //渠道提成信息
if (cs != null && cs.Length > 0) //根据电路代码和渠道查询提成
{
for (int j = 0; j < cs.Length; j++)
{
int year = date.Year; //年度
int month = date.Month; //月度
string saler = UserBusiness.findUserById(cs[j].Userid).USER_NAME; //渠道工号
int days = ScoreBusiness.calCommissionDate(interval, cs[j].Begintime, cs[j].Endtime); //根据时间区间和提成日期计算有效天数
if (days <= 0)
{
continue;
}
int daysInMonth = DateTime.DaysInMonth(year, month); //计算当月天数
decimal ratio = cs[j].Ratio / 100;
decimal tax = cs[j].Tax / 100;
decimal score = 0;
if (tmpCalbe.Paytype == (int)EnmPayType.月付)
{
score = ScoreBusiness.calCommissionMoney(daysInMonth, days, avgmoney, ratio, tax); //业绩计算
writeScoreTable(year, month, cusName, cablenumber, saler, Math.Round(score, 2), tbScore, calbeType, contractMoney, paytype, money, contractStartTime, contractEndTime, 0, 0, ratio, tax, daysInMonth);
}
else
{
score = ScoreBusiness.calCommissionMoney(avgmoney, ratio, tax); //业绩计算
writeScoreTable(interval.StartDate.Year, interval.StartDate.Month, cusName, cablenumber, saler, Math.Round(score, 2), tbScore, calbeType, contractMoney, paytype, money, contractStartTime, contractEndTime, 0, 0, ratio, tax, daysInMonth);
}
//writeScoreTable(year, month, cusName, cablenumber, saler, Math.Round(score, 2), tbScore, calbeType, contractMoney, paytype, money, contractStartTime, contractEndTime, 0, 0,ratio,tax);
}
}
else
{
continue;
}
}
}
else
{
//MessageHelper.ShowMessage("I005");
continue;
}
}
return(tbScore);
}
catch (Exception ex)
{
Log.Warning(ex.Message);
MessageHelper.ShowMessage("E999", "预计提成金额计算发生错误。");
return(tbScore);
}
}
/// <summary>
/// 计算实际提成金额
/// </summary>
/// <param name="startDate">查询起始日期</param>
/// <param name="endDate">查询截止日期</param>
/// <param name="cableNumber">电路代码</param>
/// <param name="userId">渠道工号</param>
/// <param name="tbScore">数据表模板</param>
public static DataTable makeActualScore(DateTime startDate, DateTime endDate, string cableNumber, int userId, string customerName)
{
DataTable tbScore = createCol();
try
{
for (DateTime date = startDate; date <= endDate; date = date.AddMonths(1))
{
object value = CommissionBusiness.isExistCommissionValueByMontAndSaler(userId, date, date.AddMonths(1).AddSeconds(-1), date.Year, date.Month);
if (value != null)
{
DataTable dt = (DataTable)value;
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
decimal score = 0;
int flag = Utils.NvInt(dt.Rows[i]["flag"].ToString());
Cable c = CableBusiness.getCalbeByCableId(int.Parse(dt.Rows[i]["电路id"].ToString()));
string cusName = CustomerBusiness.findCustomerById(int.Parse(dt.Rows[i]["客户id"].ToString())).Customername; //客户名称
string cablenumber = c.Cablenumber; //电路代码
string calbeType = c.Contracttype; //电路类型
string paytype = GlobalBusiness.getPayType(c.Paytype); //合同付款方式
decimal proxy = decimal.Parse(dt.Rows[i]["proxy"].ToString()); //代理费
decimal contractMoney = c.Money; //合同金额
decimal receivable = decimal.Parse(dt.Rows[i]["Receivable"].ToString()); //代理费
//计算时间间隔
Nullable <DateTime> contractStartTime = DateTime.Parse(dt.Rows[i]["账单开始时间"].ToString()); //账单结算起始日期
Nullable <DateTime> contractEndTime = DateTime.Parse(dt.Rows[i]["账单结束时间"].ToString()); //账单结算截止日期
if (contractStartTime == null || contractEndTime == null) //如果合同没有起始或结束时间
{
continue;
}
Dateinterval interval = new Dateinterval();
//计算时间间隔
interval.StartDate = DateTime.Parse(contractStartTime.ToString());
interval.EndDate = DateTime.Parse(contractEndTime.ToString());
Nullable <DateTime> begindate = DateTime.Parse(dt.Rows[i]["提成开始时间"].ToString());
Nullable <DateTime> enddate = null;
if (dt.Rows[i]["提成结束时间"] != null && !string.IsNullOrEmpty(dt.Rows[i]["提成结束时间"].ToString()))
{
enddate = DateTime.Parse(dt.Rows[i]["提成结束时间"].ToString());
}
int days = ScoreBusiness.calCommissionDate(interval, begindate, enddate);//根据时间区间和提成日期计算有效天数
if (days <= 0)
{
continue;
}
int daysInBill = new TimeSpan(DateTime.Parse(dt.Rows[i]["账单开始时间"].ToString()).Ticks).Subtract(new TimeSpan(DateTime.Parse(dt.Rows[i]["账单结束时间"].ToString()).Ticks)).Duration().Days + 1; //计算间隔天数//计算账单结算区间天数
decimal ratio = decimal.Parse(dt.Rows[i]["提成比例"].ToString()) / 100;
decimal tax = decimal.Parse(dt.Rows[i]["税率"].ToString()) / 100;
decimal money = decimal.Parse(dt.Rows[i]["提成金额"].ToString()); //账单金额
int year = date.Year; //年度
int month = date.Month; //月度
//TB_User saler = UserBusiness
if (flag == (int)EnmWriteOffFlag.正常结算)
{
score = ScoreBusiness.calCommissionMoney(daysInBill, days, money, ratio, tax); //业绩计算
writeScoreTable(year, month, cusName, cablenumber, UserBusiness.findUserById(userId).USER_NAME, Math.Round(score, 2), tbScore, calbeType, contractMoney, paytype, money, contractStartTime, contractEndTime, proxy, receivable, ratio, tax, daysInBill);
}
else if (flag == (int)EnmWriteOffFlag.补结算)
{
score = SuppmentWriteoffScore(interval.StartDate, interval.EndDate, c, money, userId, date.Year, date.Month);
writeScoreTable(interval.StartDate.Year, interval.StartDate.Month, cusName, cablenumber, UserBusiness.findUserById(userId).USER_NAME, Math.Round(score, 2), tbScore, calbeType, contractMoney, paytype, money, contractStartTime, contractEndTime, proxy, receivable, ratio, tax, daysInBill);
}
//writeScoreTable(year, month, cusName, cablenumber, UserBusiness.findUserById(userId).LogName, Math.Round(score, 2), tbScore, calbeType, contractMoney, paytype, money, contractStartTime, contractEndTime, proxy, receivable,ratio,tax);
}
}
}
}
return(tbScore);
}
catch (Exception ex)
{
Log.Warning(ex.Message);
MessageHelper.ShowMessage("E999", "预计提成金额计算发生错误。");
return(tbScore);
}
}