#region 导入订单
protected override string DoExcelData(System.Data.DataTable dt)
{
string data = "";
try
{
if (dt.Rows.Count == 0)
{
return "excel中无数据";
}
#region 接单日期时分取导入报表时间
string mstime = DateTime.Now.ToString("HH:mm");
for (int i = 0; i < dt.Rows.Count; i++)
{
string time = Convert.ToDateTime(dt.Rows[i]["接单日期"].ToString()).ToString("yyyy-MM-dd");
dt.Rows[i]["接单日期"] = time + " " + mstime;
}
#endregion
NameValueCollection cols = new NameValueCollection();
cols.Add("客户名称", "CustomerAbbr");
cols.Add("交货单号/出货单号", "SourceNo");
cols.Add("任务单号", "TaskSourceNo");
cols.Add("线路分类", "LineType");
cols.Add("接单日期", "OrderDt");
cols.Add("发货工厂", "FactoryAbbr");
cols.Add("收货单位", "DealerAbbr");
cols.Add("预开票单位", "TaxpayerName");
cols.Add("出货仓库", "ShipmentHub");
cols.Add("运输方式", "TransType");
cols.Add("货物名称", "CargoName");
cols.Add("品项", "ItemName");
cols.Add("货量", "GW");
cols.Add("数量", "Qty");
//cols.Add("货物分类", "CargoType");
cols.Add("是否中转", "IsTrans");
cols.Add("备注", "Remark");
ChangeDtTitle(dt, cols);
dt.TableName = "data";
using (System.IO.StringWriter writer = new System.IO.StringWriter())
{
dt.WriteXml(writer, false);
data = writer.ToString();
}
data = BaseService<dynamic>.GetScale(new { ActionName = "importDetail", data = data, DeptCode = ImportDeptCode, CreateBy = CurrentUserInfo.UserCode }, "Web_T_Order_sp");
}
catch (Exception e)
{
Exception ex = e;
while (ex.InnerException != null)
ex = ex.InnerException;
data = ex.Message;
}
return data;
}
//
@data xml='',
SELECT identity(int,1,1) as RowNo,
t.c.value('CustomerAbbr[1]','varchar(max)') CustomerAbbr,
CAST(NULL as varchar(50) ) as CustomerCode,
ISNULL( t.c.value('SourceNo[1]','varchar(max)'),0) SourceNo,
t.c.value('TaskSourceNo[1]','varchar(max)') TaskSourceNo,
t.c.value('LineType[1]','varchar(max)') LineType,
t.c.value('OrderDt[1]','datetime') OrderDt,
t.c.value('FactoryAbbr[1]','varchar(max)')FactoryAbbr,
t.c.value('IsTrans[1]','varchar(max)')IsTrans,
CAST(NULL AS varchar(50))TransCode,
CAST(NULL as varchar(50) )FactoryCode,
t.c.value('DealerAbbr[1]','varchar(max)')DealerAbbr,
CAST(NULL as varchar(50) )DealerCode,
t.c.value('TaxpayerName[1]','varchar(max)')TaxpayerName,
CAST(NULL as varchar(50) )TaxpayerID,
t.c.value('CargoName[1]','varchar(max)')CargoName,
CAST(NULL as varchar(50) )CargoCode,
t.c.value('ItemName[1]','varchar(max)')ItemName,
CAST(NULL as varchar(50) )ItemCode,
t.c.value('GW[1]','numeric(18,2)')GW,
t.c.value('Qty[1]','numeric(18,2)')Qty,
t.c.value('TransType[1]','varchar(max)')TransType,
t.c.value('ShipmentHub[1]','varchar(max)')ShipmentHub,
t.c.value('Remark[1]','varchar(max)')Remark,
CAST(NULL as varchar(50))DeptCode ,
CAST(NULL as varchar(30))QuotationNo,
CAST(NULL as int) QuotationRowID,
CAST(NULL as varchar(30)) SuccFulCode,
CAST(NULL as varchar(30))LineCode,
CAST(NULL as int) as OrderType,
CAST(NULL as varchar(50))OrderNo,
CAST(NULL AS varchar(50))CustomerTaskNo,
CAST(NULL AS varchar(50))BussessType
INTO #tmpdetailimport
FROM @data.nodes('//DocumentElement/data')t(c)