public ActionResult Save(int id = 0)
{
Finance_EveryDaySaleLog model = null;
if (id > 0)
{
model = Bll.BllFinance_EveryDaySaleLog.First(o => o.Id == id && o.Display != 2);
if (model == null)
{
return(LayerAlertSuccessAndRefresh("加载数据失败，未找到该数据"));
}
}
return(View(model));
}
/// <summary>
/// 数据填充映射模型
/// </summary>
/// <param name="row"></param>
/// <param name="logDate"></param>
/// <returns></returns>
private Finance_EveryDaySaleLog CreateEveryDaySaleLogModel(IRow row, DateTime addTime)
{
if (row == null)
{
return(null);
}
var model = new Finance_EveryDaySaleLog();
model.Year = row.GetCell(1)?.ToString().ToInt32();
model.Month = row.GetCell(2)?.ToString().ToInt32();
model.Day = row.GetCell(3)?.ToString().ToInt32();
model.DepartmentName = row.GetCell(4)?.ToString();
model.SaleName = row.GetCell(5)?.ToString() == "" ? null : row.GetCell(5)?.ToString();
model.Customer = row.GetCell(6)?.ToString() == "" ? null : row.GetCell(6)?.ToString();
model.ContractNumber = row.GetCell(7)?.ToString() == "" ? null : row.GetCell(7)?.ToString();
model.Supplier = row.GetCell(8)?.ToString() == "" ? null : row.GetCell(8)?.ToString();
model.ProductName = row.GetCell(9)?.ToString() == "" ? null : row.GetCell(9)?.ToString();
model.SPU = row.GetCell(10)?.ToString() == "" ? null : row.GetCell(10)?.ToString();
model.ProductSKU = row.GetCell(11)?.ToString() == "" ? null : row.GetCell(11)?.ToString();
model.ProductSpecification = row.GetCell(12)?.ToString() == "" ? null : row.GetCell(12)?.ToString();
model.SaleCount = getCellVal(row.GetCell(13));
model.SalePrice = getCellVal(row.GetCell(14));
model.Currency = row.GetCell(15)?.ToString();
model.ExchangeRate = getCellVal(row.GetCell(16));
model.MaterialUnitPrice = getCellVal(row.GetCell(18));
model.ProcessUnitPrice = getCellVal(row.GetCell(19));
model.ChangeCostNumber = getCellVal(row.GetCell(24));
model.ChangeCostMatter = getCellVal(row.GetCell(25));
model.ContributionMoney = getCellVal(row.GetCell(26));
model.ContributionRatio = getCellVal(row.GetCell(27));
model.AvgCoatUndue = getCellVal(row.GetCell(28));
model.AvgCoatCurrentdue = getCellVal(row.GetCell(29));
model.AvgCoatOverdue = getCellVal(row.GetCell(30));
model.CustomerContributionMoney = getCellVal(row.GetCell(31));
model.CustomerContributionRatio = getCellVal(row.GetCell(32));
model.Follow = getCellVal2(row.GetCell(33));
model.AddDate = addTime;
model.AddUserId = MyInfo.Id;
model.LastDate = addTime;
model.LastUserId = MyInfo.Id;
model.Display = 1;
model.SaleDate = $"{model.Year}-{model.Month}-{model.Day}".ToDateTime();
return(CountSaleDate(model));
}
/// <summary>
/// 根据数据计算收入成本
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
private Finance_EveryDaySaleLog CountSaleDate(Finance_EveryDaySaleLog model)
{
//销售收入
model.SaleIncome = (model.SaleCount != null && model.SalePrice != null && model.ExchangeRate != null)
? model.SaleCount * model.SalePrice * model.ExchangeRate
: null;
//材料成本合计
model.MaterialTotalPrice = (model.SaleCount != null && model.MaterialUnitPrice != null)
? model.SaleCount * model.MaterialUnitPrice
: null;
//加工成本合计
model.ProcessTotalPrice = (model.SaleCount != null && model.ProcessUnitPrice != null)
? model.SaleCount * model.ProcessUnitPrice
: null;
//毛利额
if (model.SaleIncome != null)
{
model.GrossProfit = model.SaleIncome;
if (model.MaterialTotalPrice != null)
{
model.GrossProfit -= model.MaterialTotalPrice;
}
if (model.ProcessTotalPrice != null)
{
model.GrossProfit -= model.ProcessTotalPrice;
}
//毛利率
model.GrossProfitRate = model.SaleIncome == 0
? 0
: Math.Round(((model.GrossProfit / model.SaleIncome) * 100).GetValueOrDefault(), 2);
}
if (model.SaleDate == Tools.Usual.Common.InitDateTime() ||
string.IsNullOrEmpty(model.SaleName) ||
string.IsNullOrEmpty(model.Customer) ||
string.IsNullOrEmpty(model.ContractNumber) ||
string.IsNullOrEmpty(model.ProductName))
{
return(null);
}
return(model);
}
private static Finance_EveryDaySaleLog CountData(List <Finance_EveryDaySaleLog> list, int year, int month)
{
var model = new Finance_EveryDaySaleLog();
model.Year = year;
model.Month = month;
model.SaleCount = list.Select(o => o.SaleCount)?.Sum() ?? 0;
model.SalePrice = list.Select(o => o.SalePrice)?.Sum() ?? 0;
model.ExchangeRate = list.Select(o => o.ExchangeRate)?.Sum() ?? 0;
model.SaleIncome = list.Select(o => o.SaleIncome)?.Sum() ?? 0;
model.MaterialUnitPrice = list.Select(o => o.MaterialUnitPrice)?.Sum() ?? 0;
model.ProcessUnitPrice = list.Select(o => o.ProcessUnitPrice)?.Sum() ?? 0;
model.MaterialTotalPrice = list.Select(o => o.MaterialTotalPrice)?.Sum() ?? 0;
model.ProcessTotalPrice = list.Select(o => o.ProcessTotalPrice)?.Sum() ?? 0;
model.GrossProfit = model.SaleIncome - model.MaterialTotalPrice - model.ProcessTotalPrice;
model.GrossProfitRate = Bll.BllFinance_Statistics.GetBusinessRate(model.GrossProfit, model.SaleIncome);
return(model);
}
public ActionResult Save(Finance_EveryDaySaleLog everyDaySaleLog)
{
everyDaySaleLog.Follow = Request["follow"] == "on" ? true : false;
//关键字段不能为空
if (everyDaySaleLog.SaleDate == null ||
string.IsNullOrWhiteSpace(everyDaySaleLog.SaleName) ||
string.IsNullOrWhiteSpace(everyDaySaleLog.Customer) ||
string.IsNullOrWhiteSpace(everyDaySaleLog.ProductName) ||
string.IsNullOrWhiteSpace(everyDaySaleLog.ContractNumber))
{
return(LayerMsgErrorAndReturn("字段不能为空！"));
}
//数据是否重复
Finance_EveryDaySaleLog model = Bll.BllFinance_EveryDaySaleLog.First(o => o.Id != everyDaySaleLog.Id &&
o.SaleDate == everyDaySaleLog.SaleDate &&
o.SaleName == everyDaySaleLog.SaleName &&
o.Customer == everyDaySaleLog.Customer &&
o.ContractNumber == everyDaySaleLog.ContractNumber &&
o.ProductName == everyDaySaleLog.ProductName &&
o.ProductSKU == everyDaySaleLog.ProductSKU &&
o.ProductSpecification == everyDaySaleLog.ProductSpecification &&
o.Display != 2);
if (model != null)
{
return(LayerMsgErrorAndReturn("添加数据失败，该条数据已存在，请确认后重新提交！"));
}
if (everyDaySaleLog.Id < 1)
{
model = new Finance_EveryDaySaleLog();
}
else
{
model = Bll.BllFinance_EveryDaySaleLog.First(o => o.Id == everyDaySaleLog.Id && o.Display != 2) ?? new Finance_EveryDaySaleLog();
}
model.SaleName = everyDaySaleLog.SaleName;
model.Customer = everyDaySaleLog.Customer;
model.ProductName = everyDaySaleLog.ProductName;
model.ProductSKU = everyDaySaleLog.ProductSKU;
model.ProductSpecification = everyDaySaleLog.ProductSpecification;
model.Year = everyDaySaleLog.SaleDate.GetValueOrDefault().Year;
model.Month = everyDaySaleLog.SaleDate.GetValueOrDefault().Month;
model.Day = everyDaySaleLog.SaleDate.GetValueOrDefault().Day;
model.SaleDate = everyDaySaleLog.SaleDate;
model.DepartmentName = everyDaySaleLog.DepartmentName;
model.ContractNumber = everyDaySaleLog.ContractNumber;
model.SaleCount = everyDaySaleLog.SaleCount;
model.SalePrice = everyDaySaleLog.SalePrice;
model.Currency = everyDaySaleLog.Currency;
model.ExchangeRate = everyDaySaleLog.ExchangeRate;
model.MaterialUnitPrice = everyDaySaleLog.MaterialUnitPrice;
model.ProcessUnitPrice = everyDaySaleLog.ProcessUnitPrice;
model.ChangeCostNumber = everyDaySaleLog.ChangeCostNumber;
model.ChangeCostMatter = everyDaySaleLog.ChangeCostMatter;
model.ContributionMoney = everyDaySaleLog.ContributionMoney;
model.ContributionRatio = everyDaySaleLog.ContributionRatio;
model.AvgCoatUndue = everyDaySaleLog.AvgCoatUndue;
model.AvgCoatCurrentdue = everyDaySaleLog.AvgCoatCurrentdue;
model.AvgCoatOverdue = everyDaySaleLog.AvgCoatOverdue;
model.CustomerContributionMoney = everyDaySaleLog.CustomerContributionMoney;
model.CustomerContributionRatio = everyDaySaleLog.CustomerContributionRatio;
model.Follow = everyDaySaleLog.Follow;
model.SPU = everyDaySaleLog.SPU;
model.LastDate = DateTime.Now;
model.LastUserId = MyInfo.Id;
model = CountSaleDate(model);
bool isok = false;
//没有ID就新增，反之修改
if (model.Id < 1)
{
model.AddDate = model.LastDate;
model.AddUserId = model.LastUserId;
model.Display = 1;
isok = Bll.BllFinance_EveryDaySaleLog.Insert(model) > 0;
}
else
{
isok = Bll.BllFinance_EveryDaySaleLog.Update(model) > 0;
}
//重新统计
if (isok)
{
Bll.BllFinance_Statistics.AgainCountData(new List <Finance_Statistics>
{
new Finance_Statistics()
{
SaleDate = everyDaySaleLog.SaleDate,
DepartmentName = everyDaySaleLog.DepartmentName,
LastUserId = MyInfo.Id,
LastDate = everyDaySaleLog.LastDate
}
});
}
return(LayerMsgSuccessAndRefresh("保存" + (isok ? "成功" : "失败")));
}
/// <summary>
/// 标记哪些部门需要重新统计
/// </summary>
/// <returns></returns>
private List <Finance_Statistics> SignNeedUpdateData(List <Finance_Statistics> statistics, Finance_EveryDaySaleLog everyDaySaleLog)
{
//部门名称不能为空
if (string.IsNullOrWhiteSpace(everyDaySaleLog.DepartmentName))
{
return(statistics);
}
var list = (from o in statistics where o.SaleDate == everyDaySaleLog.SaleDate && o.DepartmentName == everyDaySaleLog.DepartmentName select o).ToList();
if (list == null || list.Count < 1)
{
statistics.Add(new Finance_Statistics
{
SaleDate = everyDaySaleLog.SaleDate,
DepartmentName = everyDaySaleLog.DepartmentName,
LastUserId = MyInfo.Id,
LastDate = everyDaySaleLog.LastDate
});
}
return(statistics);
}
private static void AddData(ref NPOIHelper2 sheet, string dataIndex, int rowIndex, Finance_EveryDaySaleLog item, ICellStyle cellStyle, ICellStyle cellStyle2)
{
sheet.CreateRow(rowIndex);
int i = 0;
//序号
sheet.CreateCell(i++, dataIndex, cellStyle);
//年
sheet.CreateCell(i++, item.Year.ToString(), cellStyle);
//月
sheet.CreateCell(i++, item.Month.ToString(), cellStyle);
//日
sheet.CreateCell(i++, item.Day.ToString(), cellStyle);
//部门
sheet.CreateCell(i++, item.DepartmentName, cellStyle);
//销售人员
sheet.CreateCell(i++, item.SaleName, cellStyle);
//客户/店铺
sheet.CreateCell(i++, item.Customer, cellStyle);
//合同号
sheet.CreateCell(i++, item.ContractNumber, cellStyle);
//供应商
sheet.CreateCell(i++, item.Supplier, cellStyle);
//产品名称
sheet.CreateCell(i++, item.ProductName, cellStyle);
//SPU
sheet.CreateCell(i++, item.SPU, cellStyle);
//产品SKU
sheet.CreateCell(i++, item.ProductSKU, cellStyle);
//产品规格
sheet.CreateCell(i++, item.ProductSpecification, cellStyle);
//销售数量
sheet.CreateCell(i++, item.SaleCount?.ToString("f2") ?? "0", cellStyle);
//销售单价
sheet.CreateCell(i++, item.SalePrice?.ToString("f2") ?? "0", cellStyle);
//货币
sheet.CreateCell(i++, item.Currency, cellStyle);
//汇率
sheet.CreateCell(i++, item.ExchangeRate?.ToString("f2") ?? "0", cellStyle);
//销售收入
sheet.CreateCell(i++, item.SaleIncome?.ToString("f2") ?? "0", cellStyle);
//单价料
sheet.CreateCell(i++, item.MaterialUnitPrice?.ToString("f2") ?? "0", cellStyle);
//单价工
sheet.CreateCell(i++, item.ProcessUnitPrice?.ToString("f2") ?? "0", cellStyle);
//合计料
sheet.CreateCell(i++, item.MaterialTotalPrice?.ToString("f2") ?? "0", cellStyle);
//合计工
sheet.CreateCell(i++, item.ProcessTotalPrice?.ToString("f2") ?? "0", cellStyle);
//毛益额
sheet.CreateCell(i++, item.GrossProfit?.ToString("f2") ?? "0", cellStyle);
//毛益率
sheet.CreateCell(i++, item.GrossProfitRate?.ToString("f2") + "%" ?? "", cellStyle);
//量
sheet.CreateCell(i++, item.ChangeCostNumber?.ToString("f2") ?? "0", cellStyle);
//事
sheet.CreateCell(i++, item.ChangeCostMatter?.ToString("f2") ?? "0", cellStyle);
//金额
sheet.CreateCell(i++, item.ContributionMoney?.ToString("f2") ?? "0", cellStyle);
//比例
sheet.CreateCell(i++, item.ContributionRatio?.ToString("f2") ?? "0", cellStyle);
//未到期
sheet.CreateCell(i++, item.AvgCoatUndue?.ToString("f2") ?? "0", cellStyle);
//当期
sheet.CreateCell(i++, item.AvgCoatCurrentdue?.ToString("f2") ?? "0", cellStyle);
//逾期
sheet.CreateCell(i++, item.AvgCoatOverdue?.ToString("f2") ?? "0", cellStyle);
//金额
sheet.CreateCell(i++, item.CustomerContributionMoney?.ToString("f2") ?? "0", cellStyle);
//比例
sheet.CreateCell(i++, item.ContributionRatio?.ToString("f2") ?? "0", cellStyle);
//是否关注
sheet.CreateCell(i++, (item.Follow != null ? (item.Follow == true ? "是" : "否") : "否"), cellStyle2);
}