/// <summary>
/// OEM制品管理表
/// </summary>
public void ExportOEMProduct()
{
//OEM制品管理表所需要的导出字段
#region
exportDt.Columns.Clear();
exportDt.Columns.Add("MACHINE_CODE", Type.GetType("System.String")); //机械编号
exportDt.Columns.Add("MACHINE_NAME", Type.GetType("System.String")); //机械名称
exportDt.Columns.Add("BODY_PURCHASE_ORDER_SLIP_NUMBER", Type.GetType("System.String")); //机械本体采购订单编号D:\龙泽ERP\ERP.WinUI\Finance\FrmSalesSearch.cs
exportDt.Columns.Add("BODY_INVOICE_NUMBER_LOCAL", Type.GetType("System.String")); //机械本体采购发票编号
exportDt.Columns.Add("BODY_PURCHASE_SLIP_DATE", Type.GetType("System.String")); //机械本体采购发票日期
exportDt.Columns.Add("BODY_INVOICE_AMOUNT", Type.GetType("System.String")); //机械本体采购发票金额(含税）
exportDt.Columns.Add("PART_PURCHASE_ORDER_SLIP_NUMBER", Type.GetType("System.String")); //机械部件采购订单编号(国内)
exportDt.Columns.Add("PART_INVOICE_NUMBER_LOCAL", Type.GetType("System.String")); //机械部件采购发票编号(国内)
exportDt.Columns.Add("PART_PURCHASE_SLIP_DATE", Type.GetType("System.String")); //机械部件采购发票日期(国内)
exportDt.Columns.Add("PART_INVOICE_AMOUNT", Type.GetType("System.Decimal")); //机械部件采购发票金额(含税）(国内)
exportDt.Columns.Add("FOREIGN_PURCHASE_ORDER_SLIP_NUMBER", Type.GetType("System.String")); //机械部件采购订单编号(国外)
exportDt.Columns.Add("FOREIGN_PURCHASE_INVOICE_NUMBER", Type.GetType("System.String")); //机械部件采购发票编号(国外)
exportDt.Columns.Add("FOREIGN_PURCHASE_SLIP_DATE", Type.GetType("System.String")); //机械部件采购发票日期(国外)
exportDt.Columns.Add("FOREIGN_INVOICE_AMOUNT", Type.GetType("System.Decimal")); //机械部件采购发票金额(含税）(国外)
exportDt.Columns.Add("EXCHANGE_RATE", Type.GetType("System.Decimal")); //外币汇率
exportDt.Columns.Add("RMB_INVOICE_AMOUNT", Type.GetType("System.Decimal")); //外币拆合人民币
exportDt.Columns.Add("TAX_AMOUNT", Type.GetType("System.Decimal")); //税金
exportDt.Columns.Add("TOTAL", Type.GetType("System.Decimal")); //小计
exportDt.Columns.Add("TOTAL_AMOUNT", Type.GetType("System.Decimal")); //总金额(采购金额）
exportDt.Columns.Add("ORDER_SLIP_NUMBER", Type.GetType("System.String")); //销售订单编号
exportDt.Columns.Add("CUSTOMER_PO_NUMBER", Type.GetType("System.String")); //销售合同编号
exportDt.Columns.Add("SHIPMENT_SLIP_DATE", Type.GetType("System.String")); //出库日期
exportDt.Columns.Add("SALES_INVOICE_NUMBER", Type.GetType("System.String")); //销售发票NO
exportDt.Columns.Add("ORDER_SLIP_DATE", Type.GetType("System.String")); //销售日期
exportDt.Columns.Add("AMOUNT_WITHOUT_TAX", Type.GetType("System.Decimal")); //不含税金额
exportDt.Columns.Add("TOTAL_BODY_AMOUNT", Type.GetType("System.Decimal")); //机械本体不含税金额
exportDt.Columns.Add("PART_AMOUNT", Type.GetType("System.Decimal")); //机械部件不含税金额
exportDt.Columns.Add("GROSS_PROFIT", Type.GetType("System.Decimal")); //利润
exportDt.Columns.Add("CHECK_NUMBER", Type.GetType("System.String")); //检查编号
exportDt.Columns.Add("CHECK_DATE", Type.GetType("System.String")); //检查日期
exportDt.Columns.Add("CUSTOMER_NAME", Type.GetType("System.String")); //代理店名称
exportDt.Columns.Add("ENDER_CUSTOMER_NAME", Type.GetType("System.String")); //需要家
exportDt.Columns.Add("ADDRESS", Type.GetType("System.String")); //纳入先地址
exportDt.Columns.Add("ADDRESS2", Type.GetType("System.String")); //纳入先地址2
exportDt.Columns.Add("FANUC_SERIAL_NUMBER", Type.GetType("System.String"));
exportDt.Columns.Add("FANUC_SLIP_NUMBER", Type.GetType("System.String"));
#endregion
//销售订单编号的取得
#region
DataTable slipTable = bInvoice.GetSlipNumber(GetOEMProductConduction()).Tables[0];
string slip_number = "";
foreach (DataRow row in slipTable.Rows)
{
slip_number += "'" + row["ORDER_SLIP_NUMBER"].ToString() + "',";
}
if (string.IsNullOrEmpty(slip_number))
{
return;
}
slip_number = slip_number.Substring(0, slip_number.Length - 1);
#endregion
//根据取出的slipnumber取得第一部分的数据 机械本体的采购信息
#region
DataTable oneDt = bInvoice.GetStatementOneInfo(slip_number).Tables[0];
DataRow dr = null;
string machineCode = CConvert.ToString(oneDt.Rows[0]["MACHINE_CODE"]);
foreach (DataRow row in oneDt.Rows)
{
if (machineCode == row["MACHINE_CODE"].ToString())
{
dr = exportDt.NewRow();
dr["ORDER_SLIP_NUMBER"] = row["ORDER_SLIP_NUMBER"];
dr["MACHINE_CODE"] = row["MACHINE_CODE"];
dr["MACHINE_NAME"] = row["MACHINE_NAME"];
dr["BODY_PURCHASE_ORDER_SLIP_NUMBER"] = row["PURCHASE_ORDER_NUMBER"];
dr["BODY_INVOICE_NUMBER_LOCAL"] = row["INVOICE_NUMBER"];
dr["BODY_PURCHASE_SLIP_DATE"] = row["SLIP_DATE"];
dr["FANUC_SERIAL_NUMBER"] = row["FANUC_SERIAL_NUMBER"];
dr["FANUC_SLIP_NUMBER"] = row["FANUC_SLIP_NUMBER"];
dr["BODY_INVOICE_AMOUNT"] = addDecimal(dr["BODY_INVOICE_AMOUNT"], row["INVOICE_AMOUNT"]);
}
else
{
exportDt.Rows.Add(dr);
dr = exportDt.NewRow();
dr["ORDER_SLIP_NUMBER"] = row["ORDER_SLIP_NUMBER"];
dr["MACHINE_CODE"] = row["MACHINE_CODE"];
dr["MACHINE_NAME"] = row["MACHINE_NAME"];
dr["BODY_PURCHASE_ORDER_SLIP_NUMBER"] = row["PURCHASE_ORDER_NUMBER"];
dr["BODY_INVOICE_NUMBER_LOCAL"] = row["INVOICE_NUMBER"];
dr["BODY_PURCHASE_SLIP_DATE"] = row["SLIP_DATE"];
dr["FANUC_SERIAL_NUMBER"] = row["FANUC_SERIAL_NUMBER"];
dr["FANUC_SLIP_NUMBER"] = row["FANUC_SLIP_NUMBER"];
dr["BODY_INVOICE_AMOUNT"] = row["INVOICE_AMOUNT"];
machineCode = row["MACHINE_CODE"].ToString();
}
}
if (dr != null)
{
exportDt.Rows.Add(dr);
}
#endregion
//根据取出的ORDER_SLIP_NUMBER取得第二部分的数据 机械部件的采购信息
#region
dr = null;
BExchange bExchange = new BExchange();
DataTable tmpDt = exportDt.Clone();
DataTable twoDt = bInvoice.GetStatementTwoInfo(slip_number).Tables[0];
string orderSlipNumber = CConvert.ToString(oneDt.Rows[0]["ORDER_SLIP_NUMBER"]);
foreach (DataRow row in twoDt.Rows)
{
if (orderSlipNumber == row["ORDER_SLIP_NUMBER"].ToString())
{
dr = tmpDt.NewRow();
dr["ORDER_SLIP_NUMBER"] = row["ORDER_SLIP_NUMBER"];
if (CConvert.ToInt32(row["TYPE"]) == CConstant.ERP_FOREIGN_NUMBER)//国外
{
dr["FOREIGN_PURCHASE_ORDER_SLIP_NUMBER"] = addString(dr["FOREIGN_PURCHASE_ORDER_SLIP_NUMBER"], row["PURCHASE_ORDER_SLIP_NUMBER"]);
dr["FOREIGN_PURCHASE_INVOICE_NUMBER"] = addString(dr["FOREIGN_PURCHASE_INVOICE_NUMBER"], row["INVOICE_NUMBER"]);
dr["FOREIGN_PURCHASE_SLIP_DATE"] = addString(dr["FOREIGN_PURCHASE_SLIP_DATE"], row["SLIP_DATE"]);
dr["FOREIGN_INVOICE_AMOUNT"] = addDecimal(dr["FOREIGN_INVOICE_AMOUNT"], row["INVOICE_AMOUNT"]);
DateTime datatime = CConvert.ToDateTime(CConvert.ToDateTime(CConvert.ToDateTime(dr["FOREIGN_PURCHASE_SLIP_DATE"]).ToString("yyyy/MM") + "/1"));
dr["EXCHANGE_RATE"] = bExchange.GetExchange(datatime, CConstant.EXCHANGE_JP, CConstant.EXCHANGE_RMB);
dr["RMB_INVOICE_AMOUNT"] = addDecimal(dr["RMB_INVOICE_AMOUNT"], CConvert.ToDecimal(row["INVOICE_AMOUNT"]) * CConvert.ToDecimal(dr["EXCHANGE_RATE"]));
}
else if (CConvert.ToInt32(row["TYPE"]) == CConstant.ERP_DOMESTIC_NUMBER)//国内
{
dr["PART_PURCHASE_ORDER_SLIP_NUMBER"] = addString(dr["PART_PURCHASE_ORDER_SLIP_NUMBER"], row["PURCHASE_ORDER_SLIP_NUMBER"]);
dr["PART_INVOICE_NUMBER_LOCAL"] = addString(dr["PART_INVOICE_NUMBER_LOCAL"], row["INVOICE_NUMBER"]);
dr["PART_PURCHASE_SLIP_DATE"] = addString(dr["PART_PURCHASE_SLIP_DATE"], row["SLIP_DATE"]);
dr["PART_INVOICE_AMOUNT"] = addDecimal(dr["PART_INVOICE_AMOUNT"], row["INVOICE_AMOUNT"]);
}
dr["TAX_AMOUNT"] = addDecimal(dr["TAX_AMOUNT"], row["TAX_AMOUNT"]);
}
else
{
tmpDt.Rows.Add(dr);
orderSlipNumber = row["ORDER_SLIP_NUMBER"].ToString();
dr = tmpDt.NewRow();
dr["ORDER_SLIP_NUMBER"] = row["ORDER_SLIP_NUMBER"];
if (CConvert.ToInt32(row["SUPPLIER_CODE"]) == CConstant.ERP_FOREIGN_NUMBER)//国外
{
dr["FOREIGN_PURCHASE_ORDER_SLIP_NUMBER"] = addString(dr["FOREIGN_PURCHASE_ORDER_SLIP_NUMBER"], row["PURCHASE_ORDER_SLIP_NUMBER"]);
dr["FOREIGN_PURCHASE_INVOICE_NUMBER"] = addString(dr["FOREIGN_PURCHASE_INVOICE_NUMBER"], row["INVOICE_NUMBER"]);
dr["FOREIGN_PURCHASE_SLIP_DATE"] = addString(dr["FOREIGN_PURCHASE_SLIP_DATE"], row["SLIP_DATE"]);
dr["FOREIGN_INVOICE_AMOUNT"] = addDecimal(dr["FOREIGN_INVOICE_AMOUNT"], row["INVOICE_AMOUNT"]);
dr["EXCHANGE_RATE"] = 0.07;
dr["RMB_INVOICE_AMOUNT"] = addDecimal(dr["RMB_INVOICE_AMOUNT"], CConvert.ToDecimal(row["INVOICE_AMOUNT"]) * CConvert.ToDecimal(dr["EXCHANGE_RATE"]));
}
else if (CConvert.ToInt32(row["SUPPLIER_CODE"]) == CConstant.ERP_DOMESTIC_NUMBER)//国内
{
dr["PART_PURCHASE_ORDER_SLIP_NUMBER"] = addString(dr["PART_PURCHASE_ORDER_SLIP_NUMBER"], row["PURCHASE_ORDER_SLIP_NUMBER"]);
dr["PART_INVOICE_NUMBER_LOCAL"] = addString(dr["PART_INVOICE_NUMBER_LOCAL"], row["INVOICE_NUMBER"]);
dr["PART_PURCHASE_SLIP_DATE"] = addString(dr["PART_PURCHASE_SLIP_DATE"], row["SLIP_DATE"]);
dr["PART_INVOICE_AMOUNT"] = addDecimal(dr["PART_INVOICE_AMOUNT"], row["INVOICE_AMOUNT"]);
}
dr["TAX_AMOUNT"] = addDecimal(dr["TAX_AMOUNT"], row["TAX_AMOUNT"]);
}
}
if (dr != null)
{
tmpDt.Rows.Add(dr);
}
#endregion
//采购信息段的合并
#region
foreach (DataRow row in exportDt.Rows)
{
foreach (DataRow tRow in tmpDt.Rows)
{
if (row["ORDER_SLIP_NUMBER"].Equals(tRow["ORDER_SLIP_NUMBER"]))
{
row["FOREIGN_PURCHASE_ORDER_SLIP_NUMBER"] = tRow["FOREIGN_PURCHASE_ORDER_SLIP_NUMBER"];
row["FOREIGN_PURCHASE_INVOICE_NUMBER"] = tRow["FOREIGN_PURCHASE_INVOICE_NUMBER"];
row["FOREIGN_PURCHASE_SLIP_DATE"] = tRow["FOREIGN_PURCHASE_SLIP_DATE"];
row["FOREIGN_INVOICE_AMOUNT"] = tRow["FOREIGN_INVOICE_AMOUNT"];
row["EXCHANGE_RATE"] = tRow["EXCHANGE_RATE"];
row["RMB_INVOICE_AMOUNT"] = tRow["RMB_INVOICE_AMOUNT"];
row["PART_PURCHASE_ORDER_SLIP_NUMBER"] = tRow["PART_PURCHASE_ORDER_SLIP_NUMBER"];
row["PART_INVOICE_NUMBER_LOCAL"] = tRow["PART_INVOICE_NUMBER_LOCAL"];
row["PART_PURCHASE_SLIP_DATE"] = tRow["PART_PURCHASE_SLIP_DATE"];
row["PART_INVOICE_AMOUNT"] = tRow["PART_INVOICE_AMOUNT"];
row["TAX_AMOUNT"] = tRow["TAX_AMOUNT"];
}
}
}
#endregion
//销售信息的取得
#region
DataTable OrderTable = bInvoice.GetOrderHeaderInfo(slip_number).Tables[0];
foreach (DataRow erow in exportDt.Rows)
{
foreach (DataRow Orow in OrderTable.Rows)
{
if (erow["ORDER_SLIP_NUMBER"].Equals(Orow["SLIP_NUMBER"]))
{
erow["CUSTOMER_PO_NUMBER"] = Orow["CUSTOMER_PO_NUMBER"];
erow["SALES_INVOICE_NUMBER"] = Orow["SERIAL_NUMBER"];
erow["ORDER_SLIP_DATE"] = Orow["SLIP_DATE"];
erow["AMOUNT_WITHOUT_TAX"] = Orow["AMOUNT_WITHOUT_TAX"];
erow["CHECK_NUMBER"] = Orow["CHECK_NUMBER"];
erow["CHECK_DATE"] = Orow["CHECK_DATE"];
erow["CUSTOMER_NAME"] = Orow["CUSTOMER_NAME"];
erow["ENDER_CUSTOMER_NAME"] = Orow["ENDER_CUSTOMER_NAME"];
erow["ADDRESS"] = Orow["DELIVERY_POINT_NAME"];
erow["ADDRESS2"] = Orow["DELIVERY_POINT_NAME"];
}
}
}
#endregion
//销售发票信息的取得
#region
DataTable InvoiceTable = bInvoice.GetInvoiceNumber(slip_number).Tables[0];
foreach (DataRow irow in exportDt.Rows)
{
foreach (DataRow Irow in InvoiceTable.Rows)
{
if (irow["ORDER_SLIP_NUMBER"].Equals(Irow["ORDER_SLIP_NUMBER"]))
{
irow["SALES_INVOICE_NUMBER"] = Irow["SALES_INVOICE_NUMBER"];
irow["SHIPMENT_SLIP_DATE"] = Irow["SLIP_DATE"];
}
}
}
#endregion
//机器本体的销售信息的取得
#region
DataTable TotalBodyAmount = bInvoice.GetAmountWithoutTaxa(slip_number).Tables[0];
foreach (DataRow totalrow in exportDt.Rows)
{
foreach (DataRow Totalrow in TotalBodyAmount.Rows)
{
if (totalrow["ORDER_SLIP_NUMBER"].Equals(Totalrow["SLIP_NUMBER"]))
{
totalrow["TOTAL_BODY_AMOUNT"] = Totalrow["AMOUNT_WITHOUT_TAX"];
}
}
}
#endregion
//各种合计金额的计算
#region
foreach (DataRow row in exportDt.Rows)
{
row["TOTAL"] = addDecimal(row["RMB_INVOICE_AMOUNT"], row["TAX_AMOUNT"]);
row["TOTAL_AMOUNT"] = addDecimal(row["BODY_INVOICE_AMOUNT"], row["PART_INVOICE_AMOUNT"], row["TOTAL"]);
}
#endregion
//数据的导出
#region
Hashtable ht = new Hashtable();
SaveFileDialog sf = new SaveFileDialog();
sf.FileName = "LZ_PRODUCT_MANAGEMENT_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
sf.Filter = "(文件)|*.xls;*.xlsx";
if (sf.ShowDialog(this) == DialogResult.OK)
{
if (exportDt.Rows.Count > 0)
{
int ret = CommonExport.ExportOEMProduct(@"rpt\OEM_PRODUCT_MANAGEMENT.xls", sf.FileName, exportDt, ht);
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
}
