/// <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);
}
}
public static Salebills[] getBillsByCondition(int year, int month, string cable, string customername)
{
try
{
Cable[] cb;
Salebills[] bills;
Customer cus;
if (!string.IsNullOrEmpty(cable)) //根据电路代码模糊查询
{
cb = CableDao.FindAll(new EqExpression("Isdeleted", (int)EnmIsdeleted.使用中),
new NotExpression(new EqExpression("Cablestatus", (int)EnmCableStatus.未完工)),
new NotExpression(new EqExpression("Cablestatus", (int)EnmCableStatus.已拆机)),
new NotExpression(new EqExpression("Cablestatus", (int)EnmCableStatus.取消)),
new LikeExpression("Cablenumber", "%" + cable + "%"));
}
else //查找全部电路代码
{
cb = CableDao.FindAll(new EqExpression("Isdeleted", (int)EnmIsdeleted.使用中),
new NotExpression(new EqExpression("Cablestatus", (int)EnmCableStatus.未完工)),
new NotExpression(new EqExpression("Cablestatus", (int)EnmCableStatus.已拆机)),
new NotExpression(new EqExpression("Cablestatus", (int)EnmCableStatus.取消)));
}
if (cb != null || cb.Length > 0)
{
object[] cableids = new object[cb.Length];
for (int i = 0; i < cb.Length; i++)
{
cableids[i] = cb[i].Id;
}
InExpression inexpress = new InExpression("Cableid", cableids); //将所有电路代码作为查询条件
if (!string.IsNullOrEmpty(customername))
{
cus = CustomerBusiness.findCustomerByName(customername);
if (cus != null)
{
EqExpression cuseqex = new EqExpression("Customerid", cus.Id);
//只查正常结算和预结算的账单信息
bills = SalebillsDao.FindAll(new EqExpression("Isdeleted", (int)EnmIsdeleted.使用中),
new OrExpression(new EqExpression("Flag", (int)EnmWriteOffFlag.正常结算), new EqExpression("Flag", (int)EnmWriteOffFlag.预结算)),
new EqExpression("Year", year),
new EqExpression("Month", month),
inexpress, cuseqex);
}
else
{
//只查正常结算和预结算的账单信息
bills = SalebillsDao.FindAll(new EqExpression("Isdeleted", (int)EnmIsdeleted.使用中),
new OrExpression(new EqExpression("Flag", (int)EnmWriteOffFlag.正常结算), new EqExpression("Flag", (int)EnmWriteOffFlag.预结算)),
new EqExpression("Year", year),
new EqExpression("Month", month),
inexpress);
}
}
else
{
//只查正常结算和预结算的Excel账单信息
bills = SalebillsDao.FindAll(new EqExpression("Isdeleted", (int)EnmIsdeleted.使用中),
new OrExpression(new EqExpression("Flag", (int)EnmWriteOffFlag.正常结算), new EqExpression("Flag", (int)EnmWriteOffFlag.预结算)),
new EqExpression("Year", year),
new EqExpression("Month", month),
inexpress);
}
return(bills);
}
return(null);
}
catch (Exception ex)
{
Log.Error(ex.Message);
MessageHelper.ShowMessage("E999", ex.Message);
return(null);
}
}
/// <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);
}
}