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"); } }