导入数据创建临时表

[password]123456[/password]

[code]using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Core.DynamicForm;
using Kingdee.BOS.Core.DynamicForm.PlugIn;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.ServiceHelper.Excel;
using Kingdee.BOS.Util;

namespace YEA.K3.BD.BarCode.Business.Plugin.Rept
{
[Description("动态表单插件:查询条码出货信息")]
public class DpBarShippingInfoPlugin : AbstractDynamicFormPlugIn
{
private string entryKey = "FEntity";

public override void AfterButtonClick(AfterButtonClickEventArgs e)
{
base.AfterButtonClick(e);
if (e.Key.EqualsIgnoreCase("Fbtn1"))
{
ExcelOut(); //模板导出//1--导入模板导出
}
if (e.Key.EqualsIgnoreCase("Fbtn2"))
{
ImportData(); //2--导入并处理数据
}
if (e.Key.EqualsIgnoreCase("Fbtn3"))
{
//3--导出处理后数据
//导出Excel
List returnDatas = new List();
returnDatas.Add(entryKey);
YEA.K3.MIS.APP.Core.CommonUtil.ExcelOut(returnDatas, this.View);
}

}

#region 模板导出

private void ExcelOut()
{
string filename = ""; //模板名称 //POSBillTemplate
string fTemplateNo = this.View.BillBusinessInfo.GetForm().Id;
string sql = @"/*dialect*/SELECT * FROM YEA_t_Template WHERE FDOCUMENTSTATUS = 'C'
AND FFORBIDSTATUS = 'A' AND FTemplateNo = '" + fTemplateNo + "'";

DynamicObjectCollection dy = DBUtils.ExecuteDynamicObject(this.Context, sql);
if (dy.Count > 0)
{
filename = dy[0]["FTemplateName"].ToString().Trim();
}
YEA.K3.MIS.APP.Core.CommonUtil.ExportTemplate(filename, this.View);
return;
}

#endregion

#region 导入并处理数据

private void ImportData()
{
DynamicFormShowParameter showParam = new DynamicFormShowParameter();
showParam.FormId = "YEA_D_POSEXCELIN";
showParam.Caption = "数据导入";
this.View.ShowForm(showParam,
new Action((formResult) =>
{
if (formResult != null && formResult.ReturnData != null)
{
string fullFileName = formResult.ReturnData.ToString();
this.DoImportExcel(fullFileName);
}
}));
}

#region 导入

private void DoImportExcel(string fullFileName)
{
// 利用ExcelOperation对象，把xml文件，转为DataSet对象
// 参数说明：
// filePath : 完整的文件名，包含了物理目录
// dataStartIndex : 数据开始行索引，从0开始。通常第一行为标题，第二行开始为数据行
// colNameIndex : 列名所在行索引，从0开始。如此参数为0，表明第一行为列名行
using (ExcelOperation helper = new ExcelOperation(this.View))
{
DataSet ds = helper.ReadFromFileCustom(fullFileName, 1, 0); // --7.0无该方法
//DataSet ds = helper.ReadFromFile(fullFileName, 0, 0);
DelFile(fullFileName); //删除文件

// 取第一个表格中的数据导入
DataTable dt = ds.Tables[0];
this.Model.ClearNoDataRow();
//数据从第二行开始，做判断用

#region 导入列校验

if (!dt.Columns.Contains("mac条码"))
{
base.View.ShowErrMessage("请确认是否包含【mac条码】列");
return;
}

if (!dt.Columns.Contains("sn条码"))
{
base.View.ShowErrMessage("请确认是否包含【sn条码】列");
return;
}
if (!dt.Columns.Contains("客户"))
{
base.View.ShowErrMessage("请确认是否包含【客户】列");
return;
}
if (!dt.Columns.Contains("出货日期"))
{
base.View.ShowErrMessage("请确认是否包含【出货日期】列");
return;
}
if (!dt.Columns.Contains("机型"))
{
base.View.ShowErrMessage("请确认是否包含【机型】列");
return;
}

#endregion

using (new SessionScope())
{
string tmpTableName = DBUtils.CreateSessionTemplateTable(base.Context, "TM_lzh_barShip",
this.GetCreateTmpTableNameSql(dt.Columns)); //临时表名称
dt.TableName = tmpTableName;
DBUtils.BulkInserts(this.Context, dt); //批量插入Excel数据至临时表
this.View.Model.SetValue("FTempTableName", tmpTableName); //临时表名称
this.UpdateTableInfo(tmpTableName); //更新其他字段信息
this.BindEntryData(tmpTableName); //绑定至单据体
DBUtils.DropSessionTemplateTable(base.Context, tmpTableName); // 会话级临时表使用完毕，需及时删除
}
}
}

///

/// 绑定至单据体
///

///
private void BindEntryData(string tmpTableName)
{
DynamicObjectCollection collection = GetTemTableData(tmpTableName); //查询临时表信息
this.View.Model.DeleteEntryData(entryKey);
for (int i = 0; i < collection.Count(); i++)
{
this.View.Model.CreateNewEntryRow(entryKey);
int index = this.View.Model.GetEntryCurrentRowIndex(entryKey);

this.View.Model.SetValue("FMacBar", Convert.ToString(collection[i]["mac条码"]).Trim(), index);
this.View.Model.SetValue("FSNBAR", Convert.ToString(collection[i]["sn条码"]).Trim(), index);
this.View.Model.SetValue("FCust", Convert.ToString(collection[i]["客户"]).Trim(), index);
this.View.Model.SetValue("FShipdate", Convert.ToString(collection[i]["出货日期"]).Trim(), index);
this.View.Model.SetValue("FMaterialName", Convert.ToString(collection[i]["机型"]).Trim(), index);
}
this.View.UpdateView(entryKey);
}

#endregion

#region 临时表相关

///

/// 构建字段str
///

///
///
private string GetCreateTmpTableNameSql(DataColumnCollection fieldColumn)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine("(");

List listStr = new List();
foreach (var field in fieldColumn)
{
listStr.Add(Convert.ToString(field).Trim() + " nvarchar(1000)");
}
stringBuilder.AppendLine(String.Join(",", listStr.Distinct()));
stringBuilder.AppendLine(")");
return stringBuilder.ToString();
}

///

/// 更新临时表信息
///

///
private void UpdateTableInfo(string tmpTableName)
{
string kingmaxDb = Convert.ToString(this.View.Model.GetValue("F_YEA_kingmaxy")).Trim(); //旧条码数据库
string cloudDb = Convert.ToString(this.View.Model.GetValue("F_YEA_k3cloud")).Trim(); //新条码数据库

List listSql = new List();
//旧系统--发货
string sql =
String.Format(@"/*dialect*/UPDATE a SET a.[客户] = b.FCustName ,a.[机型] = b.FName,a.[出货日期] = CONVERT(varchar(100), b.FDate, 23),a.[sn条码] = b.FSNBar
FROM {0} a,
(
SELECT v1.FCustName,V1.FDate,t1.FName,bar.FMacBar,bar.FSNBar
FROM [{1}].dbo.CP_SEOutStock v1 WITH(NOLOCK)
INNER JOIN [{1}].dbo.CP_SEOutStockEntry t1 WITH(NOLOCK) ON t1.FInterID = v1.FInterID
INNER JOIN [{1}].dbo.CP_SEOutStockBar bar WITH(NOLOCK) ON t1.FDetailID = bar.FDetailID
) b WHERE a.[mac条码] =b.FMacBar ", tmpTableName, kingmaxDb);
listSql.Add(sql);

//新系统--发货
string sql1 =
String.Format(@"/*dialect*/UPDATE a SET a.[客户] = b.FCustName ,a.[机型] = b.FName,a.[出货日期] = CONVERT(varchar(100), b.FDate, 23),a.[sn条码] = b.FSNBAR
FROM {0} a,
(
SELECT * FROM
(
--产线发货单
SELECT a.FDATE, cust.FNAME AS FCustName ,ml.FNAME AS FName,c.FMACBAR,c.FSNBAR
FROM [{1}].dbo.T_CP_SeOutStock a WITH(NOLOCK)
INNER JOIN [{1}].dbo.T_CP_SeOutStockDetail b WITH(NOLOCK) ON a.fid =b.FID
INNER JOIN [{1}].dbo.T_CP_SeOutStockEntry c WITH(NOLOCK)ON b.FEntryID = c.FEntryID
LEFT JOIN [{1}].dbo.T_BD_MATERIAL_L ml WITH(NOLOCK) ON ml.FMATERIALID = b.FMATERIALID
LEFT JOIN [{1}].dbo.T_BD_CUSTOMER_L cust WITH(NOLOCK) ON a.FCustomerID = cust.FCUSTID
UNION
--亿联发货单
SELECT a.FDATE, cust.FNAME AS FCustName ,ml.FNAME AS FName,c.FMACBAR,c.FSNBAR
FROM [{1}].dbo.T_YL_SendNotice a WITH(NOLOCK)
INNER JOIN [{1}].dbo.T_YL_SendNoticeDetail b WITH(NOLOCK) ON a.fid =b.FID
INNER JOIN [{1}].dbo.T_YL_SendNoticeEntry c WITH(NOLOCK)ON b.FEntryID = c.FEntryID
LEFT JOIN [{1}].dbo.T_BD_MATERIAL_L ml WITH(NOLOCK) ON ml.FMATERIALID = b.FMATERIALID
LEFT JOIN [{1}].dbo.T_BD_CUSTOMER_L cust WITH(NOLOCK) ON a.FCustomerID = cust.FCUSTID
) a WHERE 1 = 1
) b WHERE a.[mac条码] = b.FMacBar", tmpTableName, cloudDb);
listSql.Add(sql1);
DBUtils.ExecuteBatch(this.Context, listSql, listSql.Count);
}

///

/// 查询临时表信息
///

///
///
private DynamicObjectCollection GetTemTableData(string tmpTableName)
{
string sql = String.Format(@"/*dialect*/select * from {0} WITH(NOLOCK)", tmpTableName);
return DBUtils.ExecuteDynamicObject(this.Context, sql);
}

#endregion

#endregion

#region 删除单个文件

private void DelFile(string path)
{
FileInfo file = new FileInfo(path);
if (file.Exists)
{
file.Delete(); //删除单个文件
}
}

#endregion

}
}[/code]