面试用
2018-03-02 14:03 梦游0和1的世界 阅读(248) 评论(0) 收藏 举报var tabs = new SmartTabs("divTabContainer"); function LoadWindow() { window.onload = null; ResizeEditor(); LoadEditor(); if (typeof (LoadManualSet) == "function") { LoadManualSet(); } LoadDropDownList(); } window.onload = LoadWindow; function ResizeEditor() { document.getElementById("divTabContainer").style.height = (document.documentElement.offsetHeight - 75) + "px"; } var xmlDocumentEntity, xmlDocumentLayout, xslDocumentLayout, xmlDocumentData = null; function LoadEditor() { xmlDocumentEntity = new ActiveXObject("MSXML2.DOMDocument"); xmlDocumentEntity.async = false; xmlDocumentEntity.loadXML(document.getElementById("xmlEntityXml").innerHTML); xmlDocumentLayout = new ActiveXObject("MSXML2.DOMDocument"); xmlDocumentLayout.async = false; xmlDocumentLayout.loadXML(document.getElementById("xmlLayoutXml").innerHTML); if (xmlDocumentLayout.xml == "") { alert("未发现数据编辑页面布局！"); return; } xslDocumentLayout = new ActiveXObject("MSXML2.DOMDocument"); xslDocumentLayout.async = false; xslDocumentLayout.loadXML(document.getElementById("xmlLayoutXsl").innerHTML); document.getElementById("divPageContainer").innerHTML = xmlDocumentLayout.transformNode(xslDocumentLayout); var tabNames = new Array(); var nodes = xmlDocumentLayout.selectNodes("/Layout/TabPages/TabPage"); for (var i = 0; i < nodes.length; i++) { tabNames[i] = nodes[i].getAttribute("Label"); } tabs.Load(tabNames); /* var firstCellXmlNode = xmlDocumentLayout.selectSingleNode("/Layout/TabPages/TabPage/Sections/Section/Rows/Row/Cells/Cell"); if (firstCellXmlNode) { SetFocus("EntityField_" + firstCellXmlNode.getAttribute("Name")); } */ var entityDataElement = document.getElementById("xmlEntityData"); var tableName = xmlDocumentLayout.documentElement.getAttribute("Name"); var fieldXmlNode, fieldPropertyXmlNode, columnName, fieldElement; if (!entityDataElement)//新增 { var fieldXmlNodes = xmlDocumentEntity.selectNodes("/Entity/Fields/Field"); for (var i = 0; i < fieldXmlNodes.length; i++) { fieldXmlNode = fieldXmlNodes[i]; columnName = fieldXmlNode.getAttribute("ColumnName"); fieldElement = document.getElementById("EntityField_" + columnName); if (fieldElement) { fieldPropertyXmlNode = fieldXmlNode.selectSingleNode("ExtendedProperty"); switch (fieldPropertyXmlNode.getAttribute("LogicType")) { case "Date": case "YearAndMonth": case "DateTime": if (fieldPropertyXmlNode.getAttribute("FormatString") != "") { fieldElement.FormatString = fieldPropertyXmlNode.getAttribute("FormatString"); } break; case "File": if (!AllowFlashUpload()) { var fieldDivElement = document.getElementById("FieldDiv_" + columnName); var fieldFlashElement = document.getElementById("EntityField_" + columnName + "_FileUpload"); AppendControlHtml(fieldFlashElement, "beforeBegin", "<iframe id=\"EntityField_" + columnName + "_Iframe\" width=\"300\" height=\"22\" style=\"vertical-align:middle;\" frameborder=\"0\" scrolling=\"no\" src=\"../EntityManage/EntityFileUpload.aspx?tn=" + tableName + "&cn=" + columnName + "\"/>"); fieldDivElement.removeChild(fieldFlashElement); } break; case "Picture": if (!AllowFlashUpload()) { var fieldDivElement = document.getElementById("FieldDiv_" + columnName); var fieldFlashElement = document.getElementById("EntityField_" + columnName + "_PictureUpload"); AppendControlHtml(fieldFlashElement, "beforeBegin", "<iframe id=\"EntityField_" + columnName + "_Iframe\" width=\"300\" height=\"22\" style=\"vertical-align:middle;\" frameborder=\"0\" scrolling=\"no\" src=\"../EntityManage/EntityPictureUpload.aspx?tn=" + tableName + "&cn=" + columnName + "\"/>"); fieldDivElement.removeChild(fieldFlashElement); } break; default: break; } if (fieldPropertyXmlNode.getAttribute("DefaultValueFillOperate").indexOf("BeforeInsert") >= 0) { switch (fieldPropertyXmlNode.getAttribute("DefaultValue").toLowerCase()) { case "": break; case "getdate()": case "datetime.now": if (fieldElement.FormatString) { fieldElement.value = systemValue.CurrentTime.format(fieldElement.FormatString); } else { fieldElement.value = systemValue.CurrentTime; } break; case "currentuserid": fieldElement.value = systemValue.CurrentUserId; break; case "currentusername": fieldElement.value = systemValue.CurrentUserName; break; case "currentpostid": fieldElement.value = systemValue.CurrentPostId; break; case "currentpostname": fieldElement.value = systemValue.CurrentPostName; break; case "currentdepartmentid": fieldElement.value = systemValue.CurrentDepartmentId; break; case "currentdepartmentname": fieldElement.value = systemValue.CurrentDepartmentName; break; case "currenttopdepartmentid": fieldElement.value = systemValue.CurrentTopDepartmentId; break; case "currenttopdepartmentname": fieldElement.value = systemValue.CurrentTopDepartmentName; break; default: fieldElement.value = fieldPropertyXmlNode.getAttribute("DefaultValue"); break; } switch (fieldPropertyXmlNode.getAttribute("LogicType")) { case "Boolean": document.getElementById(fieldElement.id + "_CheckBoxField").checked = ParseBoolean(fieldPropertyXmlNode.getAttribute("DefaultValue")); fieldElement.value = document.getElementById(fieldElement.id + "_CheckBoxField").checked; break; case "Reference": if (document.getElementById(fieldElement.id + "_ValueField")) { document.getElementById(fieldElement.id + "_ValueField").value = fieldPropertyXmlNode.getAttribute("DefaultValue"); } if (fieldElement.RelationNameField == fieldElement.id + "_TextField") { document.getElementById(fieldElement.id + "_TextField").value = fieldPropertyXmlNode.getAttribute("DefaultValue"); } if (fieldElement.RelationNameField == fieldElement.id + "_ShowField") { document.getElementById(fieldElement.id + "_ShowField").value = fieldPropertyXmlNode.getAttribute("DefaultValue"); } break; default: break; } } } } } else//编辑 { xmlDocumentData = new ActiveXObject("MSXML2.DOMDocument"); xmlDocumentData.async = false; xmlDocumentData.loadXML(entityDataElement.innerHTML); FillData(xmlDocumentData); } } function FillData(xmlDocumentData) { var cellXmlNode; var tableName = xmlDocumentData.documentElement.getAttribute("Name"); var fieldDataXmlNodes = xmlDocumentData.documentElement.firstChild.childNodes; for (var i = 0; i < fieldDataXmlNodes.length; i++) { columnName = fieldDataXmlNodes[i].nodeName; fieldElement = document.getElementById("EntityField_" + columnName); if (fieldElement) { cellXmlNode = xmlDocumentLayout.selectSingleNode("/Layout/TabPages/TabPage/Sections/Section/Rows/Row/Cells/Cell[@Name='" + columnName + "']"); if (cellXmlNode) { fieldXmlNode = xmlDocumentEntity.selectSingleNode("/Entity/Fields/Field[@ColumnName='" + columnName + "']"); fieldPropertyXmlNode = fieldXmlNode.selectSingleNode("ExtendedProperty"); switch (cellXmlNode.getAttribute("LogicType")) { case "Boolean": document.getElementById(fieldElement.id + "_CheckBoxField").checked = eval(fieldDataXmlNodes[i].text.toLowerCase()); fieldElement.value = document.getElementById(fieldElement.id + "_CheckBoxField").checked; break; case "Date": case "YearAndMonth": case "DateTime": if (fieldPropertyXmlNode.getAttribute("FormatString") != "") { fieldElement.FormatString = fieldPropertyXmlNode.getAttribute("FormatString"); } fieldElement.value = fieldDataXmlNodes[i].text; break; case "File": if (fieldDataXmlNodes[i].text != "") { fieldElement.value = fieldDataXmlNodes[i].text; var linkElement = document.getElementById("EntityField_" + columnName + "_FileLink"); linkElement.style.display = ""; linkElement.href = "../EntityFile/" + tableName + "/" + fieldDataXmlNodes[i].text; } if (!AllowFlashUpload()) { var fieldDivElement = document.getElementById("FieldDiv_" + columnName); var fieldFlashElement = document.getElementById("EntityField_" + columnName + "_FileUpload"); AppendControlHtml(fieldFlashElement, "beforeBegin", "<iframe id=\"EntityField_" + columnName + "_Iframe\" width=\"300\" height=\"22\" style=\"vertical-align:middle;\" frameborder=\"0\" scrolling=\"no\" src=\"../EntityManage/EntityFileUpload.aspx?tn=" + tableName + "&cn=" + columnName + "\"/>"); fieldDivElement.removeChild(fieldFlashElement); } break; case "Picture": if (fieldDataXmlNodes[i].text != "") { fieldElement.value = fieldDataXmlNodes[i].text; var linkElement = document.getElementById("EntityField_" + columnName + "_PictureLink"); linkElement.style.display = ""; linkElement.href = "../EntityPicture/" + tableName + "/" + fieldDataXmlNodes[i].text; var maxWidth = linkElement.parentNode.clientWidth; linkElement.innerHTML = "<img src=\"../EntityPicture/" + tableName + "/" + fieldDataXmlNodes[i].text + "\" border=\"0\" onload=\"if(this.width>" + maxWidth + ")this.width=" + maxWidth + ";\"/>"; } if (!AllowFlashUpload()) { var fieldDivElement = document.getElementById("FieldDiv_" + columnName); var fieldFlashElement = document.getElementById("EntityField_" + columnName + "_PictureUpload"); AppendControlHtml(fieldFlashElement, "beforeBegin", "<iframe id=\"EntityField_" + columnName + "_Iframe\" width=\"300\" height=\"22\" style=\"vertical-align:middle;\" frameborder=\"0\" scrolling=\"no\" src=\"../EntityManage/EntityPictureUpload.aspx?tn=" + tableName + "&cn=" + columnName + "\"/>"); fieldDivElement.removeChild(fieldFlashElement); } break; case "Reference": if (eval(cellXmlNode.getAttribute("IsRichList"))) { if (fieldElement.RelationNameField == fieldElement.id + "_ShowField") { document.getElementById(fieldElement.id + "_ShowField").value = fieldDataXmlNodes[i].text; } } else { document.getElementById(fieldElement.id + "_ValueField").value = fieldDataXmlNodes[i].text; if (fieldElement.RelationNameField == fieldElement.id + "_TextField") { document.getElementById(fieldElement.id + "_TextField").value = fieldDataXmlNodes[i].text; } } fieldElement.value = fieldDataXmlNodes[i].text; break; default: fieldElement.value = fieldDataXmlNodes[i].text; break; } } else { SetControlValue(fieldElement, fieldDataXmlNodes[i].text); } } } } function RefreshData() { var entityDataElement = document.getElementById("xmlEntityData"); if (entityDataElement) { xmlDocumentData = new ActiveXObject("MSXML2.DOMDocument"); xmlDocumentData.async = false; xmlDocumentData.loadXML(entityDataElement.innerHTML); FillData(xmlDocumentData); } if (typeof (LoadManualData) == "function") { LoadManualData(); } } function LoadDropDownList() { var tableName, columnName, fieldElement; tableName = xmlDocumentLayout.documentElement.getAttribute("Name"); var cellXmlNodes = xmlDocumentLayout.selectNodes("/Layout/TabPages/TabPage/Sections/Section/Rows/Row/Cells/Cell[@LogicType='Reference' and @IsRichList='false']"); for (var i = 0; i < cellXmlNodes.length; i++) { columnName = cellXmlNodes[i].getAttribute("Name"); LoadSelectList(tableName, columnName); } } function OptionSelected(select, manualFunction) { var itemData = { KeyValue: select.value, KeyName: GetSelectListSelectedText(select) }; document.getElementById(select.id + "_ValueField").value = itemData.KeyValue; document.getElementById(select.RelationNameField).value = itemData.KeyName; if (manualFunction) { manualFunction(itemData); } } function AllowFlashUpload() { return !(navigator.userAgent.indexOf("MSIE") >= 0 && navigator.userAgent.indexOf("Mozilla/5.0") >= 0); } function OnUploadFileComplete(tableName, columnName, fileName, originalName) { SetControlValue("EntityField_" + columnName, fileName); var linkElement = document.getElementById("EntityField_" + columnName + "_FileLink"); linkElement.style.display = ""; linkElement.href = "../EntityFile/" + tableName + "/" + fileName; if (typeof (UploadFileComplete) == "function") { UploadFileComplete(tableName, columnName, fileName, originalName); } } function OnUploadPictureComplete(tableName, columnName, fileName, originalName) { SetControlValue("EntityField_" + columnName, fileName); var linkElement = document.getElementById("EntityField_" + columnName + "_PictureLink"); linkElement.style.display = ""; linkElement.href = "../EntityPicture/" + tableName + "/" + fileName; var maxWidth = linkElement.parentNode.clientWidth; linkElement.innerHTML = "<img src=\"../EntityPicture/" + tableName + "/" + fileName + "\" border=\"0\" onload=\"if(this.width>" + maxWidth + ")this.width=" + maxWidth + ";\"/>"; if (typeof (UploadPictureComplete) == "function") { UploadPictureComplete(tableName, columnName, fileName, originalName); } } function OnUploadCancel() { } function OnUploadError(message) { alert(message); } function FieldAutoUpper(fieldElement) { fieldElement.value = fieldElement.value.toUpperCase(); } function FieldChecked(checkbox, hiddenElementId, manualFunction) { document.getElementById(hiddenElementId).value = checkbox.checked; if (manualFunction) { manualFunction(checkbox, hiddenElementId); } } function ClearPictureValue(pkFieldId) { document.getElementById(pkFieldId).value = ""; var linkElement = document.getElementById(pkFieldId + "_PictureLink"); linkElement.style.display = "none"; } function ClearFileValue(pkFieldId) { document.getElementById(pkFieldId).value = ""; var linkElement = document.getElementById(pkFieldId + "_FileLink"); linkElement.style.display = "none"; } function ClearReferenceValue(pkFieldId, nameFieldId) { document.getElementById(pkFieldId).value = ""; document.getElementById(nameFieldId).value = ""; if (typeof (ManualClearReferenceValue) == "function") { ManualClearReferenceValue(pkFieldId); } } function CheckFormData() { var fieldXmlNodes = xmlDocumentLayout.selectNodes("/Layout/TabPages/TabPage/Sections/Section/Rows/Row/Cells/Cell"); for (var i = 0; i < fieldXmlNodes.length; i++) { var fieldNode = fieldXmlNodes[i]; var fieldName = fieldNode.getAttribute("Name"); var fieldLabel = fieldNode.getAttribute("Label"); var fillRequire = fieldNode.getAttribute("FillRequire"); var logicType = fieldNode.getAttribute("LogicType"); var fieldElement = document.getElementById("EntityField_" + fieldName); if (fieldElement) { if (fillRequire == "System" || fillRequire == "Require") { if (fieldElement.value == "") { alert("请输入“" + fieldLabel + "”！"); SetFocus(fieldElement); return false; } } if (fieldElement.value != "") { switch (logicType) { case "Integer": if (!IsInt(fieldElement.value)) { alert("输入的“" + fieldLabel + "”必须为整数！"); SetFocus(fieldElement); return false; } break; case "Decimal": if (!IsNumeric(fieldElement.value)) { alert("输入的“" + fieldLabel + "”必须为数字！"); SetFocus(fieldElement); return false; } break; case "Date": if (!IsDate(fieldElement.value)) { alert("输入的“" + fieldLabel + "”必须为日期！"); SetFocus(fieldElement); return false; } break; case "DateTime": if (!IsDateTime(fieldElement.value)) { alert("输入的“" + fieldLabel + "”必须为日期时间！"); SetFocus(fieldElement); return false; } break; } } } } if (typeof (ManualCheckFormData) == "function") { return ManualCheckFormData(); } else { return true; } } function SetFocus(control) { if (typeof (control) == "string") { if (control != null && control != "") { control = document.getElementById(control); } } if (control) { if (control.type == "hidden") { if (control.RelationNameField) { control = document.getElementById(control.RelationNameField); if (control) { control.focus(); if (control.tagName == "INPUT" || control.tagName == "TEXTAREA") { control.select(); } } } } else { control.focus(); if (control.tagName == "INPUT" || control.tagName == "TEXTAREA") { control.select(); } } } } function GetFieldName(columnName, xmlDocument) { if (xmlDocument == undefined) { var cellXmlNode = xmlDocumentLayout.selectSingleNode("/Layout/TabPages/TabPage/Sections/Section/Rows/Row/Cells/Cell[@Name='" + columnName + "']"); if (cellXmlNode) { return cellXmlNode.getAttribute("Label"); } else { return xmlDocumentEntity.selectSingleNode("/Entity/Fields/Field[@ColumnName='" + columnName + "']").getAttribute("FieldName"); } } else { return xmlDocument.selectSingleNode("/Entity/Fields/Field[@ColumnName='" + columnName + "']").getAttribute("FieldName"); } } function GetFieldValue(columnName, xmlDocument) { if (xmlDocument == undefined) { return xmlDocumentData.selectSingleNode("/DataTable/Row/" + columnName).text; } else { return xmlDocument.selectSingleNode("/DataTable/Row/" + columnName).text; } } function AppendControlHtml(control, where, html) { if (typeof (control) == "string") { control = document.getElementById(control); } if (control) { switch (where) { case "beforeBegin": case "afterBegin": case "beforeEnd": case "afterEnd": control.insertAdjacentHTML(where, html); break; case "innerHTML": control.innerHTML = html; break; } } } function SetControlValue(control, value) { if (typeof (control) == "string") { control = document.getElementById(control); } if (control) { switch (control.tagName.toLowerCase()) { case "input": switch (control.type) { case "text": case "hidden": control.value = value; break; case "checkbox": if (typeof (value) == "boolean") { control.checked = value; } else { control.checked = eval(value.toLowerCase()); } break; } break; case "textarea": control.value = value; break; case "select": for (var i = 0; i < control.options.length; i++) { if (control.options[i].value == value) { control.options[i].selected = true; } else { control.options[i].selected = false; } } break; } } return control; } function IsMawbNumber(value) { var pattern = /^\d{3}-\d{8}$/; //999-99999999 if (pattern.test(value)) { var a = parseInt(value.substring(4, 11), 10); var b = parseInt(value.substring(11, 12), 10); if (a % 7 == b) { return true; } else { return false; } } else { return false; } } function ShowDataLog(url, tableName, id) { url += "?tn=" + tableName + "&id=" + id; ShowDialog(url, null, 800, 600); return false; }
function SetSumText() { var incomeCostObject = {}; if (incomeObject != null && costObject != null) { for (var key in incomeObject) { incomeCostObject[key] = { MoneyValue: incomeObject[key].MoneyValue, ExchangeRate: incomeObject[key].ExchangeRate, InvoicedValue: incomeObject[key].InvoicedValue }; } for (var key in costObject) { if (incomeCostObject[key]) { incomeCostObject[key].MoneyValue = incomeCostObject[key].MoneyValue - costObject[key].MoneyValue; incomeCostObject[key].InvoicedValue = incomeCostObject[key].InvoicedValue - costObject[key].PrejudicationValue; if (costObject[key].ExchangeRate != 0) { incomeCostObject[key].ExchangeRate = costObject[key].ExchangeRate; } } else { incomeCostObject[key] = { MoneyValue: -costObject[key].MoneyValue, ExchangeRate: costObject[key].ExchangeRate, InvoicedValue: -costObject[key].PrejudicationValue }; } } var moneyText = "", totalMoney = 0; var invoicedText = "", totalInvoicedMoney = 0; for (var key in incomeCostObject) { if (incomeCostObject[key].MoneyValue != 0) { if (moneyText.length > 0) { moneyText += ", "; } moneyText += key + ":" + incomeCostObject[key].MoneyValue.toFixed(2); totalMoney += incomeCostObject[key].MoneyValue * incomeCostObject[key].ExchangeRate } if (incomeCostObject[key].InvoicedValue != 0) { if (invoicedText.length > 0) { invoicedText += ", "; } invoicedText += key + ":" + incomeCostObject[key].InvoicedValue.toFixed(2); totalInvoicedMoney += incomeCostObject[key].InvoicedValue * incomeCostObject[key].ExchangeRate; } } AppendControlHtml("spanIncomeCost", "innerHTML", moneyText); AppendControlHtml("spanTotalIncomeCost", "innerHTML", totalMoney.toFixed(2)); AppendControlHtml("spanIncomeCostPrejudication", "innerHTML", invoicedText.replace("NaN", "0")); AppendControlHtml("spanTotalIncomeCostPrejudication", "innerHTML", totalInvoicedMoney.toFixed(2).replace("NaN", "0")); } }
function BeginRequest(sender, args) { args.get_postBackElement().disabled = true; var updatePanelIds = args.get_updatePanelsToUpdate(); for (var m = 0; m < updatePanelIds.length; m++) { var updatePanelId; for (var i = 0; i < sender._updatePanelIDs.length; i++) { if (sender._updatePanelIDs[i] = updatePanelIds[m]) { updatePanelId = sender._updatePanelClientIDs[i]; break; } } var updatePanel = document.getElementById(updatePanelId); var inputElements = updatePanel.getElementsByTagName("input"); for (var i = 0; i < inputElements.length; i++) { inputElements[i].disabled = true; } var selectElements = updatePanel.getElementsByTagName("select"); for (var i = 0; i < selectElements.length; i++) { selectElements[i].disabled = true; } var textareaElements = updatePanel.getElementsByTagName("textarea"); for (var i = 0; i < textareaElements.length; i++) { textareaElements[i].disabled = true; } } } function ConsignmentUnitIdSelected(itemData) { var suIsDisabled = document.getElementById("hdnSUIsDisabled"); //结算单位禁用 var cuIsDisabled = document.getElementById("hdnCUIsDisabled"); //委托单位禁用 if (itemData.IsDisabled.toLowerCase() == "true") { cuIsDisabled.value = "true"; if (itemData.Remarks) { Layer("divWarningWin", "divBgWin", true, itemData.Remarks); ShowLayer("divWarningWin", "divBgWin"); } else { if (itemData.WarningRemarks) { Layer("divWarningWin", "divBgWin", true, itemData.WarningRemarks); ShowLayer("divWarningWin", "divBgWin"); } else { Layer("divWarningWin", "divBgWin", true, "该客户已禁用!"); ShowLayer("divWarningWin", "divBgWin"); } } }
function ItemCodeClick(srcElement, hiddenElement, tableName, columnName) { return { FeeTypeKey: "Manual|2|3" }; }
function ItemCodeSelected(itemData) { var currencyTypeElement = smartDropDown.HiddenElement.id.replace("_ItemCode_", "_CurrencyType_"); SetControlValue(currencyTypeElement, itemData.CurrencyTypeKey); }
SQL 解析XML 各类操作
USE [ForwarderXa]
GO
/****** Object: StoredProcedure [dbo].[P_EntityDataOperate] Script Date: 08/18/2016 14:07:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 郑琦东
-- Create date: 2016-8-25 13:26:33
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[P_EntityDataOperate]
@OperatesXml xml,
@OutputesXml nvarchar(max) OUTPUT
AS
BEGIN
SET @OutputesXml = '<Outputs>'
SET XACT_ABORT ON
BEGIN TRANSACTION
DECLARE @OperateXml xml, @OperateType varchar(10), @TableName nvarchar(128), @RowCount int, @ConstCondition nvarchar(max)
DECLARE @Sql nvarchar(max), @Columns nvarchar(max), @Values nvarchar(max), @ColumnValues nvarchar(max), @Conditions nvarchar(max), @SortConditions nvarchar(max)
DECLARE @OperateCursor CURSOR
SET @OperateCursor = CURSOR FOR
SELECT Operate.query('.')
FROM @OperatesXml.nodes('/Operates/Operate') Operates(Operate)
ORDER BY Operates.Operate.value('@SequenceId', 'smallint')
OPEN @OperateCursor
FETCH NEXT FROM @OperateCursor INTO @OperateXml
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @OperateType = Operate.value('@OperateType','varchar(10)'), @TableName = Operate.value('@TableName','nvarchar(128)'), @RowCount = Operate.value('@RowCount','int'), @ConstCondition = Operate.value('@ConstCondition','nvarchar(max)')
FROM @OperateXml.nodes('/Operate') Operates(Operate)
SET @Columns = ''
SELECT @Columns = @Columns + '[' + Field.value('@ColumnName','nvarchar(128)') + '],'
FROM @OperateXml.nodes('/Operate/OperateFields/Field') Fields(Field)
IF @Columns <> ''
BEGIN
SET @Columns = SUBSTRING(@Columns, 1, LEN(@Columns) - 1)
END
SET @Conditions = ISNULL(@ConstCondition, '')
IF @OperateType = 'Select'
BEGIN
SELECT @Conditions = @Conditions + ' AND ' +
CASE
WHEN Field.value('@LogicType','nvarchar(64)') = 'String' OR Field.value('@LogicType','nvarchar(64)') = 'Text'
THEN
CASE
WHEN Field.value('@ColumnValue','nvarchar(512)') IS NULL
THEN '([' + Field.value('@ColumnName','nvarchar(128)') + '] IS NULL)'
ELSE
'([' + Field.value('@ColumnName','nvarchar(128)') + '] LIKE ''%' + dbo.F_AvoidInject(Field.value('@ColumnValue','nvarchar(512)')) + '%'')'
END
WHEN Field.value('@LogicType','nvarchar(64)') = 'Boolean'
THEN
CASE
WHEN Field.value('@ColumnValue','nvarchar(512)') IS NULL
THEN '([' + Field.value('@ColumnName','nvarchar(128)') + '] IS NULL)'
ELSE
'([' + Field.value('@ColumnName','nvarchar(128)') + '] = ' + dbo.F_AvoidInject(Field.value('@ColumnValue','nvarchar(512)')) + ')'
END
WHEN Field.value('@LogicType','nvarchar(64)') = 'Integer' OR Field.value('@LogicType','nvarchar(64)') = 'Decimal'
THEN
CASE
WHEN Field.value('@ColumnValue','nvarchar(512)') IS NULL AND Field.value('@ColumnValue1','nvarchar(512)') IS NULL
THEN '([' + Field.value('@ColumnName','nvarchar(128)') + '] IS NULL)'
ELSE
CASE
WHEN Field.value('@ColumnValue','nvarchar(512)') IS NULL
THEN '([' + Field.value('@ColumnName','nvarchar(128)') + '] <= ' + Field.value('@ColumnValue1','nvarchar(512)') + ')'
WHEN Field.value('@ColumnValue1','nvarchar(512)') IS NULL
THEN '([' + Field.value('@ColumnName','nvarchar(128)') + '] >= ' + Field.value('@ColumnValue','nvarchar(512)') + ')'
ELSE
'([' + Field.value('@ColumnName','nvarchar(128)') + '] >= ' + Field.value('@ColumnValue','nvarchar(512)') + ') AND ([' + Field.value('@ColumnName','nvarchar(128)') + '] <= ' + Field.value('@ColumnValue1','nvarchar(512)') + ')'
END
END
WHEN Field.value('@LogicType','nvarchar(64)') = 'Date' OR Field.value('@LogicType','nvarchar(64)') = 'DateTime'
THEN
CASE
WHEN Field.value('@ColumnValue','nvarchar(512)') IS NULL AND Field.value('@ColumnValue1','nvarchar(512)') IS NULL
THEN '([' + Field.value('@ColumnName','nvarchar(128)') + '] IS NULL)'
ELSE
CASE
WHEN Field.value('@ColumnValue','nvarchar(512)') IS NULL
THEN '([' + Field.value('@ColumnName','nvarchar(128)') + '] < ''' + CONVERT(nvarchar(10), DATEADD(dd, 1, Field.value('@ColumnValue1','datetime')), 120) + ''')'
WHEN Field.value('@ColumnValue1','nvarchar(512)') IS NULL
THEN '([' + Field.value('@ColumnName','nvarchar(128)') + '] >= ''' + CONVERT(nvarchar(10), Field.value('@ColumnValue','datetime'), 120) + ''')'
ELSE
'([' + Field.value('@ColumnName','nvarchar(128)') + '] >= ''' + CONVERT(nvarchar(10), Field.value('@ColumnValue','datetime'), 120) + ''') AND ([' + Field.value('@ColumnName','nvarchar(128)') + '] < ''' + CONVERT(nvarchar(10), DATEADD(dd, 1, Field.value('@ColumnValue1','datetime')), 120) + ''')'
END
END
ELSE
CASE
WHEN Field.value('@ColumnValue','nvarchar(512)') IS NULL
THEN '([' + Field.value('@ColumnName','nvarchar(128)') + '] IS NULL)'
ELSE
'([' + Field.value('@ColumnName','nvarchar(128)') + '] = ''' + dbo.F_AvoidInject(Field.value('@ColumnValue','nvarchar(512)')) + ''')'
END
END
FROM @OperateXml.nodes('/Operate/ConditionFields/Field') Fields(Field)
SET @SortConditions = ''
SELECT @SortConditions = @SortConditions + '[' + Field.value('@ColumnName','nvarchar(128)') + '] ' + Field.value('@ColumnValue','nvarchar(4)') + ','
FROM @OperateXml.nodes('/Operate/SortFields/Field') Fields(Field)
SET @SortConditions = SUBSTRING(@SortConditions, 1, LEN(@SortConditions) - 1)
SET @Sql = 'SELECT ' + @Columns + ' FROM [' + @TableName + '] WHERE (1 = 1)' + @Conditions + ' ORDER BY ' + @SortConditions
IF @RowCount > 0
BEGIN
SET ROWCOUNT @RowCount
END
EXECUTE(@Sql)
SET ROWCOUNT 0
END
IF @OperateType = 'Insert'
BEGIN
SET @Values = ''
SELECT @Values = @Values + CASE WHEN Field.value('@ColumnValue','nvarchar(4000)') IS NULL THEN 'NULL' ELSE '''' + dbo.F_AvoidInject(Field.value('@ColumnValue','nvarchar(4000)')) + '''' END + ','
FROM @OperateXml.nodes('/Operate/OperateFields/Field') Fields(Field)
SET @Values = SUBSTRING(@Values, 1, LEN(@Values) - 1)
SET @Sql = 'INSERT INTO [' + @TableName + '](' + @Columns + ') VALUES(' + @Values + ')' + CHAR(13) + CHAR(10) + 'SET @IdentityId = SCOPE_IDENTITY()'
DECLARE @IdentityId bigint
EXECUTE sp_executesql @Sql, N'@IdentityId bigint OUTPUT', @IdentityId OUTPUT
SET @OutputesXml = @OutputesXml + '<Output TableName=''' + @TableName + ''' ColumnName=''PrimaryKey'' ColumnValue=''' + CAST(@IdentityId AS nvarchar) + '''/>'
END
IF @OperateType = 'Update'
BEGIN
SELECT @Conditions = @Conditions + ' AND ' +
CASE
WHEN Field.value('@ColumnValue','nvarchar(512)') IS NULL
THEN '([' + Field.value('@ColumnName','nvarchar(128)') + '] IS NULL)'
ELSE
'([' + Field.value('@ColumnName','nvarchar(128)') + '] = ''' + dbo.F_AvoidInject(Field.value('@ColumnValue','nvarchar(512)')) + ''')'
END
FROM @OperateXml.nodes('/Operate/ConditionFields/Field') Fields(Field)
IF @Conditions <> ''
BEGIN
SET @ColumnValues = ''
SELECT @ColumnValues = @ColumnValues + '[' + Field.value('@ColumnName','nvarchar(128)') + '] = ' + CASE WHEN Field.value('@ColumnValue','nvarchar(4000)') IS NULL THEN 'NULL' ELSE '''' + dbo.F_AvoidInject(Field.value('@ColumnValue','nvarchar(4000)')) + '''' END + ','
FROM @OperateXml.nodes('/Operate/OperateFields/Field') Fields(Field)
SET @ColumnValues = SUBSTRING(@ColumnValues, 1, LEN(@ColumnValues) - 1)
SET @Sql = 'UPDATE [' + @TableName + '] SET ' + @ColumnValues + ' WHERE (1 = 1)' + @Conditions
EXECUTE(@Sql)
END
END
IF @OperateType = 'Delete'
BEGIN
SELECT @Conditions = @Conditions + ' AND ' +
CASE
WHEN Field.value('@ColumnValue','nvarchar(512)') IS NULL
THEN '([' + Field.value('@ColumnName','nvarchar(128)') + '] IS NULL)'
ELSE
'([' + Field.value('@ColumnName','nvarchar(128)') + '] = ''' + dbo.F_AvoidInject(Field.value('@ColumnValue','nvarchar(512)')) + ''')'
END
FROM @OperateXml.nodes('/Operate/ConditionFields/Field') Fields(Field)
IF @Conditions <> ''
BEGIN
SET @Sql = 'DELETE FROM [' + @TableName + '] WHERE (1 = 1)' + @Conditions
EXECUTE(@Sql)
END
END
FETCH NEXT FROM @OperateCursor INTO @OperateXml
END
CLOSE @OperateCursor
DEALLOCATE @OperateCursor
COMMIT TRANSACTION
SET @OutputesXml = @OutputesXml + '</Outputs>'
END
GO
实际调用
USE [ForwarderXa] GO /****** Object: StoredProcedure [dbo].[P_APP_AirExportBusinessUpdateWithXML] Script Date: 08/18/2016 14:04:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: 郑琦东 -- Create date: 2016-1-13 11:40:23 -- Description: -- ============================================= ALTER PROCEDURE [dbo].[P_APP_AirExportBusinessUpdateWithXML] @OperatesXml xml, @OutputesXml nvarchar(max)=NULL AS BEGIN --DECLARE @OutputesXml nvarchar(max) SET @OutputesXml = '<Outputs>' --insert into [t_test](content)values( CAST(@OperatesXml as nvarchar(max) ) ) --SET XACT_ABORT ON --BEGIN TRANSACTION DECLARE @OperateXml xml, @TableName nvarchar(128), @ConstCondition nvarchar(4000) DECLARE @Sql nvarchar(max), @ColumnValues nvarchar(max), @Conditions nvarchar(max) SELECT @OperateXml = Operate.query('.') FROM @OperatesXml.nodes('/Operates/Operate') Operates(Operate) SELECT @TableName = Operate.value('@TableName','nvarchar(128)'), @ConstCondition = Operate.value('@ConstCondition','nvarchar(4000)') FROM @OperateXml.nodes('/Operate') Operates(Operate) --SET @TableName='T_AirExportBusiness' DECLARE @AirExportBusinessId bigint IF @TableName='T_AirExportBusiness' BEGIN -- 重量 手动添加 -- declare @ActualWeight nvarchar(50) -- SELECT @ActualWeight = Field.value('@ColumnValue','nvarchar(50)') --FROM @OperateXml.nodes('/Operate/OperateFields/Field') Fields(Field) --WHERE (Field.value('@ColumnName','nvarchar(128)') = 'Weight') SET @OutputesXml = '<Outputs>' SELECT @AirExportBusinessId = Field.value('@ColumnValue','bigint') FROM @OperateXml.nodes('/Operate/ConditionFields/Field') Fields(Field) WHERE (Field.value('@ColumnName','nvarchar(128)') = 'AirExportBusinessId') SET @Conditions = ISNULL(@ConstCondition, '') SET @Conditions = @Conditions + ' AND (AirExportBusinessId = ' + CAST(@AirExportBusinessId AS nvarchar) + ')' SET @ColumnValues = '' SELECT @ColumnValues = @ColumnValues + '[' + Field.value('@ColumnName','nvarchar(128)') + '] = ' + CASE WHEN Field.value('@ColumnValue','nvarchar(4000)') IS NULL THEN 'NULL' ELSE '''' + dbo.F_AvoidInject(Field.value('@ColumnValue','nvarchar(4000)')) + '''' END + ',' FROM @OperateXml.nodes('/Operate/OperateFields/Field') Fields(Field) WHERE (Field.value('@ColumnName','nvarchar(128)') not in ('GreenLightTime','StorageTime','Width','Length','Height')) SET @ColumnValues = SUBSTRING(@ColumnValues, 1, LEN(@ColumnValues) - 1) --if @ActualWeight is not null or @ActualWeight<>'' --begin -- set @ColumnValues = @ColumnValues+', [' +'ActualWeight'+ '] = '+@ActualWeight --end SET @Sql = 'UPDATE [' + @TableName + '] SET ' + @ColumnValues + ' WHERE (1 = 1)' + @Conditions EXECUTE(@Sql) declare @GreenLightTime datetime declare @StorageTime datetime SELECT @GreenLightTime = Field.value('@ColumnValue','datetime') FROM @OperateXml.nodes('/Operate/OperateFields/Field') Fields(Field) WHERE (Field.value('@ColumnName','nvarchar(128)') = 'GreenLightTime') SELECT @StorageTime = Field.value('@ColumnValue','datetime') FROM @OperateXml.nodes('/Operate/OperateFields/Field') Fields(Field) WHERE (Field.value('@ColumnName','nvarchar(128)') = 'StorageTime') declare @Length decimal(18,3) declare @Width decimal(18,3) declare @Height decimal(18,3) declare @Weight decimal(18,3) SELECT @Length = Field.value('@ColumnValue','decimal(18,3)') FROM @OperateXml.nodes('/Operate/OperateFields/Field') Fields(Field) WHERE (Field.value('@ColumnName','nvarchar(128)') = 'Length') SELECT @Width = Field.value('@ColumnValue','decimal(18,3)') FROM @OperateXml.nodes('/Operate/OperateFields/Field') Fields(Field) WHERE (Field.value('@ColumnName','nvarchar(128)') = 'Width') SELECT @Height = Field.value('@ColumnValue','decimal(18,3)') FROM @OperateXml.nodes('/Operate/OperateFields/Field') Fields(Field) WHERE (Field.value('@ColumnName','nvarchar(128)') = 'Height') SELECT @Weight = Field.value('@ColumnValue','decimal(18,3)') FROM @OperateXml.nodes('/Operate/OperateFields/Field') Fields(Field) WHERE (Field.value('@ColumnName','nvarchar(128)') = 'Weight') -- 报关， 如果有报关记录，取第一条更新 放行时间， 如果没有，不新建 declare @customid bigint select @customid= Min(AirExportCustomsId) from T_AirExportCustoms where AirExportBusinessId=@AirExportBusinessId and IsDeleted=0 if ISNULL(@customid,0)>0 begin update T_AirExportCustoms set GreenLightTime=@GreenLightTime where AirExportCustomsId=@customid end -- 入库，如果有记录，取第一条更新各项数据， 如果没有，新建记录，加入数据 declare @stockid bigint if exists (select * from T_AirExportStockIn where AirExportBusinessId=@AirExportBusinessId and IsDeleted=0) begin select @stockid= Min(AirExportStockInId) from T_AirExportStockIn where AirExportBusinessId=@AirExportBusinessId and IsDeleted=0 update T_AirExportStockIn set StorageTime=@StorageTime ,CrateLength=@Length,CrateWidth=@Width,CrateHeight=@Height ,ActualWeight=@Weight where AirExportStockInId=@stockid end else begin if @StorageTime is not null begin insert T_AirExportStockIn (AirExportBusinessId,BusinessNumber,MawbNumber,HawbNumber,DestinationPortCode,DestinationPortName,KeepInNumber,GoodsName,Packages,ActualWeight,CrateVolume,StorageTime,IsDeleted,CrateLength ,CrateWidth ,CrateHeight ) select AirExportBusinessId,BusinessNumber,MawbNumber,HawbNumber,DestinationPortCode,DestinationPortName,KeepInNumber,GoodsName,Packages,@Weight,Volume,@StorageTime,0 ,@Length ,@Width ,@Height from T_AirExportBusiness WHERE AirExportBusinessId=@AirExportBusinessId end end SET @OutputesXml = @OutputesXml + '</Outputs>' END ELSE IF @TableName='T_AirExportCustoms' BEGIN EXEC [P_EntityDataOperateXaApp] @OperatesXml,@OutputesXml output END ELSE IF @TableName='T_AirExportStockIn' BEGIN declare @InsertXml xml ,@FirstField nvarchar(50) select @InsertXml=@OperatesXml.query('(/Operates/Operate[@OperateType="Insert"])') SELECT @FirstField = Field.value('@ColumnValue','nvarchar(50)') FROM @InsertXml.nodes('/Operate/OperateFields/Field') Fields(Field) WHERE (Field.value('@ColumnName','nvarchar(50)')) not in ('AirExportBusinessId','GoodsName') -- 没有填 数据时 不新增 if @FirstField <> '' or @FirstField is not null begin declare @AEBID bigint , @StockInPackages int, @StockInLength decimal(18,3), @StockInWidth decimal(18,3), @StockInHeight decimal(18,3), @StockInWeight decimal(18,3), @StockInVolume decimal(18,3) SELECT @StockInPackages = Field.value('@ColumnValue','int') FROM @InsertXml.nodes('/Operate/OperateFields/Field') Fields(Field) WHERE (Field.value('@ColumnName','nvarchar(128)') = 'Packages') SELECT @StockInLength = Field.value('@ColumnValue','decimal(18,3)') FROM @InsertXml.nodes('/Operate/OperateFields/Field') Fields(Field) WHERE (Field.value('@ColumnName','nvarchar(128)') = 'CrateLength') SELECT @StockInWidth = Field.value('@ColumnValue','decimal(18,3)') FROM @InsertXml.nodes('/Operate/OperateFields/Field') Fields(Field) WHERE (Field.value('@ColumnName','nvarchar(128)') = 'CrateWidth') SELECT @StockInHeight = Field.value('@ColumnValue','decimal(18,3)') FROM @InsertXml.nodes('/Operate/OperateFields/Field') Fields(Field) WHERE (Field.value('@ColumnName','nvarchar(128)') = 'CrateHeight') SELECT @StockInWeight = Field.value('@ColumnValue','decimal(18,3)') FROM @InsertXml.nodes('/Operate/OperateFields/Field') Fields(Field) WHERE (Field.value('@ColumnName','nvarchar(128)') = 'ActualWeight') SELECT @StockInVolume = Field.value('@ColumnValue','decimal(18,3)') FROM @InsertXml.nodes('/Operate/OperateFields/Field') Fields(Field) WHERE (Field.value('@ColumnName','nvarchar(128)') = 'CrateVolume') SELECT @AEBID = Field.value('@ColumnValue','bigint') FROM @InsertXml.nodes('/Operate/OperateFields/Field') Fields(Field) WHERE (Field.value('@ColumnName','nvarchar(128)') = 'AirExportBusinessId') insert T_AirExportStockIn (AirExportBusinessId,BusinessNumber,MawbNumber,HawbNumber,DestinationPortCode,DestinationPortName,KeepInNumber,GoodsName,Packages,ActualWeight,CrateVolume,IsDeleted,CrateLength ,CrateWidth ,CrateHeight ) select AirExportBusinessId,BusinessNumber,MawbNumber,HawbNumber,DestinationPortCode,DestinationPortName,KeepInNumber,GoodsName,@StockInPackages,@StockInWeight,@StockInVolume,0,@StockInLength ,@StockInWidth ,@StockInHeight from T_AirExportBusiness WHERE AirExportBusinessId=@AEBID end set @OperatesXml.modify('delete/Operates/Operate[@OperateType="Insert"]') EXEC [P_EntityDataOperateXaApp] @OperatesXml,@OutputesXml output END --COMMIT TRANSACTION SELECT @@ERROR AS result END
USE [ftjFreight] GO /****** Object: StoredProcedure [dbo].[P_CommonFreightImport] Script Date: 08/18/2016 14:00:48 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE [dbo].[P_CommonFreightImport] @dataXml xml AS BEGIN DECLARE @idoc int EXEC sp_xml_preparedocument @idoc OUTPUT, @dataXml insert dbo.WatchXML values (@dataXml,GETDATE()) insert into T_CommonFreightImport ( RowId, Hawb, ItemNumber, GoodsName, InvoiceNumber, [Dest], Packages, GW, NW, Qty, Unit, Currency, Amount, Verification1, Verification2, TradeTterms, Fee, InSurance, ContractNumber, MAWB, FlightNumber, Customer, CustomsNumber, CreateUserId, CreateUserName, UpdateUserId, UpdateUserName, GroupID, [FileName], UploadFileName ) SELECT [序号], [分单号], [项号], [品名], [发票号], [DES], [件数], [GW], [NW], [数量], [单位], [币种], [金额], [核销单1], [核销单2], [成交], [运费], [保费], [合同号], [MAWB], [航班], [客户], [报关单号], [操作员ID], [操作员名], [操作员ID], [操作员名], [组ID], [文件名], [上传文件名] FROM OPENXML (@idoc, '/NewDataSet/Table1',2) WITH ([序号] nvarchar(10), [分单号] nvarchar(50), [项号] nvarchar(50), [品名] nvarchar(50), [发票号] nvarchar(50), [DES] nvarchar(50), [件数] nvarchar(50), [GW] nvarchar(50), [NW] nvarchar(50), [数量] nvarchar(50), [单位] nvarchar(50), [币种] nvarchar(50), [金额] nvarchar(50), [核销单1] nvarchar(50), [核销单2] nvarchar(50), [成交] nvarchar(50), [运费] nvarchar(50), [保费] nvarchar(50), [合同号] nvarchar(50), [MAWB] nvarchar(50), [航班] nvarchar(50), [客户] nvarchar(50), [报关单号] nvarchar(50), [操作员ID] nvarchar(50), [操作员名] nvarchar(50), [文件名] nvarchar(50), [组ID] nvarchar(50), [上传文件名] nvarchar(50)) DECLARE @GroupID nvarchar(50) SELECT @GroupID=[组ID] FROM OPENXML (@idoc, '/NewDataSet/Table1',2) WITH ([组ID] nvarchar(50)) EXEC sp_xml_removedocument @idoc DECLARE curRecord CURSOR FOR SELECT Hawb, ItemNumber, GoodsName, InvoiceNumber, Dest, Packages, CAST(GW as numeric(18,3)), CAST(NW as numeric(18,3)), Qty, Unit, Currency, Amount, Verification1, Verification2, TradeTterms, Fee, Insurance, ContractNumber, MAWB, FlightNumber, Customer, CustomsNumber, BusinessNumber, CommonFreightImportId, CreateUserId, CreateUserName FROM T_CommonFreightImport WHERE GroupID=@GroupID OPEN curRecord DECLARE @Hawb nvarchar(50), @ItemNumber nvarchar(50), @GoodsName nvarchar(50), @InvoiceNumber nvarchar(50), @Dest nvarchar(50), @Packages nvarchar(50), @GW numeric(18,3), @NW numeric(18,3), @Qty nvarchar(50), @Unit nvarchar(50), @Currency nvarchar(50), @Amount numeric(18,3), @Verification1 nvarchar(50), @Verification2 nvarchar(50), @TradeTterms nvarchar(50), @Fee nvarchar(50), @Insurance nvarchar(50), @ContractNumber nvarchar(50), @MAWB nvarchar(50), @FlightNumber nvarchar(50), @Customer nvarchar(50), @CustomsNumber nvarchar(50), @BusinessNumber nvarchar(50), @CommonFreightImportId bigint, @CreateUserId int, @CreateUserName nvarchar(50) declare @i int set @i=1 declare @PrewVerson1 nvarchar(10)='' declare @PrewBid nvarchar(50)='' declare @PrewBisNumber nvarchar(50)='' FETCH NEXT FROM curRecord INTO @Hawb, @ItemNumber, @GoodsName, @InvoiceNumber, @Dest, @Packages, @GW, @NW, @Qty, @Unit, @Currency, @Amount, @Verification1, @Verification2, @TradeTterms, @Fee, @Insurance, @ContractNumber, @MAWB, @FlightNumber, @Customer, @CustomsNumber, @BusinessNumber, @CommonFreightImportId, @CreateUserId, @CreateUserName WHILE @@FETCH_STATUS=0 BEGIN IF ISNULL(@BusinessNumber,'')='' BEGIN DECLARE @BusinessIndex nvarchar(50) SELECT @BusinessIndex = COUNT(CommonFreightBusinessId) + 1 FROM T_CommonFreightBusiness WHERE (DATEDIFF(mm, CreateTime, GETDATE()) = 0) SET @BusinessNumber= 'TJCF' + CONVERT(nvarchar(4), GETDATE(), 12) + RIGHT('000' + CAST(@BusinessIndex AS nvarchar), 4) DECLARE @ConsignmentUnitName nvarchar(250) DECLARE @ConsignmentUnitCode nvarchar(50) DECLARE @ConsignmentUnitId nvarchar(50) set @ConsignmentUnitId='2314656694421946406' set @ConsignmentUnitName='天津松下电子部品有限公司' set @ConsignmentUnitCode='TJSX6' -- if @Hawb='中远' --begin -- set @ConsignmentUnitId='2314656694421946855' set @ConsignmentUnitName='阪急阪神国际货运代理（北京）有限公司天津分公司' set @ConsignmentUnitCode='TJBJBS' -- end -- else if @Hawb='辛克' --begin -- set @ConsignmentUnitId='2314656694421946886' set @ConsignmentUnitName='辛克全球货运有限公司' set @ConsignmentUnitCode='XKTJ6' -- end --else if @Hawb='华丰' --begin -- set @ConsignmentUnitId='2314656694421946603' set @ConsignmentUnitName='天津市兰赫国际货运代理有限公司' set @ConsignmentUnitCode='TJLHW' -- end -- else if @Hawb='日通' --begin -- set @ConsignmentUnitId='2314656694421946674' set @ConsignmentUnitName='日通国际物流（中国）有限公司天津分公司' set @ConsignmentUnitCode='TJNEC' -- end -- else if @Hawb='NNR' --begin -- set @ConsignmentUnitId='2314656694421946486' set @ConsignmentUnitName='锦海捷亚国际货运有限公司天津分公司' set @ConsignmentUnitCode='JHJY6' -- end -- else if @Hawb='UPS' --begin -- set @ConsignmentUnitId='2314656694421947003' set @ConsignmentUnitName='天津泛艺国际货运代理服务有限公司' set @ConsignmentUnitCode='TJUPS' -- end -- else if @Hawb='EXEL' --begin -- set @ConsignmentUnitId='2314656694421946457' set @ConsignmentUnitName='敦豪全球货运(中国)有限公司' set @ConsignmentUnitCode='TJDHL' -- end -- else if @Hawb='泛亚' --begin -- set @ConsignmentUnitId='2314656694421946887' set @ConsignmentUnitName='泛亚班拿' set @ConsignmentUnitCode='FYBN' -- end -- else -- begin -- set @ConsignmentUnitId='' set @ConsignmentUnitName='' set @ConsignmentUnitCode='' -- end declare @OperatorId int , @OperatorName nvarchar(50) if (@CreateUserName<>'康建伟' and @CreateUserName<>'潘万达' and @CreateUserName<>'陈晓轩' and @CreateUserName<>'王怀漭') begin Set @OperatorId='' set @OperatorName='' end else begin Set @OperatorId=@CreateUserId set @OperatorName=@CreateUserName end declare @TaxNumber nvarchar(50) Set @TaxNumber='' if @ItemNumber='1' and @GoodsName='传感器' set @TaxNumber='90299000' if @ItemNumber='2' and @GoodsName='防抖传感器' set @TaxNumber='85299049' if @ItemNumber='3' and @GoodsName='FA' set @TaxNumber='85332190' if @ItemNumber='4' and @GoodsName='ID' set @TaxNumber='85045000' if @ItemNumber='5' and @GoodsName='热敏传感器' set @TaxNumber='90299000.90' if @ItemNumber='7' and @GoodsName='FR' set @TaxNumber='85332110' if @ItemNumber='8' and @GoodsName='噪音滤波器' set @TaxNumber='85416000' if @ItemNumber='9' and @GoodsName='石墨片' set @TaxNumber='85459000' declare @SupervisionType nvarchar(50) declare @SupervisionTypeName nvarchar(50) if ISNUMERIC(@ItemNumber)=1 begin Set @SupervisionType='0615' Set @SupervisionTypeName='进口对料' end if @ItemNumber='一般' begin Set @SupervisionType='0110' Set @SupervisionTypeName='一般贸易' end BEGIN SET XACT_ABORT ON BEGIN TRANSACTION INSERT INTO T_CommonFreightBusiness(BusinessNumber,InvoiceNumber,BusinessClass,MasterBillNumber,HouseBillNumber,ConsignmentUnitId,ConsignmentUnitCode,ConsignmentUnitName,SettlementUnitId,SettlementUnitCode,SettlementUnitName,Consignor,SellerId,SellerName,BusinessDepartmentId,BusinessDepartmentName,OperatorId,OperatorName,GoodsName,Packages,CargoSourceKey,CargoSourceName,ActualWeight,OriginPlaceName,DestinationPlaceName,CurrencyType,Amount,TaxNumber,CreateUserId,CreateUserName,CreateTime,IsDeleted,BusinessIndex,OrderDate,TransportDate) VALUES(@BusinessNumber,@InvoiceNumber,'报关',@MAWB,@Hawb,@ConsignmentUnitId,@ConsignmentUnitCode,@ConsignmentUnitName,2314656694421946406,'TJSX6','天津松下电子部品有限公司','天津松下电子部品有限公司',6223,'华贸柏骏',753,'销售部',@OperatorId,@OperatorName,@GoodsName,@Packages,4,'操作指定同行',@GW,'TSN',@Dest,@Currency,@Amount,@TaxNumber,@CreateUserId,@CreateUserName,GETDATE(),0,@BusinessIndex,GETDATE(),Dateadd(DAY,1,GETDATE())) DECLARE @AirExportBusinessId bigint SET @AirExportBusinessId = @@IDENTITY if @Verification1=@PrewVerson1 begin update T_CustomsInformation set InvoiceNumber=InvoiceNumber+'/'+@InvoiceNumber ,Amount=Amount+@Amount,Number=cast(Number as int)+@Packages ,GrossWeight=GrossWeight+@GW ,GoodsName=GoodsName+','+@GoodsName where BusinessId=@PrewBid end else begin INSERT INTO T_CustomsInformation(BusinessId,BizNo,BusinessType,BusinessTypeName,HAWBNo,ConsignmentUnitId,ConsignmentUnitCode,ConsignmentUnitName,CustomsNumber,InvoiceNumber,GoodsName,TaxNumber,Currency,Amount,Number,GrossWeight,CustomsMethodCode,CustomsMethodName,CustomsTypeCode,CustomsTypeName,CustomsDocTypeCode,CustomsDocTypeName,CreateUserId,CreateUserName,CreateTime,IsDeleted,WorkFlowState,SupervisionType,SupervisionTypeName,BLNo,ManagementName,ApplicationDate,FreightDate) values(@AirExportBusinessId,@BusinessNumber,'CommonFreight','综合业务',@Hawb,@ConsignmentUnitId,@ConsignmentUnitCode,@ConsignmentUnitName,@CustomsNumber,@InvoiceNumber,@GoodsName,@TaxNumber,@Currency,@Amount,@Packages,@GW,'001','普通报关单','001','普通报关','001','一般报关单',@CreateUserId,@CreateUserName,GETDATE(),0,0,@SupervisionType,@SupervisionTypeName,@MAWB,'天津松下电子部品有限公司',GETDATE(),GETDATE()) Set @PrewVerson1=@Verification1 Set @PrewBid=@AirExportBusinessId Set @PrewBisNumber=@BusinessNumber end UPDATE dbo.T_CommonFreightImport SET BusinessNumber=@BusinessNumber WHERE GroupID=@GroupID and RowId=@i COMMIT TRANSACTION END set @i=@i+1 END FETCH NEXT FROM curRecord INTO @Hawb, @ItemNumber, @GoodsName, @InvoiceNumber, @Dest, @Packages, @GW, @NW, @Qty, @Unit, @Currency, @Amount, @Verification1, @Verification2, @TradeTterms, @Fee, @Insurance, @ContractNumber, @MAWB, @FlightNumber, @Customer, @CustomsNumber, @BusinessNumber, @CommonFreightImportId, @CreateUserId, @CreateUserName END CLOSE curRecord DEALLOCATE curRecord END GO
浙公网安备 33010602011771号