/// <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
}
