/// <summary> /// 应收账款管理表 /// </summary> private void ExportFinanceInfo() { exportDt = new DataTable(); exportDt.Columns.Add("SLIP_NUMBER", Type.GetType("System.String")); //开票内部订单编号 exportDt.Columns.Add("SLIP_DATE", Type.GetType("System.String")); //开票日期 exportDt.Columns.Add("SERIAL_NUMBER", Type.GetType("System.String")); //发票内容 exportDt.Columns.Add("MODEL_NUMBER", Type.GetType("System.String")); //机械型号 exportDt.Columns.Add("CUSTOMER_PO_NUMBER", Type.GetType("System.String")); //合同编号 exportDt.Columns.Add("CUSTOMER_NAME", Type.GetType("System.String")); //代理店 exportDt.Columns.Add("ENDUSER_CUSTOMER_NAME", Type.GetType("System.String")); //最终客户 exportDt.Columns.Add("SHIPMENT_SLIP_DATE", Type.GetType("System.String")); //出库日期 exportDt.Columns.Add("QUANTITY", Type.GetType("System.Decimal")); //数量 exportDt.Columns.Add("INVOICE_NUMBER", Type.GetType("System.String")); //发票No exportDt.Columns.Add("INVOICE_AMOUNT_WITHOUT_TAX", Type.GetType("System.Decimal")); //未含税金额 exportDt.Columns.Add("INVOICE_AMOUNT", Type.GetType("System.Decimal")); //含税金额 exportDt.Columns.Add("CUSTOMER_CLAIM_DATE", Type.GetType("System.String")); //收款预定日期 exportDt.Columns.Add("RECEIVABLES_DATE_1", Type.GetType("System.String")); //收款日期1 exportDt.Columns.Add("RECEIVABLES_1", Type.GetType("System.Decimal")); //收款金额1 exportDt.Columns.Add("RECEIVABLES_DATE_2", Type.GetType("System.String")); //收款日期2 exportDt.Columns.Add("RECEIVABLES_2", Type.GetType("System.Decimal")); //收款金额2 exportDt.Columns.Add("RECEIVABLES_DATE_3", Type.GetType("System.String")); //收款日期3 exportDt.Columns.Add("RECEIVABLES_3", Type.GetType("System.Decimal")); //收款金额3 exportDt.Columns.Add("UN_RECEIVABLES", Type.GetType("System.Decimal")); //未收款金额 exportDt.Columns.Add("TOTAL_RECEIVABLES", Type.GetType("System.Decimal")); //总收款金额 DataTable exportMachineDt = exportDt.Clone(); DataTable exportPartsDt = exportDt.Clone(); string invoiceNumber = ""; DataRow eDr = null; //获得机械本体应收账款 #region DataTable machineDT = bInvoice.GetMachineAccountReceivable(GetConduction()).Tables[0]; foreach (DataRow dr in machineDT.Rows) { if (!invoiceNumber.Equals(dr["INVOICE_NUMBER"])) { if (invoiceNumber != "") { exportMachineDt.Rows.Add(eDr); } eDr = exportMachineDt.NewRow(); eDr["SLIP_NUMBER"] = dr["SLIP_NUMBER"]; //开票内部订单编号 eDr["SLIP_DATE"] = dr["SLIP_DATE"]; //开票日期 eDr["SERIAL_NUMBER"] = dr["SERIAL_NUMBER"]; //发票内容 eDr["MODEL_NUMBER"] = dr["MODEL_NUMBER"]; //机械型号 eDr["CUSTOMER_PO_NUMBER"] = dr["CUSTOMER_PO_NUMBER"]; //合同编号 eDr["CUSTOMER_NAME"] = dr["CUSTOMER_NAME"]; //代理店 eDr["ENDUSER_CUSTOMER_NAME"] = dr["ENDUSER_CUSTOMER_NAME"]; //最终客户 eDr["SHIPMENT_SLIP_DATE"] = dr["SHIPMENT_SLIP_DATE"]; //出库日期 eDr["QUANTITY"] = 1; //dr["QUANTITY"]; //数量 eDr["INVOICE_NUMBER"] = dr["INVOICE_NUMBER"]; //发票No eDr["INVOICE_AMOUNT_WITHOUT_TAX"] = GetAmountWithoutTax(dr["INVOICE_AMOUNT"], dr["TAX_RATE"]); //未含税金额 eDr["INVOICE_AMOUNT"] = dr["INVOICE_AMOUNT"]; //含税金额 eDr["CUSTOMER_CLAIM_DATE"] = dr["CUSTOMER_CLAIM_DATE"]; //收款预定日期 invoiceNumber = CConvert.ToString(dr["INVOICE_NUMBER"]); } } if (eDr != null) { exportMachineDt.Rows.Add(eDr); } #endregion //获得机械部件应收账款 #region DataTable partsDT = bInvoice.GetPartsAccountReceivable(GetConduction()).Tables[0]; invoiceNumber = ""; eDr = null; foreach (DataRow dr in partsDT.Rows) { if (!invoiceNumber.Equals(dr["INVOICE_NUMBER"])) { if (invoiceNumber != "") { exportPartsDt.Rows.Add(eDr); } eDr = exportPartsDt.NewRow(); eDr["SLIP_NUMBER"] = dr["SLIP_NUMBER"]; //开票内部订单编号 eDr["SLIP_DATE"] = dr["SLIP_DATE"]; //开票日期 eDr["SERIAL_NUMBER"] = dr["SERIAL_NUMBER"]; //发票内容 eDr["MODEL_NUMBER"] = ""; //dr["MODEL_NUMBER"]; //机械型号 eDr["CUSTOMER_PO_NUMBER"] = dr["CUSTOMER_PO_NUMBER"]; //合同编号 eDr["CUSTOMER_NAME"] = dr["CUSTOMER_NAME"]; //代理店 eDr["ENDUSER_CUSTOMER_NAME"] = dr["ENDUSER_CUSTOMER_NAME"]; //最终客户 eDr["SHIPMENT_SLIP_DATE"] = dr["SHIPMENT_SLIP_DATE"]; //出库日期 eDr["QUANTITY"] = 1; //dr["QUANTITY"]; //数量 eDr["INVOICE_NUMBER"] = dr["INVOICE_NUMBER"]; //发票No eDr["INVOICE_AMOUNT_WITHOUT_TAX"] = GetAmountWithoutTax(dr["INVOICE_AMOUNT"], dr["TAX_RATE"]); //未含税金额 eDr["INVOICE_AMOUNT"] = dr["INVOICE_AMOUNT"]; //含税金额 eDr["CUSTOMER_CLAIM_DATE"] = dr["CUSTOMER_CLAIM_DATE"]; //收款预定日期 invoiceNumber = CConvert.ToString(dr["INVOICE_NUMBER"]); } } if (eDr != null) { exportPartsDt.Rows.Add(eDr); } #endregion decimal totalAmount = 0; decimal machineTotalAmount = 0; decimal machineTotalAmountWithoutTax = 0; decimal partTotalAmountWithoutTax = 0; //付款信息的实装 #region //获得开票付款信息 DataTable receiptDT = bInvoice.GetReceiptMatch(GetReceiptMatchConduction()).Tables[0]; //机械本体的开票信息的实装 foreach (DataRow dr in exportMachineDt.Rows) { int i = 1; foreach (DataRow rDr in receiptDT.Rows) { if (CConvert.ToString(dr["SLIP_NUMBER"]).Equals(rDr["SLIP_NUMBER"])) { dr["RECEIVABLES_DATE_" + i] = rDr["SLIP_DATE"]; dr["RECEIVABLES_" + i] = rDr["RECEIPT_AMOUNT"]; i++; if (i > 3) { break; } } } dr["TOTAL_RECEIVABLES"] = addDecimal(dr["RECEIVABLES_1"], dr["RECEIVABLES_2"], dr["RECEIVABLES_3"]); dr["UN_RECEIVABLES"] = CConvert.ToDecimal(dr["INVOICE_AMOUNT"]) - CConvert.ToDecimal(dr["TOTAL_RECEIVABLES"]); machineTotalAmountWithoutTax += CConvert.ToDecimal(dr["INVOICE_AMOUNT_WITHOUT_TAX"]); machineTotalAmount += CConvert.ToDecimal(dr["INVOICE_AMOUNT"]); totalAmount += machineTotalAmount; } //机械部件的开票信息的实装 foreach (DataRow dr in exportPartsDt.Rows) { int i = 1; foreach (DataRow rDr in receiptDT.Rows) { if (CConvert.ToString(dr["SLIP_NUMBER"]).Equals(rDr["SLIP_NUMBER"])) { dr["RECEIVABLES_DATE_" + i] = rDr["SLIP_DATE"]; dr["RECEIVABLES_" + i] = rDr["RECEIPT_AMOUNT"]; i++; if (i > 3) { break; } } } dr["TOTAL_RECEIVABLES"] = addDecimal(dr["RECEIVABLES_1"], dr["RECEIVABLES_2"], dr["RECEIVABLES_3"]); dr["UN_RECEIVABLES"] = CConvert.ToDecimal(dr["INVOICE_AMOUNT"]) - CConvert.ToDecimal(dr["TOTAL_RECEIVABLES"]); partTotalAmountWithoutTax += CConvert.ToDecimal(dr["INVOICE_AMOUNT_WITHOUT_TAX"]); totalAmount += CConvert.ToDecimal(dr["INVOICE_AMOUNT"]);; } #endregion //数据的导出 string[] headerTitles = { "发票内部编号", "発行年月", "発票内容", "機械型番", "契約書No.", "商社名", "顧客名", "出荷日", "台数", "発票No.", "金額(税抜)(元)", "金額(税込)(元)", "入金予定日", "入金日1", "入金額(元)1", "入金日2", "入金額(元)2", "入金日3", "入金額(元)3", "未入金額(元)", "入金総額(元)" }; #region Hashtable ht = new Hashtable(); ht.Add("&DATE", dateFrom.Text + (dateTo.Checked == true ? " 至 " + dateTo.Text : "")); ht.Add("&TOTAL_AMOUNT", totalAmount); ht.Add("&MACHINE_TOTAL_AMOUNT", machineTotalAmount); ht.Add("&B_MACHINE_TOTAL_AMOUNT_WITHOUT_TAX", machineTotalAmountWithoutTax); ht.Add("&PART_TOTAL_AMOUNT_WITHOUT_TAX", partTotalAmountWithoutTax); SaveFileDialog sf = new SaveFileDialog(); sf.FileName = "LZ_RECEIVABLES_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; sf.Filter = "(文件)|*.xls;*.xlsx"; if (sf.ShowDialog(this) == DialogResult.OK) { if (exportMachineDt.Rows.Count > 0 || exportPartsDt.Rows.Count > 0) { int ret = CExport.ExportReceivables(@"rpt\Receivables.frx", sf.FileName, exportMachineDt, exportPartsDt, ht, headerTitles); if (CConstant.EXPORT_FAILURE.Equals(ret)) { MessageBox.Show("导出失败。", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } else if (CConstant.EXPORT_SUCCESS.Equals(ret)) { MessageBox.Show("导出成功。", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information); } else if (CConstant.EXPORT_RUNNING.Equals(ret)) { MessageBox.Show("文件正在运行,重新生成文件失败。", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } else if (CConstant.EXPORT_TEMPLETE_FILE_NOT_EXIST.Equals(ret)) { MessageBox.Show("模版文件不存在。", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } this.Close(); } else { MessageBox.Show("明细信息不存在。", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } } #endregion }