public void saveReport(string savePath, int year, int month)
{
Workbook wk = new Workbook();
TB_User[] sales = UserBusiness.getAllSalersAndWrite(); //获取所有销售渠道和完工录入信息
if (Global.g_usergroupid != (int)EnmUserRole.财务 && Global.g_usergroupid != (int)EnmUserRole.全部 && Global.g_usergroupid != (int)EnmUserRole.销售总监)
{
TB_User u = UserBusiness.findUserById(Global.g_userid);
List <TB_User> list = new List <TB_User>();
list.Add(u);
sales = list.ToArray();
}
string str = year.ToString() + "-" + month.ToString() + "-01";
DateTime startdate = DateTime.Parse(str).Date;
DateTime enddate = DateTime.Parse(str).AddMonths(1).AddDays(-1).Date;
for (int i = 0; i < sales.Length; i++)
{
DataTable salesData = ScoreBusiness.makeActualScore(startdate, enddate, "", sales[i].Id, "");
if (salesData.Rows.Count > 0)
{
writeSalesReport(wk, i, salesData, year, month, sales[i].USER_NAME);
try
{
wk.Password = DES.Decode(sales[i].PASSWORD, Global.DB_PWDKEY);
wk.Save(savePath + "//" + sales[i].USER_NAME + " " + year + "-" + month + "月度提成清单1.xls");
}
catch (Exception ex)
{
MessageHelper.ShowMessage("E999", "错误" + ex.Message + "\n此文件导出失败，其他文件仍将被导出到所选目录。");
}
}
}
MessageHelper.ShowMessage("I007");
}
public DataTable saveExcel(int StartYear, int EndYear, int StartMonth, int EndMonth, int salerId, string salerName, string savePath)
{
try
{
string tmpstartDate = StartYear.ToString() + "-" + StartMonth.ToString() + "-01";
DateTime startDate = DateTime.Parse(tmpstartDate);
string tmpendDate = EndYear.ToString() + "-" + EndMonth.ToString() + "-01";
DateTime endDate = DateTime.Parse(tmpendDate).AddMonths(1).AddDays(-1); //查询结束月最后一天
DataTable salesTrendcy = ScoreBusiness.makeActualScore(startDate, endDate, "", salerId, "");
DataTable tb = createCol();
for (DateTime date = startDate; date < endDate; date = date.AddMonths(1))
{
int tmpyear = date.Year;
int tmpmonth = date.Month;
decimal amount = 0;
string yearmonth = tmpyear.ToString() + "年" + tmpmonth + "月";
DataRow[] rows = salesTrendcy.Select("年度=" + tmpyear + " and 月度=" + tmpmonth + "");
for (int i = 0; i < rows.Length; i++)
{
amount += Utils.NvDecimal(rows[i]["应收月租费"]);
}
DataRow r = tb.NewRow();
r["yearmonth"] = yearmonth;
r["amount"] = amount;
tb.Rows.Add(r);
}
return(tb);
}
catch (Exception ex)
{
Log.Error(ex.Message);
return(new DataTable());
MessageHelper.ShowMessage("E999", "销售业绩走势导出失败。");
}
}
public void exportProfitReport(string outfile, int year, int month, float ptax)
{
try
{
int line = 0;
decimal totalreceive = 0;
decimal totalcost = 0;
decimal totalprofit = 0;
decimal totalafterTax = 0;
float tax = float.Parse(ptax.ToString()) / 100; //税率
string str = year.ToString() + "-" + month.ToString() + "-01";
DateTime startdate = DateTime.Parse(str);
DateTime enddate = DateTime.Parse(str).AddMonths(1).AddDays(-1).Date;
Customer[] customers = CustomerBusiness.getAllCustomers();
if (customers.Length > 0)
{
for (int i = 0; i < customers.Length; i++)
{
//string condition = " and b.year=" + year + " and b.month=" + month + " and cu.customername='" + customers[i].Customername + "'";
DataTable tb = ScoreBusiness.makeActualScore(startdate, enddate, "", 0, customers[i].Customername); //.getActual(condition, enddate);
decimal tmptotal = 0; //电路金额合计
decimal profit = 0;
if (tb.Rows.Count > 0)
{
for (int j = 0; j < tb.Rows.Count; j++)
{
tmptotal += Utils.NvDecimal(tb.Rows[j]["提成金额"].ToString()); //渠道提成金额
}
}
else
{
continue;
}
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line, 1].PutValue(customers[i].Customername);
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line, 1].Style.Copy(cellstyle);
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line, 2].PutValue(tb.Rows[0]["电路代码"].ToString());
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line, 2].Style.Copy(cellstyle);
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line, 3].PutValue(DateTime.Parse(tb.Rows[0]["结算起始日期"].ToString()).ToString("yyyy-MM-dd"));
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line, 3].Style.Copy(cellstyle);
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line, 4].PutValue(DateTime.Parse(tb.Rows[0]["结算截止日期"].ToString()).ToString("yyyy-MM-dd"));
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line, 4].Style.Copy(cellstyle);
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line, 5].PutValue(Utils.NvDecimal(tb.Rows[0]["销账金额"].ToString()));
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line, 5].Style.Copy(cellstyle);
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line, 6].PutValue(Utils.NvDecimal(tb.Rows[0]["代理费"].ToString()));
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line, 6].Style.Copy(cellstyle);
totalreceive += Utils.NvDecimal(tb.Rows[0]["代理费"].ToString());
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line, 7].PutValue(ptax + "%");
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line, 7].Style.Copy(cellstyle);
decimal afterTaxAmoun = Utils.NvDecimal(tb.Rows[0]["代理费"].ToString()) * (1 - Utils.NvDecimal(ptax) / 100);
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line, 8].PutValue(Math.Round(afterTaxAmoun, 2));
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line, 8].Style.Copy(this.cellstyle);
totalafterTax += afterTaxAmoun;
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line, 9].PutValue(Math.Round(tmptotal, 2));
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line, 9].Style.Copy(this.cellstyle);
totalcost += tmptotal;
profit = afterTaxAmoun - tmptotal;
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line, 10].PutValue(Math.Round(profit, 2));
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line, 10].Style.Copy(this.cellstyle);
totalprofit = totalprofit + profit;
line++;
}
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line + 2, 1].PutValue("合计:");
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line + 2, 6].PutValue(Math.Round(totalreceive, 2));
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line + 2, 8].PutValue(Math.Round(totalafterTax, 2));
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line + 2, 9].PutValue(Math.Round(totalcost, 2));
profitSheet.Cells[PROFITDATA_STARTLINE_INDEX + line + 2, 10].PutValue(Math.Round(totalprofit, 2));
profitSheet.Cells[1, 0].Style = profitSheet.Cells[1, 1].Style;
book.Password = DES.Decode(Global.g_password, Global.DB_PWDKEY);
book.Save(outfile);
MessageHelper.ShowMessage("I007");
}
else
{
MessageHelper.ShowMessage("I014");
}
}
catch (Exception ex)
{
Log.Error(ex.Message);
MessageHelper.ShowMessage("I008");
}
}