QQ：1187362408 欢迎技术交流和学习
数据导入（excel读取，存储进数据库【多表】），业务需求
TODO：
1，选择导入地区
2，数据校验：
（角色类别[1]：一个用户具有多角色 角色类别[2]：多用户单角色)
3，生成网格用户信息
讲解篇：1，服务端aspx,2，服务端后台3，前端javascript4，webservice返回数据（这里采用一般处理程序：handler.ashx.cs）
服务端aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="BaseInfoList.aspx.cs" Inherits="HMFW.Web.DataImport.BaseInfoList" %>
<%@ Register TagPrefix="uc1" TagName="uc_btnSelectArea" Src="~/UserControl/uc_btnSelectArea.ascx" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>数据导入基础表</title>
<script src="/Js/jquery-1.7.2.js" type="text/javascript"></script>
<script src="/Js/jquerytree/jquery.ztree.core-3.1.min.js" type="text/javascript"></script>
<link href="/Js/jquerytree/zTreeStyle/zTreeStyle.css" rel="stylesheet" type="text/css" />
<script src="/Js/jquery.easyui.min.js" type="text/javascript"></script>
<script src="/Js/jquery.form.js" type="text/javascript"></script>
<script src="/Js/jquery.validate.js" type="text/javascript"></script>
<link href="/themes/default/easyui.css" rel="stylesheet" type="text/css" />
<link href="/themes/icon.css" rel="stylesheet" type="text/css" />
<script src="/Js/jquery-loadmask-0.4/jquery.loadmask.min.js" type="text/javascript"></script>
<link href="/Js/jquery-loadmask-0.4/jquery.loadmask.css" rel="stylesheet" type="text/css" />
<link href="/css/width.css" rel="stylesheet" type="text/css" />
<script src="/Js/list.js" type="text/javascript"></script>
<script src="/Js/Common.js" type="text/javascript"></script>
<link href="/css/Common.css" rel="stylesheet" type="text/css" />
<script src="BaseInfoList.js" type="text/javascript"></script>
<script type="text/javascript" src="/Js/swfupload/swfupload.js"></script>
<script type="text/javascript" src="/Js/swfupload/handlers.js"></script>
<script type="text/javascript">
var swfu1; var swfu2;
window.onload = function () {
swfu1 = new SWFUpload({
upload_url: "BaseInfoListHandler.ashx?action=fileupload",
post_params: {
"ASPSESSID": "\"" + $("#hidSessionID").val() + "\""
},
file_size_limit: "10 MB",
file_types: "*.xls;*.xlsx",
file_types_description: "EXCEL文件",
file_upload_limit: 0,
swfupload_preload_handler: preLoad,
swfupload_load_failed_handler: loadFailed,
file_queue_error_handler: fileQueueError,
file_dialog_complete_handler: fileDialogComplete,
upload_progress_handler: uploadProgress,
upload_error_handler: uploadError,
upload_success_handler: uploadFileSuccess,
upload_complete_handler: uploadComplete,
button_image_url: "/Js/swfupload/XPButtonNoText_160x22.png",
button_placeholder_id: "spanButtonPlaceholder1",
button_width: 66,
button_height: 22,
button_text: '<span class="button"> </span>',
button_text_style: '.button { font-family: Helvetica, Arial, sans-serif; font-size: 14pt;} .buttonSmall { font-size: 10pt; }',
button_text_top_padding: 1,
button_text_left_padding: 5,
flash_url: "/Js/swfupload/swfupload.swf", // Relative to this file
flash9_url: "/Js/swfupload/swfupload_FP9.swf", // Relative to this file
custom_settings: {
upload_target: "divFileProgressContainer"
},
debug: false
});
swfu2 = new SWFUpload({
upload_url: "PeopleInfoListHandler.ashx?action=fileupload",
post_params: {
"ASPSESSID": "\"" + $("#hidSessionID").val() + "\""
},
file_size_limit: "10 MB",
file_types: "*.xls;*.xlsx",
file_types_description: "EXCEL文件",
file_upload_limit: 0,
swfupload_preload_handler: preLoad,
swfupload_load_failed_handler: loadFailed,
file_queue_error_handler: fileQueueError,
file_dialog_complete_handler: fileDialogComplete,
upload_progress_handler: uploadProgress,
upload_error_handler: uploadError,
upload_success_handler: uploadFileSuccess,
upload_complete_handler: uploadComplete,
button_image_url: "/Js/swfupload/XPButtonNoText_160x22.png",
button_placeholder_id: "spanButtonPlaceholder2",
button_width: 66,
button_height: 22,
button_text: '<span class="button"> </span>',
button_text_style: '.button { font-family: Helvetica, Arial, sans-serif; font-size: 14pt;} .buttonSmall { font-size: 10pt; }',
button_text_top_padding: 1,
button_text_left_padding: 5,
flash_url: "/Js/swfupload/swfupload.swf", // Relative to this file
flash9_url: "/Js/swfupload/swfupload_FP9.swf", // Relative to this file
custom_settings: {
upload_target: "divFileProgressContainer"
},
debug: false
});
}
$(function () {
divHeight();//设置错误提示信息的Div的高度
});
///设置错误提示信息的Div的高度
function divHeight() {
var iHeight = $(this).height() - $("#div_action").height() - 20;
$("#divError").height(iHeight);
}
</script>
</head>
<body>
<form id="form1" οnresize="divHeight();" runat="server">
<div id="div_action" style="margin: 10px 0 0 0;">
<table width="100%" border="0" cellspacing="0" class="findk">
<tbody>
<tr align="left">
<td align="right">模板下载：</td>
<td colspan="2"><a runat="server" id="WGdownload" style="text-decoration: none"><span style="font-weight: bolder; font-size: larger; color: red; margin-left: 10px;">社区网格信息模板下载</span></a></td>
<td class="d_hid"><a runat="server" id="RKdownload" style="text-decoration: none"><span style="font-weight: bolder; font-size: larger; color: red; margin-left: 10px;">社区人口信息模板下载</span></a></td>
<td><span style="font-weight: bolder; font-size: larger; color: red; margin-left: 10px;">门牌号显示图：格式【<label id="labMPH">ABc</label>】</span><%--<a runat="server" id="ZDdownload"><span class="xiazai"></span>字典对照表下载</a>--%></td>
</tr>
<tr align="left">
<td align="right" style="width: 12%">第一步：</td>
<td align="left" style="width: 13%">选择导入地区</td>
<td style="width: 18%"></td>
<td rowspan="3">
<asp:Image ID="imgMPH" runat="server" ImageUrl="../../Images/MPH/1ABc.jpg" />
</td>
</tr>
<tr>
<td></td>
<td align="right">
<div class="divnr-auto">
地区：
</div>
</td>
<td align="left">
<uc1:uc_btnSelectArea ID="uc_btnSelectArea1" runat="server" SetSearchFunction="VillageContrlo()" ShowClearBtn="true" Width="153"
CanSelParent="True" />
</td>
</tr>
<tr>
<td align="right">第二步：</td>
<td align="left">导入基础信息数据</td>
<td></td>
</tr>
<tr>
<td></td>
<td align="right" nowrap>
<font color="red">*</font>文件上传（社区网格信息）：
</td>
<td align="left" nowrap style="height: 50px">
<div style="float: left; width: 130px;">
<asp:TextBox ID="txtimg1" ContentEditable="false" runat="server" class="srk001"></asp:TextBox>
</div>
<div id="divUpLoad1" style="float: left; width: 66px; z-index: -1; margin-top: 1px">
<span id="spanButtonPlaceholder1"></span>
</div>
<br />
<input type="hidden" id="FieldUrl1" runat="server" />
</td>
<td align="left" width="10%" nowrap height="20">
<div style="float: left; width: 50px;">
<xhm:xhmButtionEasyUI ID="btn_Import1" runat="server" IconTypeSelected="save" OnClientClick="Import1();return false;">文件上传</xhm:xhmButtionEasyUI>
</div>
<div id="divWGUserMes" style="float: right; color: red; font-size: 13px; line-height: 18px; font-weight: 600;">
提示：生成的网格用户请到【内网系统管理】--【用户管理】--【用户列表】<br />
菜单里查看，所有用户初始密码为123，请及时修改密码！！！
</div>
</td>
</tr>
<tr>
<td></td>
<td></td>
<td>
<div id="divMes" style="float: left; color: red; font-size: 13px; font-weight: 600; padding-top: 2px">
</div>
</td>
<td colspan="3">
<div class="datagrid-toolbar">
<table>
<tr>
<td> 门牌号格式：<select id="ddl_sFormat" runat="server" class="xlk001">
<option value="ABc">ABc</option>
<option value="ABC">ABC</option>
<option value="BCC">BCC</option>
<option value="A-Bcc">A-Bcc</option>
<option value="A-BCC">A-BCC</option>
<option value="A-B-C">A-B-C</option>
<option value="A-B-c">A-B-c</option>
</select></td>
<td>
<xhm:xhmLinkButtionEasyUI ID="btnValidate1" runat="server" Enabled="false" IconTypeSelected="dc"
OnClientClick="return DataValidate1();">数据校验</xhm:xhmLinkButtionEasyUI>
</td>
<td>
<xhm:xhmLinkButtionEasyUI ID="btnInsertData1" runat="server" Enabled="false" IconTypeSelected="dc"
OnClientClick="return ValidateBuild();">生成基础信息</xhm:xhmLinkButtionEasyUI>
</td>
<td>
<xhm:xhmLinkButtionEasyUI ID="btnCreateWGUser" runat="server" Enabled="false" IconTypeSelected="dc"
OnClientClick="return IntelligentWGUser();">生成网格用户信息</xhm:xhmLinkButtionEasyUI>
</td>
</tr>
</table>
</div>
</td>
</tr>
<tr class="d_hid">
<td align="right">第三步：</td>
<td align="left">导入人口信息数据</td>
<td></td>
<td></td>
</tr>
<tr class="d_hid">
<td></td>
<td align="right" nowrap>
<font color="red">*</font>文件上传（社区人口信息）：
</td>
<td align="left" nowrap style="height: 50px">
<div style="float: left; width: 130px;">
<asp:TextBox ID="txtimg2" ContentEditable="false" runat="server" class="srk001"></asp:TextBox>
</div>
<div id="divUpLoad2" style="float: left; width: 66px; display: none; z-index: -1; margin-top: 1px">
<span id="spanButtonPlaceholder2"></span>
</div>
<br />
<%--<div style="float: left; padding-top: 2px">
<font color="red">(文件不得超过10M)</font>
</div>--%>
<input type="hidden" id="FieldUrl2" runat="server" />
</td>
<td align="left" width="10%" nowrap height="20">
<xhm:xhmButtionEasyUI ID="btn_Import2" runat="server" IconTypeSelected="save" OnClientClick="Import2();return false;">文件上传</xhm:xhmButtionEasyUI>
</td>
</tr>
<tr class="d_hid">
<td></td>
<td></td>
<td></td>
<td colspan="3">
<div class="datagrid-toolbar">
<table>
<tr>
<td>
<xhm:xhmLinkButtionEasyUI ID="btnValidate2" runat="server" Enabled="false" IconTypeSelected="dc"
OnClientClick="DataValidate2();return false;">数据校验</xhm:xhmLinkButtionEasyUI>
</td>
<td>
<xhm:xhmLinkButtionEasyUI ID="btnInsertData2" runat="server" Enabled="false" IconTypeSelected="dc"
OnClientClick="IntelligentImPort2();return false;">生成家庭人口信息</xhm:xhmLinkButtionEasyUI></td>
</tr>
</table>
</div>
</td>
</tr>
</tbody>
</table>
</div>
<div id="divError" style="font-size: 16px; overflow: auto;" class="error"></div>
<input type="hidden" runat="server" id="hidRandomID" />
<input type="hidden" runat="server" id="hidMph" />
</form>
</body>
</html>
服务端后台
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace HMFW.Web.DataImport
{
public partial class BaseInfoList : BasePage
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
hidRandomID.Value = Guid.NewGuid().ToString();//页面导入数据标识，Guid
}
string action = GetParam("action");
if (!IsPostBack && string.IsNullOrEmpty(action))
{
//XX社区实有人口信息采集模板
string RKfilePath = Server.UrlEncode(Server.MapPath("/DocTemplate/社区人口信息采集模板.xls"));
string RKresult = "BaseInfoList.aspx?action=xiazai&filepath=" + RKfilePath + "&filename=" + Server.UrlEncode("社区人口信息采集模板.xls");
RKdownload.HRef = RKresult;
//XX社区网格基础信息采集模板
string WGfilePath = Server.UrlEncode(Server.MapPath("/DocTemplate/社区网格信息采集模板.xls"));
string WGresult = "BaseInfoList.aspx?action=xiazai&filepath=" + WGfilePath + "&filename=" + Server.UrlEncode("社区网格信息采集模板.xls");
WGdownload.HRef = WGresult;
字典表
//string ZDfilePath = Server.UrlEncode(Server.MapPath("/DocTemplate/字典对照表.xls"));
//string ZDresult = "BaseInfoList.aspx?action=xiazai&filepath=" + ZDfilePath + "&filename=" + Server.UrlEncode("字典对照表.xls");
//ZDdownload.HRef = ZDresult;
}
if (!string.IsNullOrEmpty(action))
Download();
}
/// <summary>
/// 下载事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Download()
{
//客户端保存的文件名
string filePath = GetParam("filepath");
string fileName = GetParam("filename");
if (!System.IO.File.Exists((filePath)))
{
Message("文件不存在！");
return;
}
//以字符流的形式下载文件
FileStream fs = new FileStream((filePath), FileMode.Open);
if (fs.Length > 0)
{
byte[] bytes = new byte[(int)fs.Length];
fs.Read(bytes, 0, bytes.Length);
fs.Close();
Response.ContentType = "application/octet-stream";
//通知浏览器下载文件而不是打开
Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
Response.BinaryWrite(bytes);
Response.Flush();
Response.End();
}
else
{
Message("指定文件内容为空！");
}
}
}
}前端javascript
var handleurl1 = "BaseInfoListHandler.ashx"; //一般处理程序路径
var handleurl2 = "PeopleInfoListHandler.ashx"; //一般处理程序路径
var sVillageCode = "";//选择的地区编码
var sVillageName = "";//选择的地区名称
var sRandomID = "";//页面导入数据标识，Guid
$(function () {
$("#btn_Import2,#btnValidate1,#btnInsertData1,#btnCreateWGUser").attr("disabled", "disabled");
///谷歌下Button禁用没用，以下方法针对无用的浏览器
$("#btn_Import2,#btnValidate1,#btnInsertData1,#btnCreateWGUser").css("color", "gray").removeAttr("onclick");
sRandomID = $("#hidRandomID").val();//页面导入数据标识，Guid
sVillageCode = $("#uc_btnSelectArea1_hidAreaCode").val();//选择的地区编码
///如果是社区，判断这个社区是否已导入基础数据，如已导入，进行控制不允许重复导入
if (sVillageCode.length == 12) {
///基础数据已导入，不允许重复导入
if (ExistData(sVillageCode) == true) {
$("#uc_btnSelectArea1_txtAreaName").attr("disabled", "disabled");//之后生成数据，地区不可变动
$("#uc_btnSelectArea1_imgselect,#uc_btnSelectArea1_imgclear").hide();//控制地区控件不可选
$("#btn_Import1,#btnValidate1,#btnInsertData1,#txtimg1,#ddl_sFormat").attr("disabled", "disabled");
$("#divUpLoad1").hide();
$("#divUpLoad2").show();
$("#btn_Import2").removeAttr("disabled");
$("#divMes").html("基础数据已导入！");//请直接导入人口信息！");
//if (ExistWGUser(sVillageCode) == true) {//验证网格用户是否已生成
// $("#btnCreateWGUser").attr("disabled", "disabled");//生成网格用户按钮不可用
// //去用户管理查看已生成的网格用户
// $("#divWGUserMes").html("提示：生成的网格用户请到【内网系统管理】--【用户管理】--【用户列表】<br/>菜单里查看，所有用户初始密码为123，请及时修改密码！！！");
//}
return false;
}
}
if (sVillageCode.length == 12) {
VillageContrlo();
}
$("#ddl_sFormat").change(function () {
setMph(this.value);
});
});
function setMph(value) {
$("#labMPH").text(value);
var iIndex = $("#ddl_sFormat option:selected").index();
$("#imgMPH").attr("src", "../../Images/MPH/" + Number(iIndex + 1) + value + ".jpg");
}
///如果是社区，判断这个社区是否已导入基础数据，如已导入，进行控制不允许重复导入
function VillageContrlo() {
sVillageCode = $("#uc_btnSelectArea1_hidAreaCode").val();//选择的地区编码
if (sVillageCode.length == 12) {
///基础数据已导入，不允许重复导入
if (ExistData(sVillageCode) == true) {
$("#btn_Import1,#btnValidate1,#btnInsertData1,#txtimg1,#ddl_sFormat").attr("disabled", "disabled");
$("#divUpLoad1").hide();
$("#divUpLoad2").show();
$("#btn_Import2").removeAttr("disabled");
$("#divMes").html("基础数据已导入！");//请直接导入人口信息！");
var value = $("#hidMph").val();
$("#ddl_sFormat").val(value);
setMph(value);
//if (ExistWGUser(sVillageCode) == true) {//验证网格用户是否已生成
// $("#btnCreateWGUser").attr("disabled", "disabled");//生成网格用户按钮不可用
// //去用户管理查看已生成的网格用户
// $("#divWGUserMes").html("提示：生成的网格用户请到【内网系统管理】--【用户管理】--【用户列表】<br/>菜单里查看，所有用户初始密码为123，请及时修改密码！！！");
//}
return false;
} else {
$("#btn_Import1,#txtimg1,#ddl_sFormat").removeAttr("disabled");
$("#divUpLoad1").show();
$("#divUpLoad2").hide();
$("#btn_Import2").attr("disabled", "disabled");
$("#divMes").html("");
$("#ddl_sFormat").val("ABc");
setMph("ABc");
return false;
}
}
}
///确定导入----基础信息
function Import1() {
sVillageCode = $("#uc_btnSelectArea1_hidAreaCode").val();//选择的地区编码
sVillageName = $("#uc_btnSelectArea1_txtAreaName").val();//选择的地区名称
if ($.trim(sVillageCode).length != 12) {//判断地区是否选择到社区
$.messager.alert('信息', "地区请选择到社区！", 'info');
return false;
}
///判断基础数据是否已导入，已导入，不允许重复导入
if (ExistData(sVillageCode) == true) {
$.messager.alert('信息', sVillageName + "已导入基础数据，不能重复导入！如需重新导入，请联系管理员进行删除数据！", 'info');
return false;
}
if ($("#txtimg1").val() == "") {//判断是否上传了文件
$.messager.alert('信息', "请选择上传文件！", 'info');
return false;
}
var data = "action=inexport&filePath=" + $("#FieldUrl1").val() + "&sVillageCode=" + sVillageCode + "&sVillageName=" + sVillageName + "&sRandomID=" + sRandomID;
$.ajax({
type: "POST",
url: handleurl1,
data: data,
dataType: "json",
error: function (xmlHttpRequest) {
$.messager.alert('错误', xmlHttpRequest.responseText, 'error');
},
success: function (returnJsonValue) {
if (returnJsonValue.result == true) {
$.messager.alert('信息', returnJsonValue.data, 'info'); //弹出消息
$("#btnValidate1").removeAttr("disabled").css("color", "").attr("onclick", "return DataValidate1();");//数据验证按钮可用
$("#uc_btnSelectArea1_txtAreaName").attr("disabled", "disabled");//之后生成数据，地区不可变动
$("#uc_btnSelectArea1_imgselect,#uc_btnSelectArea1_imgclear").hide();//控制地区控件不可选
$("#divError").empty();//清空错误结果提示信息【针对重新导入，之前导入错误的】
}
else {
$.messager.alert('信息', "数据校验失败！详细信息请查看校验结果！", 'info'); //弹出消息
$("#divError").empty().html(returnJsonValue.data);//.show();
$("#btnInsertData1,#btnCreateWGUser").attr("disabled", "disabled");//生成基础信息按钮不可用
///谷歌下Button禁用没用，以下方法针对无用的浏览器
$("#btnInsertData1,#btnCreateWGUser").css("color", "gray").removeAttr("onclick");
}
$("#btnValidate2").attr("disabled", "disabled");//数据验证按钮不可用
$("#btnInsertData2").attr("disabled", "disabled");//生成家庭人口信息按钮不可用
},
beforeSend: function () {
$("body").mask("数据处理中，请稍候...");
},
complete: function () {
$("body").unmask();
}
});
return false;
}
///是否已存在数据，导入基础数据之前验证当时社区的基础数据是否已导入，存在 true 不存在 false
function ExistData(sVillageCode) {
var result = false;
var data = "action=existdata&sVillageCode=" + sVillageCode;
$.ajax({
type: "POST",
url: handleurl1,
data: data,
async: false,
dataType: "json",
error: function (xmlHttpRequest) {
$.messager.alert('错误', "Error：" + xmlHttpRequest.responseText, 'error');
},
success: function (returnJsonValue) {
result = returnJsonValue.result;
$("#hidMph").val(returnJsonValue.ID);
}
});
return result;
}
///是否已存在数据，导入基础数据之前验证当时社区的基础数据是否已导入，存在 true 不存在 false
function ExistWGUser(sVillageCode) {
var result = false;
var data = "action=existwguser&sVillageCode=" + sVillageCode;
$.ajax({
type: "POST",
url: handleurl1,
data: data,
async: false,
dataType: "json",
error: function (xmlHttpRequest) {
$.messager.alert('错误', "Error：" + xmlHttpRequest.responseText, 'error');
},
success: function (returnJsonValue) {
result = returnJsonValue.result;
}
});
return result;
}
///数据校验----基础信息
function DataValidate1() {
var data = "action=datavalidate&sVillageCode=" + sVillageCode + "&sVillageName=" + sVillageName + "&sRandomID=" + sRandomID;
$.ajax({
type: "POST",
url: handleurl1,
data: data,
async: false,
dataType: "json",
error: function (xmlHttpRequest) {
$.messager.alert('错误', "Error：" + xmlHttpRequest.responseText, 'error');
$("#divError").empty().html(xmlHttpRequest.responseText);//.show();
},
success: function (returnJsonValue) {
if (returnJsonValue.result == true) {
$.messager.alert('信息', returnJsonValue.data, 'info'); //弹出消息
$("#btnInsertData1").removeAttr("disabled").css("color", "").attr("onclick", "return ValidateBuild();");;//生成基础数据按钮可用
$("#divError").empty();//清空错误结果提示信息
}
else {
$.messager.alert('信息', "数据校验失败！详细信息请查看校验结果！", 'info'); //弹出消息
$("#divError").empty().html(returnJsonValue.data);//.show();
$("#btnInsertData1,#btnCreateWGUser").attr("disabled", "disabled");//生成基础信息按钮不可用
///谷歌下Button禁用没用，以下方法针对无用的浏览器
$("#btnInsertData1,#btnCreateWGUser").css("color", "gray").removeAttr("onclick");
}
},
beforeSend: function () {
$("body").mask("数据处理中，请稍候...");
},
complete: function () {
$("body").unmask();
}
});
return false;
}
///验证楼层是否有大于33层的，大于33层进行信息提示，是否确定生成基础信息
function ValidateBuild() {
var result = true;
var data = "action=validatebuild&sVillageCode=" + sVillageCode;
$.ajax({
type: "POST",
url: handleurl1,
data: data,
async: false,
dataType: "json",
error: function (xmlHttpRequest) {
$.messager.alert('错误', "Error：" + xmlHttpRequest.responseText, 'error');
},
success: function (returnJsonValue) {
if (returnJsonValue.result == true) {
IntelligentImPort1();
}
else {
var strMes = returnJsonValue.data;
$.messager.confirm('数据提示', '<div style="margin-top:0px;line-height:22px">' + strMes, function (data) {
if (data) {
IntelligentImPort1();
}
});
}
}
});
return result;
}
///一键导入网格小区楼栋并生成房屋----基础信息
function IntelligentImPort1() {
///判断基础数据是否已导入，已导入，不允许重复导入
if (ExistData(sVillageCode) == true) {
$.messager.alert('信息', sVillageName + "已导入基础数据，不能重复导入！如需重新导入，请联系管理员进行删除数据！", 'info');
return false;
}
var sFormat = $("#ddl_sFormat").val();
var data = "action=intelligentimport&sVillageCode=" + sVillageCode + "&sVillageName=" + sVillageName + "&sFormat=" + sFormat + "&sRandomID=" + sRandomID;
$.ajax({
type: "POST",
url: handleurl1,
data: data,
dataType: "json",
async: false,
error: function (xmlHttpRequest) {
$.messager.alert('错误', xmlHttpRequest.responseText, 'error');
},
success: function (returnJsonValue) {
$.messager.alert('信息', returnJsonValue.data, 'info'); //弹出消息
if (returnJsonValue.result == true) {
$("#btn_Import1,#btnValidate1,#btnInsertData1,#txtimg1,#ddl_sFormat").attr("disabled", "disabled");
$("#divUpLoad1").hide();
$("#divUpLoad2").show();
$("#btn_Import2").removeAttr("disabled");
$("#btnCreateWGUser").removeAttr("disabled").css("color", "").attr("onclick", "return IntelligentWGUser();");;//生成网格用户信息
}
},
beforeSend: function () {
$("body").mask("数据处理中，请稍候...");
},
complete: function () {
$("body").unmask();
}
});
return false;
}
///一键生成网格用户信息：默认密码 123
function IntelligentWGUser() {
var data = "action=intelligentwguser&sVillageCode=" + sVillageCode + "&sVillageName=" + sVillageName + "&sRandomID=" + sRandomID;
$.ajax({
type: "POST",
url: handleurl1,
data: data,
dataType: "json",
async: false,
error: function (xmlHttpRequest) {
$.messager.alert('错误', xmlHttpRequest.responseText, 'error');
},
success: function (returnJsonValue) {
$.messager.alert('信息', returnJsonValue.data, 'info'); //弹出消息
if (returnJsonValue.result == true) {
$("#btn_Import1,#btnValidate1,#btnInsertData1,#txtimg1,#ddl_sFormat,#btnCreateWGUser").attr("disabled", "disabled");
$("#divUpLoad1").hide();
$("#divUpLoad2").show();
$("#btn_Import2").removeAttr("disabled");
Add_2("/Pages/UserRoleManage/UserList.aspx", "sVillageCode=" + sVillageCode + "&sVillageName=" + sVillageName + "&iUserType=2", 600, 600, false);//跳转用户管理界面
}
},
beforeSend: function () {
$("body").mask("数据处理中，请稍候...");
},
complete: function () {
$("body").unmask();
}
});
return false;
}
///确定导入----人口信息
function Import2() {
sVillageCode = $("#uc_btnSelectArea1_hidAreaCode").val();//选择的地区编码
sVillageName = $("#uc_btnSelectArea1_txtAreaName").val();//选择的地区名称
if ($("#txtimg2").val() == "") {//判断是否上传了文件
$.messager.alert('信息', "请上传文件！", 'info');
return false;
}
var data = "action=inexport&filePath=" + $("#FieldUrl2").val() + "&sRandomID=" + sRandomID + "&sVillageCode=" + sVillageCode + "&sVillageName=" + sVillageName;
$.ajax({
type: "POST",
url: handleurl2,
data: data,
dataType: "json",
error: function (xmlHttpRequest) {
$.messager.alert('错误', xmlHttpRequest.responseText, 'error');
},
success: function (returnJsonValue) {
if (returnJsonValue.result == true) {
$.messager.alert('信息', returnJsonValue.data, 'info'); //弹出消息
$("#btnValidate2").removeAttr("disabled");//数据验证按钮可用
$("#divError").empty();//清空错误结果提示信息
}
else {
$.messager.alert('信息', "数据校验失败！详细信息请查看校验结果！", 'info'); //弹出消息
$("#divError").empty().html(returnJsonValue.data);//.show();
$("#btnInsertData2").attr("disabled", "disabled");//生成家庭人口信息按钮不可用
}
},
beforeSend: function () {
$("body").mask("数据处理中，请稍候...");
},
complete: function () {
$("body").unmask();
}
});
return false;
}
///数据校验----人口信息
function DataValidate2() {
var data = "action=datavalidate&sVillageCode=" + sVillageCode + "&sVillageName=" + sVillageName + "&sRandomID=" + sRandomID;
$.ajax({
type: "POST",
url: handleurl2,
data: data,
dataType: "json",
error: function (xmlHttpRequest) {
$.messager.alert('错误', "Error：数据校验失败！详细信息请查看校验结果！", 'error');
$("#divError").empty().html(xmlHttpRequest.responseText);//.show();
},
success: function (returnJsonValue) {
if (returnJsonValue.result == true) {
$.messager.alert('信息', returnJsonValue.data, 'info'); //弹出消息
$("#btnInsertData2").removeAttr("disabled");//生成家庭人口信息按钮可用
$("#divError").empty();//清空错误结果提示信息
}
else {
$.messager.alert('信息', "数据校验失败！详细信息请查看校验结果！", 'info'); //弹出消息
$("#divError").empty().html(returnJsonValue.data);//.show();
$("#btnInsertData2").attr("disabled", "disabled");//生成家庭人口信息按钮不可用
}
},
beforeSend: function () {
$("body").mask("数据处理中，请稍候...");
},
complete: function () {
$("body").unmask();
}
});
return false;
}
///导入家庭、人口信息
function IntelligentImPort2() {
var data = "action=intelligentimport&sVillageCode=" + sVillageCode + "&sVillageName=" + sVillageName + "&sRandomID=" + sRandomID;
$.ajax({
type: "POST",
url: handleurl2,
data: data,
dataType: "json",
error: function (xmlHttpRequest) {
$.messager.alert('错误', xmlHttpRequest.responseText, 'error');
},
success: function (returnJsonValue) {
$.messager.alert('信息', returnJsonValue.data, 'info'); //弹出消息
$("#btnValidate2").attr("disabled", "disabled");//数据验证按钮不可用
$("#btnInsertData2").attr("disabled", "disabled");//生成家庭人口信息按钮不可用
},
beforeSend: function () {
$("body").mask("数据处理中，请稍候...");
},
complete: function () {
$("body").unmask();
}
});
return false;
}webservice返回数据（这里采用一般处理程序：handler.ashx.cs）
BaseInfoListHandler
using HMFW.BLL.UserRoleManage;
using HMFW.Common;
using HMFW.Model;
using HMFW.SQLServerDAL;
using HMFW.Web.App_Code;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
namespace HMFW.Web.DataImport
{
/// <summary>
/// BaseInfoListHandler 的摘要说明
/// </summary>
public class BaseInfoListHandler : BaseHandler
{
UsersBLL userBll = new UsersBLL();
protected override void ActionHandler(string action)
{
switch (action.ToLower())
{
case "fileupload"://上传社区导入数据
UploadFile();
break;
case "existdata"://验证基础数据是否已导入
ExistData();
break;
case "inexport"://读取Excel并插入数据到临时表
ReadExcel();
break;
case "datavalidate"://数据校验
DataValidate();
break;
case "validatebuild"://验证楼层
ValidateBuild();
break;
case "intelligentimport"://一键导入网格小区楼栋
IntelligentImPort();
break;
case "existwguser"://验证网格用户是否已导入
ExistWGUser();
break;
case "intelligentwguser"://一键生成网格用户信息
IntelligentWGUser();
break;
default:
break;
}
}
#region 文件上传
/// <summary>
/// 文件上传
/// </summary>
private void UploadFile()
{
string pathPrefix = "/upload/DataImport/BaseInfo/";
try
{
HttpPostedFile file = _context.Request.Files["Filedata"];
if (file == null || file.ContentLength == 0)
throw new ArgumentOutOfRangeException("没有文件需要上传或文件过小！");
if (file.ContentLength > 10000000)
throw new ArgumentOutOfRangeException(string.Format("所上传的文件大小{0}字节超过指定大小{1}字节！", file.ContentLength, 10000000));
string[] strings = file.FileName.Split('.');
string path = Path.Combine(pathPrefix,
DateTime.Now.ToString("yyyyMMddHHmmss") + DateTime.Now.Millisecond + "." +
strings[strings.Length - 1]);
string sbst;
try
{
sbst = HttpContext.Current.Server.MapPath(path);
}
catch
{
sbst = path;
}
FileInfo fileInfo = new FileInfo(sbst);
if (!fileInfo.Directory.Exists)
fileInfo.Directory.Create();
file.SaveAs(sbst);
_context.Response.StatusCode = 200;
_context.Response.Write(path);
}
catch
{
// If any kind of error occurs return a 500 Internal Server error
_context.Response.StatusCode = 500;
_context.Response.Write("An error occured");
_context.Response.End();
}
finally
{
_context.Response.End();
}
}
#endregion
#region 验证基础数据是否已导入
/// <summary>
/// 验证基础数据是否已导入
/// </summary>
private void ExistData()
{
bool bResult = false;
string sMes = "验证成功！";
string mph = "ABc";
string sVillageCode = GetParam("sVillageCode");
string sVillageName = GetParam("sVillageName");
string strDelSQL = "select * from _SQ_DataImportLog where sVillageCode='" + sVillageCode + "' and iType=1";
DataTable dtBaseData = SqlHelper.ExecuteSqlToTable(strDelSQL);
if (dtBaseData.Rows.Count > 0)
{
bResult = true;
sMes = "基础数据已导入，不允许重复导入！";
mph = dtBaseData.Rows[0]["sFormat"].ToString();
}
OutputResultCarryID(bResult, sMes, mph);
}
#endregion
#region 验证网格用户是否已生成
/// <summary>
/// 验证网格用户是否已生成
/// </summary>
private void ExistWGUser()
{
bool bResult = false;
string sMes = "验证成功！";
string sVillageCode = GetParam("sVillageCode");
string sVillageName = GetParam("sVillageName");
string strDelSQL = "select * from _SQ_DataImportLog where sVillageCode='" + sVillageCode + "' and iType=3";
DataTable dtBaseData = SqlHelper.ExecuteSqlToTable(strDelSQL);
if (dtBaseData.Rows.Count > 0)
{
bResult = true;
//sMes = "网格用户已生成，不允许重复生成！";
}
OutputResult(bResult, sMes);
}
#endregion
#region Excel数据导入数据库
/// <summary>
/// Excel数据导入数据库
/// </summary>
private void ReadExcel()
{
string sVillageCode = GetParam("sVillageCode");
string sVillageName = GetParam("sVillageName");
string sFileName = GetParam("filePath");
string sRandomID = GetParam("sRandomID");//页面导入数据标识，Guid
string connStr = "";
string fileType = System.IO.Path.GetExtension(sFileName);
if (string.IsNullOrEmpty(fileType))
{
Output("请选择需要导入的文件！");
}
sFileName = _context.Server.MapPath(sFileName);
if (fileType == ".xls")
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + sFileName + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
else
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + sFileName + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
string sql_F = "Select * FROM [{0}]";
DataTable dtResult = new DataTable();
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataTable dtSheetName = null;
bool resul = true;
string Mes = "上传成功！";
//bool bWGUpdate = true;//网格编码更新
//bool bHomeBuildUpdate = true;//小区楼栋更新
try
{
// 初始化连接，并打开
conn = new OleDbConnection(connStr);
conn.Open();
// 获取数据源的表定义元数据
string SheetName = "";
dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
// 初始化适配器
da = new OleDbDataAdapter();
//SheetName = (string)dtSheetName.Rows[0]["TABLE_NAME"];
for (int i = 0; i < dtSheetName.Rows.Count; i++)
{
SheetName += dtSheetName.Rows[i]["TABLE_NAME"].ToString();
}
if (SheetName.Contains("社区网格信息$"))
{
da.SelectCommand = new OleDbCommand(String.Format(sql_F, "社区网格信息$"), conn);
da.Fill(dtResult);
removeEmpty(dtResult);//去除DataTable空行
string errMes = "";
if (!CheckRule(dtResult, out errMes, sVillageName))
{
resul = false;
Mes = errMes;
}
else
{
string strDelSQL = "delete from _SQ_Excel_BaseInfo where 地区编码='" + sVillageCode + "'";
SqlHelper.ExecuteSql(strDelSQL);
foreach (DataRow r in dtResult.Rows)
{
if (!string.IsNullOrEmpty(r[0].ToString().Trim()) && r[0].ToString() != "序号")
{
if (!(r[0].ToString().Contains("说明") || r[0].ToString().Contains("必填项") || r[0].ToString().Contains("规则") || r[0].ToString().Contains("例如") || r[0].ToString().Contains("导入")))
{
string strSQL = "insert into _SQ_Excel_BaseInfo(楼栋主键,地区名称,地区编码,序号,社区名称,网格名称,小区名称,楼栋名称,总层数,负层数,单元数,每单元每层户数,单元户数,不生成的门牌号,商品房,车库,备注,sRandomID)values(NEWID(),'" +
sVillageName + "','" + sVillageCode + "','" + r[0] + "','" + r[1] + "','" + r[2] + "','" + r[3] + "','" + r[4] + "','" + r[5] + "','" + r[6] + "','" + r[7] + "','" + r[8] + "','" + r[9] + "','" + r[10] + "','" + r[11] + "','" + r[12] + "','" + r[13] + "','" + sRandomID + "')";
SqlHelper.ExecuteSql(strSQL);
}
}
}
SqlParameter[] paraList = new SqlParameter[]{
new SqlParameter("@sAreaCode",sVillageCode)
};
SqlHelper.ExecuteStoredProcedure("proc_StripBaseInfoSpaces", paraList);
}
}
else
{
resul = false;
Mes = "导入失败！Excel中不包含名称为“社区网格信息”的工作薄！";
}
}
catch (Exception ex)
{
resul = false;
Mes = "导入失败！请联系管理员！";
}
finally
{
// 关闭连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
da.Dispose();
conn.Dispose();
}
}
OutputResult(resul, Mes);
}
#endregion
#region 数据校验
/// <summary>
/// 数据校验
/// </summary>
private void DataValidate()
{
bool bCheckResult = true;
string mes = "";
string sVillageCode = GetParam("sVillageCode");//地区控件选择的导入数据的地区---编码
string sVillageName = GetParam("sVillageName");//地区控件选择的导入数据的地区---名称
//string sRandomID = GetParam("sRandomID");//页面导入数据标识，Guid
string[] strArray = { "第一网格", "第二网格", "第三网格", "第四网格", "第五网格", "第六网格", "第七网格", "第八网格", "第九网格",
"第十网格", "第十一网格", "第十二网格", "第十三网格", "第十四网格", "第十五网格", "第十六网格", "第十七网格",
"第十八网格", "第十九网格", "第二十网格","第二十一网格","第二十二网格","第二十三网格","第二十四网格",
"第二十五网格","第二十六网格","第二十七网格","第二十八网格","第二十九网格","第三十网格"};
List<string> wgList = new List<string>();
wgList.AddRange(strArray);
string strCheck = "SELECT * FROM _SQ_Excel_BaseInfo WHERE [地区编码] = '" + sVillageCode + "' order by [序号]";
DataTable dtCheck = SqlHelper.ExecuteSqlToTable(strCheck);
string rowNum;//序号
string rowVillageName;//社区名称
int intNum = 0;//是否数字的转换out参数
for (int i = 0; i < dtCheck.Rows.Count; i++)//遍历Table行
{
rowNum = dtCheck.Rows[i]["序号"].ToString().Trim();
rowVillageName = dtCheck.Rows[i]["社区名称"].ToString().Trim();//dtCheck.Rows[i][1].ToString().Trim();
#region 不能为空判断，数据合法性验证
if (rowNum == "")//行号
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：“序号”数据列不能为空！</br>";
}
else if (!int.TryParse(rowNum, out intNum))
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：“序号”数据列必须为正整数！序号：【" + rowNum + "】</br>";
}
if (dtCheck.Rows[i]["社区名称"].ToString().Trim() == "")//社区名称
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：“社区名称”数据列不能为空！</br>";
}
if (dtCheck.Rows[i]["网格名称"].ToString().Trim() == "")//网格名称
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：“网格名称”数据列不能为空！</br>";
}
else//网格名称不为空，必须为一定的格式【第一网格】
{
string wgName = dtCheck.Rows[i]["网格名称"].ToString().Trim();
bool result = wgList.Contains(wgName);
if (!result)
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：“网格名称”数据列必须按规定的格式填写！例如：第一网格、第十二网格！</br>";
}
}
if (dtCheck.Rows[i]["小区名称"].ToString().Trim() == "")//小区名称
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：“小区名称”数据列不能为空！</br>";
}
if (dtCheck.Rows[i]["楼栋名称"].ToString().Trim() == "")//楼栋名称
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：“楼栋名称”数据列不能为空！</br>";
}
if (dtCheck.Rows[i]["总层数"].ToString().Trim() == "")//总层数
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：“总层数”数据列不能为空！</br>";
}
else
{
if (!int.TryParse(dtCheck.Rows[i]["总层数"].ToString().Trim(), out intNum))
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：“总层数”数据列必须为正整数！总层数：【" + dtCheck.Rows[i]["总层数"].ToString().Trim() + "】</br>";
}
else if (int.Parse(dtCheck.Rows[i]["总层数"].ToString().Trim()) == 0)
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：“总层数”数据列不能为0！</br>";
}
}
if (dtCheck.Rows[i]["负层数"].ToString().Trim() != "")//负层数
{
if (!int.TryParse(dtCheck.Rows[i]["负层数"].ToString().Trim(), out intNum))
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：“负层数”数据列必须为正整数！负层数：【" + dtCheck.Rows[i]["负层数"].ToString().Trim() + "】</br>";
}
}
if (dtCheck.Rows[i]["单元数"].ToString().Trim() == "")//单元数
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：“单元数”数据列不能为空！</br>";
}
else
{
if (!int.TryParse(dtCheck.Rows[i]["单元数"].ToString().Trim(), out intNum))
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：“单元数”数据列必须为正整数！单元数：【" + dtCheck.Rows[i]["单元数"].ToString().Trim() + "】</br>";
}
else if (int.Parse(dtCheck.Rows[i]["单元数"].ToString().Trim()) == 0)
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：“单元数”数据列不能为0！</br>";
}
}
if (dtCheck.Rows[i]["每单元每层户数"].ToString().Trim() == "")//每单元每层户数
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：“每单元每层户数”数据列不能为空！</br>";
}
else
{
if (!int.TryParse(dtCheck.Rows[i]["每单元每层户数"].ToString().Trim(), out intNum))
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：“每单元每层户数”数据列必须为正整数！每单元每层户数：【" + dtCheck.Rows[i]["每单元每层户数"].ToString().Trim() + "】</br>";
}
else if (int.Parse(dtCheck.Rows[i]["每单元每层户数"].ToString().Trim()) == 0)
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：“每单元每层户数”数据列不能为0！</br>";
}
}
#endregion
if (rowVillageName != sVillageName)//判断社区名称是否与导入地区相同
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：Excel里的“社区名称”为【" + rowVillageName + "】，与所选的导入地区名称【" + sVillageName + "】不符！</br>";
}
}
///检验同一个小区下是否有二个相同的楼栋名称
string strBuildCheck = "SELECT [小区名称],[楼栋名称] FROM _SQ_Excel_BaseInfo WHERE [地区编码] = '" + sVillageCode + "' group by 小区名称,楼栋名称 having COUNT(1)>1";
DataTable dtBuildCheck = SqlHelper.ExecuteSqlToTable(strBuildCheck);
if (dtBuildCheck.Rows.Count > 0)
{
bCheckResult = false;
for (int i = 0; i < dtBuildCheck.Rows.Count; i++)
{
string strRepeatBuild = "SELECT * FROM _SQ_Excel_BaseInfo WHERE [地区编码] = '" + sVillageCode + "' AND 小区名称='" + dtBuildCheck.Rows[i]["小区名称"] + "' AND 楼栋名称='" + dtBuildCheck.Rows[i]["楼栋名称"] + "'";
DataTable dtRepeatBuild = SqlHelper.ExecuteSqlToTable(strRepeatBuild);
for (int j = 0; j < dtRepeatBuild.Rows.Count; j++)
{
rowNum = dtRepeatBuild.Rows[j]["序号"].ToString().Trim();
mes += "序号【" + rowNum + "】，错误说明：同一小区下楼栋重复！小区名称：【" + dtRepeatBuild.Rows[j]["小区名称"] + "】---楼栋名称：【" + dtRepeatBuild.Rows[j]["楼栋名称"] + "】<br/>";
}
}
}
///检验：如果维护了单元户数，维护的数组长度是否与单元数一一对应
string strCSCheck = "select 序号,小区名称,楼栋名称,总层数,负层数,单元数,每单元每层户数,单元户数 from _SQ_Excel_BaseInfo WHERE [地区编码] = '" + sVillageCode + "'";
DataTable dtCSCheck = SqlHelper.ExecuteSqlToTable(strCSCheck);
for (int i = 0; i < dtCSCheck.Rows.Count; i++)
{
rowNum = dtCSCheck.Rows[i]["序号"].ToString().Trim();//序号
if (ToInt(dtCSCheck.Rows[i]["负层数"].ToString()) > ToInt(dtCSCheck.Rows[i]["总层数"].ToString()))
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：负层数不能大于总层数！负层数：【" + dtCSCheck.Rows[i]["负层数"] + "】---总层数：【" + dtCSCheck.Rows[i]["总层数"] + "】<br/>";
}
if (dtCSCheck.Rows[i]["单元户数"].ToString() != "" && dtCSCheck.Rows[i]["单元户数"] != null)
{
string[] dyhs = dtCSCheck.Rows[i]["单元户数"].ToString().Replace("，", ",").TrimEnd(',').Split(',');
if (dyhs.Length != ToInt(dtCSCheck.Rows[i]["单元数"].ToString()))
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：单元户数维护的数量需要同单元数一致！单元数：【" + dtCSCheck.Rows[i]["单元数"] + "】---单元户数：【" + dtCSCheck.Rows[i]["单元户数"] + "】<br/>";
}
}
}
if (mes == "")
{
mes = "数据校验成功！";
}
OutputResult(bCheckResult, mes);
}
#endregion
#region 楼层验证
private void ValidateBuild()
{
string sVillageCode = GetParam("sVillageCode");//地区控件选择的导入数据的地区---编码
string sVillageName = GetParam("sVillageName");//地区控件选择的导入数据的地区---名称
bool result = true;
string mes = "";
string strBuildSQL = "select 序号,楼栋主键,小区编码,小区名称,楼栋编码,楼栋名称,总层数,负层数,单元数,单元户数,每单元每层户数,不生成的门牌号,商品房,车库 from _SQ_Excel_BaseInfo where 地区编码 ='" + sVillageCode + "' and 总层数>33 order by 楼栋编码";
DataTable dtBuild = SqlHelper.ExecuteSqlToTable(strBuildSQL);
for (int i = 0; i < dtBuild.Rows.Count; i++)
{
result = false;
string rowNum = dtBuild.Rows[i]["序号"].ToString().Trim();
if (i > 1)
{
mes += " ";
}
mes += "序号【" + rowNum + "】，总层数为【" + dtBuild.Rows[i]["总层数"].ToString().Trim() + "】</br>";
}
if (mes != "")
{
mes += " ";
mes += "是否确定生成基础信息？";
}
OutputResult(result, mes);
}
#endregion
#region 一键导入网格小区楼栋
/// <summary>
/// 一键导入网格小区楼栋
/// </summary>
private void IntelligentImPort()
{
bool bInitResult = true;
bool bWGUpdate = true;
bool bHomeBuildUpdate = true;
string mes = "基础数据生成成功！";
string sFormat = GetParam("sFormat");//门牌号格式
string sVillageCode = GetParam("sVillageCode");//社区编码
string sVillageName = GetParam("sVillageName");//地区控件选择的导入数据的地区---名称
//string sRandomID = GetParam("sRandomID");//页面导入数据标识，Guid
bWGUpdate = UpdateWGCode(sVillageCode);
bHomeBuildUpdate = UpdateHomeBuildCode(sVillageCode);
List<string> sqlList = new List<string>();//执行语句
List<SqlParameter[]> paraList = new List<SqlParameter[]>();//执行语句相对应的参数
#region 生成导入网格信息SQL
string strWGSQL = "select distinct 地区编码,地区名称,网格编码,网格名称 from _SQ_Excel_BaseInfo where 地区编码='" + sVillageCode + "' order by 网格编码";
DataTable dtWG = SqlHelper.ExecuteSqlToTable(strWGSQL);
string strInsertSQL = "insert into SQ_WGInfo(gID,sVillageCode,sCode,sName,iDeleteMark,iSort,dCreateDate,sCreateUserRealname) values(NEWID(),@sWGVillageCode,@sWGCode,@sWGName,0,0,getdate(),'数据导入')";
int iWGRows = dtWG.Rows.Count;
for (int i = 0; i < iWGRows; i++)
{
string sWGVillageCode = dtWG.Rows[i]["地区编码"].ToString();
string sWGCode = dtWG.Rows[i]["网格编码"].ToString();
string sWGName = dtWG.Rows[i]["网格名称"].ToString();
sqlList.Add(strInsertSQL);
paraList.Add(new SqlParameter[] {
new SqlParameter("@sWGVillageCode", sWGVillageCode),
new SqlParameter("@sWGCode", sWGCode),
new SqlParameter("@sWGName", sWGName)
});
}
#endregion
#region 生成导入小区信息SQL
string strHomeSQL = "select distinct 地区编码,地区名称,小区编码,小区名称 from _SQ_Excel_BaseInfo where 地区编码='" + sVillageCode + "' order by 小区编码";
DataTable dtHome = SqlHelper.ExecuteSqlToTable(strHomeSQL);
string strInsertHomeSQL = "insert into SQ_Homes(gID,sVillageCode,sHomeCode,sHomeName,iDeleteMark,iSort,dCreateDate,sCreateUserRealname) values(NEWID(),@sHomeVillageCode,@sHomeCode,@sHomeName,0,0,getdate(),'数据导入')";
int iHomeRows = dtHome.Rows.Count;
for (int i = 0; i < iHomeRows; i++)
{
string sHomeVillageCode = dtHome.Rows[i]["地区编码"].ToString();
string sHomeCode = dtHome.Rows[i]["小区编码"].ToString();
string sHomeName = dtHome.Rows[i]["小区名称"].ToString();
sqlList.Add(strInsertHomeSQL);
paraList.Add(new SqlParameter[]{
new SqlParameter("@sHomeVillageCode",sHomeVillageCode),
new SqlParameter("@sHomeCode",sHomeCode),
new SqlParameter("@sHomeName",sHomeName),
});
}
#endregion
#region 生成导入楼栋信息SQL
string strBuildSQL = "select 楼栋主键,小区编码,小区名称,楼栋编码,楼栋名称,总层数,负层数,单元数,单元户数,每单元每层户数,不生成的门牌号,商品房,车库 from _SQ_Excel_BaseInfo where 地区编码 ='" + sVillageCode + "' order by 楼栋编码";
DataTable dtBuild = SqlHelper.ExecuteSqlToTable(strBuildSQL);
string strInsertBuildSQL = "insert into SQ_Build(gID,sBuildCode,sBuildName,sHomeCode,iCS,iFCS,iFWS,iDYS,sFWLX,sImage,iDeleteMark,iSort,dCreateDate,sCreateUserRealname,FW_DY,fXpoint,fYpoint,fWGXpoint,fWGYpoint,BdXpoint,BdYpoint,sFormat) ";
strInsertBuildSQL += "values(@sBuildKey,@sBuildCode,@sBuildName,@sBuildHomeCode,@CS,@FCS,@FWS,@DYS,'02',@sImage,0,0,getdate(),'数据导入',@FW_DY,'0','0','0','0','0','0',@sFormat) ";
string strInsertWGLDGLBSQL = "insert into SQ_WGLDGLB(gID,gWGID,sBuildCode,iDeleteMark,iSort,dCreateDate,sCreateUserRealname) values(NEWID(),NEWID(),@sBuildCode,0,0,getdate(),'数据导入')";
int iBuildRows = dtBuild.Rows.Count;
for (int i = 0; i < iBuildRows; i++)
{
string sBuildKey = dtBuild.Rows[i]["楼栋主键"].ToString();//楼栋主键
string sBuildCode = dtBuild.Rows[i]["楼栋编码"].ToString();//楼栋编码
string sBuildName = dtBuild.Rows[i]["楼栋名称"].ToString();//楼栋名称
string sBuildHomeCode = dtBuild.Rows[i]["小区编码"].ToString();//小区编码
string CS = dtBuild.Rows[i]["总层数"].ToString();//总层数
string FCS = dtBuild.Rows[i]["负层数"].ToString();//负层数
string FWS = dtBuild.Rows[i]["每单元每层户数"].ToString();//每单元每层户数
string DYS = dtBuild.Rows[i]["单元数"].ToString();//单元数
string FW_DY = dtBuild.Rows[i]["单元户数"].ToString().Replace("，", ",");//单元户数
//根据层数，单位数，户数自动生成相应的图片
string sImage = GetHomeImage(CS, DYS);//楼栋图片
sqlList.Add(strInsertBuildSQL);
paraList.Add(new SqlParameter[]{
new SqlParameter("@sBuildKey",sBuildKey),
new SqlParameter("@sBuildCode",sBuildCode),
new SqlParameter("@sBuildName",sBuildName),
new SqlParameter("@sBuildHomeCode",sBuildHomeCode),
new SqlParameter("@CS",CS),
new SqlParameter("@FCS",FCS),
new SqlParameter("@FWS",FWS),
new SqlParameter("@DYS",DYS),
new SqlParameter("@sImage",sImage),
new SqlParameter("@FW_DY",FW_DY),
new SqlParameter("@sFormat",sFormat)
});
sqlList.Add(strInsertWGLDGLBSQL);
paraList.Add(new SqlParameter[]{
new SqlParameter("@sBuildCode",sBuildCode)
});
}
string strUpdateWGLDGLBSQL = "update SQ_WGLDGLB set gWGID=(select gID from SQ_WGInfo w join _SQ_Excel_BaseInfo b on w.sCode=b.网格编码 and SQ_WGLDGLB.sBuildCode=b.楼栋编码 ) where sBuildCode like @sVillageCode";
sqlList.Add(strUpdateWGLDGLBSQL);
string paraVillageCode = sVillageCode + "%";
paraList.Add(new SqlParameter[]{
new SqlParameter("@sVillageCode",paraVillageCode)
});
#endregion
#region 更新网格楼栋坐标（排列楼栋）
string strWGInfoSQL = "select gID from SQ_WGInfo where sVillageCode='" + sVillageCode + "' order by sCode";
DataTable dtWGInfo = SqlHelper.ExecuteSqlToTable(strWGInfoSQL);
for (int i = 0; i < dtWGInfo.Rows.Count; i++)
{
string sWgID = dtWGInfo.Rows[i]["gID"].ToString();//网格主键
string strWGBuild = "select B.gID from SQ_Build B left join SQ_WGLDGLB G on B.sBuildCode = G.sBuildCode where gWGID = '" + sWgID + "' order by B.sBuildCode";
DataTable dtWGBuild = SqlHelper.ExecuteSqlToTable(strWGBuild);//网格下的所有楼栋
List<string> listUpdatePoint = new List<string>();//更新坐标语句集合
for (int k = 1; k <= dtWGBuild.Rows.Count; k++)
{
string sBuildID = dtWGBuild.Rows[k - 1]["gID"].ToString();//楼栋主键
int xPoint = 76 + 107 * ((k - 1) % 6);//X轴坐标位置
int yPoint = 64 + 97 * (k / 6);//Y轴坐标位置
string strUpdatePoint = "update SQ_Build set fWGXpoint=@xPoint, fWGYpoint=@yPoint where gID=@sBuildID";
sqlList.Add(strUpdatePoint);
paraList.Add(new SqlParameter[] {
new SqlParameter("@xPoint",xPoint),
new SqlParameter("@yPoint",yPoint),
new SqlParameter("@sBuildID",sBuildID)
});
}
}
#endregion
#region 更新小区楼栋坐标（排列楼栋）
string strHomeInfoSQL = "select gID,sHomeCode from SQ_Homes where sVillageCode='" + sVillageCode + "' order by sHomeCode";
DataTable dtHomeInfo = SqlHelper.ExecuteSqlToTable(strHomeInfoSQL);
for (int i = 0; i < dtHomeInfo.Rows.Count; i++)
{
string sHomeCode = dtHomeInfo.Rows[i]["sHomeCode"].ToString();//网格主键
string strHomeBuild = "select gID from SQ_Build where sHomeCode = '" + sHomeCode + "' order by sBuildCode";
DataTable dtHomeBuild = SqlHelper.ExecuteSqlToTable(strHomeBuild);//网格下的所有楼栋
List<string> listUpdatePoint = new List<string>();//更新坐标语句集合
for (int k = 1; k <= dtHomeBuild.Rows.Count; k++)
{
string sBuildID = dtHomeBuild.Rows[k - 1]["gID"].ToString();//楼栋主键
int xPoint = 76 + 107 * ((k - 1) % 6);//X轴坐标位置
int yPoint = 64 + 97 * (k / 6);//Y轴坐标位置
string strUpdatePoint = "update SQ_Build set fXpoint=@xPoint, fYpoint=@yPoint where gID=@sBuildID";
sqlList.Add(strUpdatePoint);
paraList.Add(new SqlParameter[]{
new SqlParameter("@xPoint",xPoint),
new SqlParameter("@yPoint",yPoint),
new SqlParameter("@sBuildID",sBuildID)
});
}
}
#endregion
#region 生成房屋信息SQL
//string sFormat = GetParam("sFormat");//门牌号格式
string strInsertFWSQL = "insert into SQ_FWInfo(gID,sBuildCode,sMPH,sFWYT,iDeleteMark,iSort,dCreateDate,sCreateUserRealname,MPHCode,i_CS,i_DY,i_NO) " +
"values(NEWID(),@sBuildCode,@sMPH,@lx,0,0,getdate(),'数据导入',@sdoor,@j,@k,@no)";
for (int i = 0; i < iBuildRows; i++)
{
string sBuildKey = dtBuild.Rows[i]["楼栋主键"].ToString();//楼栋主键
string sBuildCode = dtBuild.Rows[i]["楼栋编码"].ToString();//楼栋编码
string sBuildName = dtBuild.Rows[i]["楼栋名称"].ToString();//楼栋名称
string sBuildHomeCode = dtBuild.Rows[i]["小区编码"].ToString();//小区编码
int iCS = ToInt(dtBuild.Rows[i]["总层数"].ToString());//总层数
int iFCS = ToInt(dtBuild.Rows[i]["负层数"].ToString());//负层数
int iZCS = iCS - iFCS;//正层数
int iDYFWS = ToInt(dtBuild.Rows[i]["每单元每层户数"].ToString());//每单元每层户数
int iDYS = ToInt(dtBuild.Rows[i]["单元数"].ToString());//单元数
var iDYHS = dtBuild.Rows[i]["单元户数"].ToString().Trim().Replace("，", ",").Split(',');
var 不生成的门牌号 = dtBuild.Rows[i]["不生成的门牌号"].ToString().Replace("，", ",");
if (!string.IsNullOrEmpty(不生成的门牌号))
{
不生成的门牌号 = "," + 不生成的门牌号 + ",";
}
var 商品房 = dtBuild.Rows[i]["商品房"].ToString().Replace("，", ",");
if (!string.IsNullOrEmpty(商品房))
{
商品房 = "," + 商品房 + ",";
}
var 车库 = dtBuild.Rows[i]["车库"].ToString().Replace("，", ",");
if (!string.IsNullOrEmpty(车库))
{
车库 = "," + 车库 + ",";
}
for (int j = -iFCS; j <= iZCS; j++)//层数
{
if (j == 0)
continue;
bool fc = j < 0;//是否负层
for (int k = 1; k <= iDYS; k++)//单元数
{
int hs = iDYFWS;
if (iDYHS.Length >= k)
{
if (iDYHS[k - 1].Trim() != "")
hs = Convert.ToInt32(iDYHS[k - 1]);
}
if (hs > iDYFWS)
hs = iDYFWS;
for (int m = 1; m <= hs; m++)
{
var no = (k - 1) * iDYFWS + m;
var sdoor = j + no.ToString("D2");
string sMPH = (fc ? "-" : "") + SQ_Common.GetMPH(sFormat, fc ? -j : j, k, iDYFWS, m);
if (不生成的门牌号.Contains("," + sMPH + ","))
continue;
var lx = "01";
if (商品房.Contains("," + sdoor + ","))
lx = "02";
if (车库.Contains("," + sdoor + ","))
lx = "03";
sqlList.Add(strInsertFWSQL);
paraList.Add(new SqlParameter[]{
new SqlParameter("@sBuildCode",sBuildCode),
new SqlParameter("@sMPH",sMPH),
new SqlParameter("@lx",lx),
new SqlParameter("@sdoor",sdoor),
new SqlParameter("@j",j),
new SqlParameter("@k",k),
new SqlParameter("@no",no)
});
}
}
}
}
#endregion
#region 记录数据导入日志
string strInsertLog = "insert into _SQ_DataImportLog(sID,sVillageCode,sVillageName,iType,sUpLoadUserLoginName,sUpLoadUserRealName,dUpLoadDate,sFormat)" +
" values(NEWID(),@sVillageCode,@sVillageName,1,@sUserName,@sRealName,getdate(),@sFormat)";
string sUserName = GetSessionCurrentUserInfo.sUserName;//用户登陆名
string sRealName = GetSessionCurrentUserInfo.sRealName;//用户姓名
sqlList.Add(strInsertLog);
paraList.Add(new SqlParameter[] {
new SqlParameter("@sVillageCode",sVillageCode),
new SqlParameter("@sVillageName",sVillageName),
new SqlParameter("@sUserName",sUserName),
new SqlParameter("@sRealName",sRealName),
new SqlParameter("@sFormat",sFormat)
});
try
{
bInitResult = SqlHelper.ExecuteTrans(sqlList, paraList);
}
catch (Exception)
{
bInitResult = false;
mes = "基础数据生成失败！";
throw;
}
#endregion
OutputResult(bWGUpdate & bHomeBuildUpdate & bInitResult, mes);
}
/// <summary>
/// 更新导入数据网格编码
/// </summary>
private bool UpdateWGCode(string sVillageCode)
{
string strWGSQL = "select distinct 序号,地区编码,地区名称,网格编码,网格名称 from _SQ_Excel_BaseInfo where 地区编码='" + sVillageCode + "' order by 序号";
DataTable dtWG = SqlHelper.ExecuteSqlToTable(strWGSQL);
bool bUpdateWGCode = true;
int rows = dtWG.Rows.Count;
string sWGCode = "";
string sWGName = "";
try
{
int num = 0;//生成网格的序号
for (int i = 0; i < rows; i++)
{
if (i > 0)
{
///由于取了序号，网络信息重复，过滤重复的网格更新
if (dtWG.Rows[i - 1]["网格名称"].ToString() != dtWG.Rows[i]["网格名称"].ToString())
{
sWGCode = dtWG.Rows[i]["地区编码"] + GetCode(num + 1);
sWGName = dtWG.Rows[i]["网格名称"].ToString();
string strUpdateWGCode = "update _SQ_Excel_BaseInfo set 网格编码='" + sWGCode + "' where 网格名称='" + sWGName + "' and 地区编码='" + sVillageCode + "' and (网格编码 ='' or 网格编码 is null)";
bUpdateWGCode = SqlHelper.ExecuteSql(strUpdateWGCode);
num++;
}
}
else
{
sWGCode = dtWG.Rows[i]["地区编码"] + GetCode(num + 1);
sWGName = dtWG.Rows[i]["网格名称"].ToString();
string strUpdateWGCode = "update _SQ_Excel_BaseInfo set 网格编码='" + sWGCode + "' where 网格名称='" + sWGName + "' and 地区编码='" + sVillageCode + "' and (网格编码 ='' or 网格编码 is null)";
bUpdateWGCode = SqlHelper.ExecuteSql(strUpdateWGCode);
num++;
}
}
}
catch (Exception)
{
bUpdateWGCode = false;
throw;
}
return bUpdateWGCode;
}
/// <summary>
/// 更新导入数据小区编码，楼栋编码
/// </summary>
private bool UpdateHomeBuildCode(string sVillageCode)
{
string strHomeSQL = "select distinct 地区编码,地区名称,小区编码,小区名称 from _SQ_Excel_BaseInfo where 地区编码='" + sVillageCode + "'";// order by 小区编码";
DataTable dtHome = SqlHelper.ExecuteSqlToTable(strHomeSQL);
bool bUpdateHomeCode = true;
bool bUpdateBuildCode = true;
int rows = dtHome.Rows.Count;
string sHomeCode = "";
string sHomeName = "";
try
{
for (int i = 0; i < rows; i++)
{
sHomeCode = dtHome.Rows[i]["地区编码"] + GetCode(i + 1);
sHomeName = dtHome.Rows[i]["小区名称"].ToString();
string strUpdateWGCode = "update _SQ_Excel_BaseInfo set 小区编码='" + sHomeCode + "' where 小区名称='" + sHomeName + "' and 地区编码='" + sVillageCode + "'";
bUpdateHomeCode = SqlHelper.ExecuteSql(strUpdateWGCode);
string strBuildSQL = "select distinct 小区编码,小区名称,楼栋编码,楼栋名称 from _SQ_Excel_BaseInfo where 小区编码='" + sHomeCode + "' order by 楼栋编码";
DataTable dtBuild = SqlHelper.ExecuteSqlToTable(strBuildSQL);
int iBuildRows = dtBuild.Rows.Count;
string sBuildCode = "";
string sBuildName = "";
try
{
for (int j = 0; j < iBuildRows; j++)
{
sBuildCode = dtBuild.Rows[j]["小区编码"] + GetCode(j + 1);
sBuildName = dtBuild.Rows[j]["楼栋名称"].ToString();
string strUpdateHomeCode = "update _SQ_Excel_BaseInfo set 楼栋编码='" + sBuildCode + "' where 楼栋名称='" + sBuildName + "' and 小区编码='" + sHomeCode + "'";
bUpdateBuildCode = SqlHelper.ExecuteSql(strUpdateHomeCode);
}
}
catch (Exception)
{
bUpdateBuildCode = false;
throw;
}
}
}
catch (Exception)
{
bUpdateHomeCode = false;
throw;
}
return bUpdateHomeCode && bUpdateBuildCode;
}
#endregion
#region
/// <summary>
/// 一键生成网格用户信息
/// </summary>
private void IntelligentWGUser()
{
bool bInitResult = true;
string mes = "网格用户信息生成成功！";
string sVillageCode = GetParam("sVillageCode");//社区编码
string sVillageName = GetParam("sVillageName");//地区控件选择的导入数据的地区---名称
List<string> sqlList = new List<string>();//执行语句
List<SqlParameter[]> paraList = new List<SqlParameter[]>();//执行语句相对应的参数
#region 生成用户信息
string gCreateUserId = GetSessionCurrentUserInfo.gUserID.ToString();//用户主键
string sCreateUserRealname = GetSessionCurrentUserInfo.sRealName + "|批量生成";//用户姓名
string strInsertUser = "insert into sys_Users(gUserID,iType,gOrganizeID,sUserName,sUserNameLower,sRealName,sPwd,sPwdFormat,bIsLockedOut,iDeleteMark,dCreateDate,gCreateUserId,sCreateUserRealname,bIsManagerLockedout,iAudit) " +
" values(@gUserID,2,@gOrganizeID,@sUserName,@sUserNameLower,@sRealName,@sPwd,@sPwdFormat,0,0,getdate(),@gCreateUserId,@sCreateUserRealname,0,2)";
string strInsertUserInRole = "insert into sys_UsersInRoles(gRoleID,gUserID,iDeleteMark,iSort,dCreateDate,gCreateUserId,sCreateUserRealname)" +
" values(@gRoleID,@gUserID,0,0,getdate(),@gCreateUserId,@sCreateUserRealname)";
string strInsertWGUser = "insert into SQ_WGUser(gID,gWGID,gUserID,iDeleteMark,iSort,dCreateDate,gCreateUserId,sCreateUserRealname)" +
" values(NEWID(),@gWGID,@gUserID,0,0,getdate(),@gCreateUserId,@sCreateUserRealname)";
string strWGSQL = "select distinct 地区编码,地区名称,网格编码,网格名称,w.gID from _SQ_Excel_BaseInfo b join dbo.SQ_WGInfo w on b.网格编码=w.scode where 地区编码='" + sVillageCode + "' order by 网格编码";
DataTable dtWG = SqlHelper.ExecuteSqlToTable(strWGSQL);
OrganizeBLL bll = new OrganizeBLL();
sys_Organize OrgModel = bll.GetModeByAreaCode(sVillageCode);//当级地区的上级地区的机构实体
if (OrgModel != null)
{
string gOrganizeID = OrgModel.gID.ToString();
int iWGRows = dtWG.Rows.Count;
List<string> userNameList = userBll.GetUserName(sVillageCode, iWGRows);
for (int i = 0; i < iWGRows; i++)
{
string gUserID = Guid.NewGuid().ToString();//用户主键
string gWGID = dtWG.Rows[i]["gID"].ToString();//网格主键
string sWGCode = dtWG.Rows[i]["网格编码"].ToString();//网格编码
string sWGName = dtWG.Rows[i]["网格名称"].ToString();//网格名称
sqlList.Add(strInsertUser);
paraList.Add(new SqlParameter[] {
new SqlParameter("@gUserID",gUserID),//用户主键
new SqlParameter("@gOrganizeID",gOrganizeID),//机构主键
new SqlParameter("@sUserName",userNameList[i]),//登录用户名
new SqlParameter("@sUserNameLower",userNameList[i].ToLower()),//登录用户名(小写)
new SqlParameter("@sRealName",sWGName),//真实姓名
new SqlParameter("@sPwd","202cb962ac59075b964b07152d234b70"),//加密后的：123
new SqlParameter("@sPwdFormat","0"),
new SqlParameter("@gCreateUserId",gCreateUserId),
new SqlParameter("@sCreateUserRealname",sCreateUserRealname)
});
sqlList.Add(strInsertUserInRole);
paraList.Add(new SqlParameter[]{
new SqlParameter("@gRoleID","4CFD5CB4-D04B-46C0-8738-C8119D2A9FBB"),
new SqlParameter("@gUserID",gUserID),
new SqlParameter("@gCreateUserId",gCreateUserId),
new SqlParameter("@sCreateUserRealname",sCreateUserRealname)
});
sqlList.Add(strInsertWGUser);
paraList.Add(new SqlParameter[]{
new SqlParameter("@gWGID",gWGID),
new SqlParameter("@gUserID",gUserID),
new SqlParameter("@gCreateUserId",gCreateUserId),
new SqlParameter("@sCreateUserRealname",sCreateUserRealname),
});
}
}
else
{
OutputResult(bInitResult, mes);
return;
}
#endregion
#region 记录数据导入日志
string strInsertLog = "insert into _SQ_DataImportLog(sID,sVillageCode,sVillageName,iType,sUpLoadUserLoginName,sUpLoadUserRealName,dUpLoadDate)" +
" values(NEWID(),@sVillageCode,@sVillageName,3,@sUserName,@sRealName,getdate())";
string sUserName = GetSessionCurrentUserInfo.sUserName;//用户登陆名
string sRealName = GetSessionCurrentUserInfo.sRealName;//用户姓名
sqlList.Add(strInsertLog);
paraList.Add(new SqlParameter[] {
new SqlParameter("@sVillageCode",sVillageCode),
new SqlParameter("@sVillageName",sVillageName),
new SqlParameter("@sUserName",sUserName),
new SqlParameter("@sRealName",sRealName),
});
try
{
bInitResult = SqlHelper.ExecuteTrans(sqlList, paraList);
}
catch (Exception)
{
bInitResult = false;
mes = "网格用户信息生成失败！";
OutputResult(bInitResult, mes);
throw;
}
#endregion
OutputResult(bInitResult, mes);
}
#endregion
#region 非功能性辅助方法
/// <summary>
/// 获取后三位编码
/// </summary>
/// <param name="i"></param>
/// <returns></returns>
private string GetCode(int i)
{
if (i < 10)
return "00" + i;
else if (i < 100)
return "0" + i;
else
return i.ToString();
}
/// <summary>
/// 根据层数，单位数，户数自动生成相应的图片
/// </summary>
/// <param name="CS">层数</param>
/// <param name="DYS">单位数</param>
/// <param name="FWS">单元户数</param>
/// <returns></returns>
public string GetHomeImage(string CS, string DYS)
{
int f = ToInt(CS);
int g = ToInt(DYS);
//int h = ToInt(FWS);
if (f == 1 && g == 1)//&& h == 1)
return "别墅_01.png";
if (f > 1 && f <= 7)
return "多层_06.png";
if (f > 7)
return "小高层_02.png";
return "民房_011.png";
}
/// <summary>
/// 转换字符串类型为整型
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
public int ToInt(string str)
{
int _outint = 0;
int.TryParse(str, out _outint);
return _outint;
}
/// <summary>
/// 检验Excel列数据项与模板数据项是否符合
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public bool CheckRule(DataTable dt, out string mes, string sVillageName)
{
string[] strArray = { "序号", "社区名称", "网格名称", "小区名称", "楼栋名称", "总层数", "负层数", "单元数", "每单元每层户数", "单元户数", "不生成的门牌号", "商品房", "车库", "备注" };
List<string> list = new List<string>();
list.AddRange(strArray);//模板字段列集合
mes = "";
bool bResult = true;
List<string> excelList = new List<string>();
DataRow dRow = dt.Rows[0];
bool IsTitle = true;//Excel是否有标题行，默认有
string columnName = dRow[0].ToString();
if (columnName != "序号")
{
IsTitle = false;
}
for (int i = 0; i < dt.Columns.Count; i++)
{
columnName = IsTitle == true ? dRow[i].ToString() : dt.Columns[i].ColumnName;
if (!columnName.StartsWith("F"))
{
excelList.Add(columnName);//Excel字段列集合
}
}
if (excelList.Count != 14)
{
bResult = false;
mes = "数据校验失败！<br/>《社区网格信息采集模版》中数据列必须为14列！<br/>";
var listMore = excelList.Except(list);//多出的数据列
foreach (var item in listMore)
{
mes += "《社区网格信息采集模版》中多出“" + item + "”数据列！请删除！<br/>";
}
var listLess = list.Except(excelList);//少的数据列
foreach (var item in listLess)
{
mes += "《社区网格信息采集模版》中缺少“" + item + "”数据列！<br/>";
}
}
else
{
for (int i = 0; i < strArray.Length; i++)
{
if (excelList[i] != strArray[i])
{
bResult = false;
break;
}
}
if (!bResult)
{
mes += "《社区网格信息采集模版》中数据列名称和顺序必须与下列信息一致：</br>";
mes += "【" + strArray[0] + "】|【" + strArray[1] + "】|【" + strArray[2] + "】|【" + strArray[3] + "】|【" + strArray[4] + "】|【" + strArray[5] + "】|【" + strArray[6] + "】|【" + strArray[7] + "】|【" + strArray[8] + "】|【" + strArray[9] + "】|【" + strArray[10] + "】|【" + strArray[11] + "】|【" + strArray[12] + "】|【" + strArray[13] + "】</br>";
}
}
return bResult;
}
/// <summary>
/// 去除Excel中的空白行
/// </summary>
/// <param name="dt"></param>
protected void removeEmpty(DataTable dt)
{
List<DataRow> removelist = new List<DataRow>();
for (int i = 0; i < dt.Rows.Count; i++)
{
bool rowdataisnull = true;
for (int j = 0; j < dt.Columns.Count; j++)
{
if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
{
rowdataisnull = false;
}
}
if (rowdataisnull)
{
removelist.Add(dt.Rows[i]);
}
}
for (int i = 0; i < removelist.Count; i++)
{
dt.Rows.Remove(removelist[i]);
}
}
#endregion
}
}PeopleInfoListHandler
using HMFW.SQLServerDAL;
using HMFW.Web.App_Code;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
namespace HMFW.Web.Pages.DataImport
{
/// <summary>
/// PeopleInfoListHandler 的摘要说明
/// </summary>
public class PeopleInfoListHandler : BaseHandler
{
protected override void ActionHandler(string action)
{
switch (action.ToLower())
{
case "fileupload"://上传社区导入数据
UploadFile();
break;
case "inexport":
ReadExcel();//导入Excel数据表临时存储表
break;
case "datavalidate"://数据校验
DataValidate();
break;
case "intelligentimport"://一键导入家庭人口信息
IntelligentImPort();
break;
default:
break;
}
}
#region 文件上传
/// <summary>
/// 文件上传
/// </summary>
private void UploadFile()
{
string pathPrefix = "/upload/DataImport/PersonInfo/";
try
{
HttpPostedFile file = _context.Request.Files["Filedata"];
if (file == null || file.ContentLength == 0)
throw new ArgumentOutOfRangeException("没有文件需要上传或文件过小！");
if (file.ContentLength > 10000000)
throw new ArgumentOutOfRangeException(string.Format("所上传的文件大小{0}字节超过指定大小{1}字节！", file.ContentLength, 10000000));
string[] strings = file.FileName.Split('.');
string path = Path.Combine(pathPrefix,
DateTime.Now.ToString("yyyyMMddHHmmss") + DateTime.Now.Millisecond + "." +
strings[strings.Length - 1]);
string sbst;
try
{
sbst = HttpContext.Current.Server.MapPath(path);
}
catch
{
sbst = path;
}
FileInfo fileInfo = new FileInfo(sbst);
if (!fileInfo.Directory.Exists)
fileInfo.Directory.Create();
file.SaveAs(sbst);
_context.Response.StatusCode = 200;
_context.Response.Write(path);
}
catch
{
// If any kind of error occurs return a 500 Internal Server error
_context.Response.StatusCode = 500;
_context.Response.Write("An error occured");
_context.Response.End();
}
finally
{
_context.Response.End();
}
}
#endregion
#region Excel数据导入数据库
/// <summary>
/// Excel数据导入数据库
/// </summary>
private void ReadExcel()
{
string sVillageCode = GetParam("sVillageCode");
string sVillageName = GetParam("sVillageName");
string sFileName = GetParam("filePath");//Excel路径
string sRandomID = GetParam("sRandomID");//页面导入数据标识，Guid
string connStr = "";
string fileType = System.IO.Path.GetExtension(sFileName);
if (string.IsNullOrEmpty(fileType))
{
Output("请选择需要导入的文件！");
}
sFileName = _context.Server.MapPath(sFileName);
if (fileType == ".xls")
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + sFileName + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
else
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + sFileName + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
string sql_F = "Select * FROM [{0}]";
DataTable dtResult = new DataTable();
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataTable dtSheetName = null;
bool resul = true;
string Mes = "上传成功！";
try
{
// 初始化连接，并打开
conn = new OleDbConnection(connStr);
conn.Open();
// 获取数据源的表定义元数据
string SheetName = "";
dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
// 初始化适配器
da = new OleDbDataAdapter();
//SheetName = (string)dtSheetName.Rows[0]["TABLE_NAME"];
for (int i = 0; i < dtSheetName.Rows.Count; i++)
{
SheetName += dtSheetName.Rows[i]["TABLE_NAME"].ToString();
}
if (SheetName.Contains("社区人口信息$"))
{
da.SelectCommand = new OleDbCommand(String.Format(sql_F, "社区人口信息$"), conn);
da.Fill(dtResult);
for (int i = 0; i < 13; i++)
{
dtResult.Rows.RemoveAt(dtResult.Rows.Count - 1);
}
string errMes = "";
if (!CheckRule(dtResult, out errMes, sVillageName))
{
resul = false;
Mes = errMes; //"Excel列数据项与模板数据项不符合，请检验！";
}
else
{
string strDelSQL = "delete from _SQ_Excel_PeopleInfo where sRandomID='" + sRandomID + "'";
SqlHelper.ExecuteSql(strDelSQL);
string strSQL = "";
string sHouseholdIDCard = "";//户主身份证号
string sFamilyID = "";//家庭表主键
string sFamilyNo = "";//家庭编码
string sMPH = "";//门牌号
string sJTGX = "";//家庭关系
foreach (DataRow r in dtResult.Rows)
{
if (string.IsNullOrEmpty(r[0].ToString().Trim()) || r[0].ToString() != "序号")
{
sJTGX = r[8].ToString().Trim();
if (sJTGX == "本人")
{
sHouseholdIDCard = r[9].ToString();//户主身份证号
sFamilyID = Guid.NewGuid().ToString().ToUpper();//家庭表主键
sFamilyNo = Guid.NewGuid().ToString().ToUpper();//家庭编码
sMPH = r[5].ToString();//户主门牌号
}
else if (sJTGX == "孙子" || sJTGX == "孙女" || sJTGX == "外孙子" || sJTGX == "外孙女")
{
sJTGX = "孙子、孙女或外孙子、外孙女";
}
else if (sJTGX == "祖父母" || sJTGX == "外祖父母")
{
sJTGX = "孙子、孙女或外孙子、外孙女";
}
if (r[5].ToString() == "")
{
r[5] = sMPH;
}
strSQL = "insert into _SQ_Excel_PeopleInfo(人员主键,地区名称,地区编码,序号,社区名称,网格名称,小区名称,楼栋名称,门牌号,姓名,性别,与户主关系,身份证号,家庭类别,人员性质,民族,户口性质,联系电话,户籍地址,户主身份证号,家庭主键,家庭编码,sRandomID)values(NEWID(),'" +
sVillageName + "','" + sVillageCode + "','" + r[0] + "','" + r[1] + "','" + r[2] + "','" + r[3] + "','" + r[4] + "','" + r[5] + "','" + r[6] + "','" + r[7] + "','" + sJTGX + "','" + r[9] + "','" + r[10] + "','" + r[11] + "','" + r[12] + "','" + r[13] + "','" + r[14] + "','" + r[15] + "','" + sHouseholdIDCard + "','" + sFamilyID + "','" + sFamilyNo + "','" + sRandomID + "')";
SqlHelper.ExecuteSql(strSQL);
}
}
string strUpdateAreaSQL = "update _SQ_Excel_PeopleInfo set 省编码=sShengCode,市编码=sShiCode, 县编码=sAreaCode, 乡镇编码=sTownCode from sysArea a where 地区编码=a.sID and 地区编码='" + sVillageCode + "' and sRandomID='" + sRandomID + "'";
string strUpdateCodeSQL = "update _SQ_Excel_PeopleInfo set 网格编码=a.sWGCode,小区编码=a.sHomeCode,楼栋编码=a.sBuildCode from VIEW_SQ_WGHomeBuild a where _SQ_Excel_PeopleInfo.楼栋名称= a.sBuildName and _SQ_Excel_PeopleInfo.小区名称=a.sHomeName and _SQ_Excel_PeopleInfo.网格名称=a.sWGName and _SQ_Excel_PeopleInfo.社区名称=a.sName and a.sID='" + sVillageCode + "' and _SQ_Excel_PeopleInfo.地区编码='" + sVillageCode + "' and _SQ_Excel_PeopleInfo.sRandomID='" + sRandomID + "'";
resul = SqlHelper.ExecuteSql(strUpdateAreaSQL + strUpdateCodeSQL);
}
}
else
{
resul = false;
Mes = "导入失败！Excel中不包含名称为“实有人口信息”的工作薄！";
}
}
catch (Exception ex)
{
resul = false;
Mes = "导入失败！";
}
finally
{
// 关闭连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
da.Dispose();
conn.Dispose();
}
}
OutputResult(resul, Mes);
}
/// <summary>
/// 检验Excel列数据项与模板数据项是否符合
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public bool CheckRule(DataTable dt, out string mes, string sVillageName)
{
mes = "";
bool bResult = true;
if (dt.Columns.Count != 16)
{
mes = "Excel模板必须为16列";
bResult = false;
return bResult;
}
int dtRows = dt.Rows.Count;
DataRow dr = dt.Rows[0];
string c1 = dr[0].ToString();
string c2 = dr[1].ToString();
string c3 = dr[2].ToString();
string c4 = dr[3].ToString();
string c5 = dr[4].ToString();
string c6 = dr[5].ToString();
string c7 = dr[6].ToString();
string c8 = dr[7].ToString();
string c9 = dr[8].ToString();
string c10 = dr[9].ToString();
string c11 = dr[10].ToString();
string c12 = dr[11].ToString();
string c13 = dr[12].ToString();
string c14 = dr[13].ToString();
string c15 = dr[14].ToString();
string c16 = dr[15].ToString();
if (!(c1 == "序号" && c2 == "社区名称" && c3 == "网格名称" && c4 == "小区名称" && c5 == "楼栋名称" && c6 == "门牌号" && c7 == "姓名" && c8 == "性别" && c9 == "与户主关系" && c10 == "身份证号" && c11 == "家庭类别" && c12 == "人员性质" && c13 == "民族" && c14 == "户口性质" && c15 == "联系电话" && c16 == "户籍地址"))
{
mes += "Excel模板第一行的标题行必须为如下顺序的标题：</br>";
mes += "【序号】|【社区名称】|【网格名称】|【小区名称】|【楼栋名称】|【门牌号】|【姓名】|【性别】|【与户主关系】|【身份证号】|【家庭类别】|【人员性质】|【民族】|【户口性质】|【联系电话】|【户籍地址】</br>";
bResult = false;
}
else
{
//List<string> villageNameList = new List<string>();//社区名称
//villageNameList.Add(sVillageName);
//for (int i = 0; i < dtRows; i++)
//{
// string rowVillageName = dt.Rows[i]["社区名称"].ToString().Trim();
// villageNameList.Add(rowVillageName);
//}
//villageNameList = villageNameList.Distinct().ToList();//去除重复地区
//if (villageNameList.Count == 1 && villageNameList[0] != sVillageName)
//{
// mes += "Excel里的地区与地区控件所选地区不一致。请检验！控件地区为：" + sVillageName + "------Excel里的地区为：" + villageNameList[0];
// bResult = false;
//}
//else if (villageNameList.Count > 1)
//{
// string sVillages = "";//Excel里面包含的所有社区名称
// foreach (string item in villageNameList)
// {
// sVillages += item + "，";
// }
// sVillages.EndsWith("，");
// mes += "Excel里的数据不属于同一个地区。请检验！控件地区为：" + sVillageName + "------Excel里的地区为：" + sVillages;
// bResult = false;
//}
}
return bResult;
}
#endregion
#region 数据校验
/// <summary>
/// 数据校验
/// </summary>
private void DataValidate()
{
bool bCheckResult = true;
string mes = "";
string sVillageCode = GetParam("sVillageCode");//地区控件选择的导入数据的地区---编码
string sVillageName = GetParam("sVillageName");//地区控件选择的导入数据的地区---名称
string sRandomID = GetParam("sRandomID");//页面导入数据标识，Guid
string rowNum;//序号
string strRequireValue = "select * from dbo._SQ_Excel_PeopleInfo where sRandomID = '" + sRandomID + "' and ( 序号='' OR 社区名称='' OR 网格名称='' OR 小区名称='' OR 楼栋名称='' OR 姓名='' OR 性别='' OR 与户主关系='' OR 身份证号='' OR 家庭类别='' OR 人员性质='' OR 民族='' OR 户口性质='' )";
DataTable dtRequireValue = SqlHelper.ExecuteSqlToTable(strRequireValue);
for (int i = 0; i < dtRequireValue.Rows.Count; i++)
{
rowNum = dtRequireValue.Rows[i]["序号"].ToString().Trim();
if (rowNum == "")//行号
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：序号不能为空！</br>";
}
if (dtRequireValue.Rows[i]["社区名称"].ToString().Trim() == "")//社区名称
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：社区名称不能为空！</br>";
}
if (dtRequireValue.Rows[i]["网格名称"].ToString().Trim() == "")//网格名称
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：网格名称不能为空！</br>";
}
if (dtRequireValue.Rows[i]["小区名称"].ToString().Trim() == "")//小区名称
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：小区名称不能为空！</br>";
}
if (dtRequireValue.Rows[i]["楼栋名称"].ToString().Trim() == "")//楼栋名称
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：楼栋名称不能为空！</br>";
}
if (dtRequireValue.Rows[i]["姓名"].ToString().Trim() == "")//姓名
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：姓名不能为空！</br>";
}
if (dtRequireValue.Rows[i]["性别"].ToString().Trim() == "")//性别
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：性别不能为空！</br>";
}
if (dtRequireValue.Rows[i]["与户主关系"].ToString().Trim() == "")//与户主关系
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：与户主关系不能为空！</br>";
}
if (dtRequireValue.Rows[i]["身份证号"].ToString().Trim() == "")//身份证号
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：身份证号不能为空！</br>";
}
if (dtRequireValue.Rows[i]["家庭类别"].ToString().Trim() == "")//家庭类别
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：家庭类别不能为空！</br>";
}
if (dtRequireValue.Rows[i]["人员性质"].ToString().Trim() == "")//人员性质
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：人员性质不能为空！</br>";
}
if (dtRequireValue.Rows[i]["民族"].ToString().Trim() == "")//民族
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：民族不能为空！</br>";
}
if (dtRequireValue.Rows[i]["户口性质"].ToString().Trim() == "")//户口性质
{
bCheckResult = false;
mes += "序号【" + rowNum + "】，错误说明：户口性质不能为空！</br>";
}
}
string strMPHValue = "select * from dbo._SQ_Excel_PeopleInfo where sRandomID = '" + sRandomID + "' and 门牌号 = '' and 与户主关系 ='本人' " + " and sRandomID='" + sRandomID + "' order by 序号";
DataTable dtMPHValue = SqlHelper.ExecuteSqlToTable(strMPHValue);
for (int i = 0; i < dtMPHValue.Rows.Count; i++)
{
bCheckResult = false;
rowNum = dtMPHValue.Rows[i]["序号"].ToString().Trim();
mes += "序号【" + rowNum + "】，错误说明：与户主关系为【本人】的门牌号不能为空！</br>";
}
string strVillageName = "select 序号,社区名称 from dbo._SQ_Excel_PeopleInfo where 社区名称 !='" + sVillageName + "' and 社区名称 !='' " + " and sRandomID='" + sRandomID + "' order by 序号"; ;
DataTable dtVillageName = SqlHelper.ExecuteSqlToTable(strVillageName);
for (int i = 0; i < dtVillageName.Rows.Count; i++)
{
bCheckResult = false;
rowNum = dtVillageName.Rows[i]["序号"].ToString().Trim();
mes += "序号【" + rowNum + "】，错误说明：Excel里的数据不属于同一个地区！控件地区为【" + sVillageName + "】---Excel里的地区为【" + dtVillageName.Rows[i]["社区名称"].ToString().Trim() + "】</br>";
}
string strWGCheck = "select 序号,网格名称 from dbo._SQ_Excel_PeopleInfo where (网格名称 not in (select distinct 网格名称 from SQ_WGInfo where sVillageCode='" + sVillageCode + "')) AND sRandomID='" + sRandomID + "' order by 序号";
DataTable dtWG = SqlHelper.ExecuteSqlToTable(strWGCheck);
for (int i = 0; i < dtWG.Rows.Count; i++)
{
bCheckResult = false;
rowNum = dtWG.Rows[i]["序号"].ToString().Trim();
mes += "序号【" + rowNum + "】，错误说明：人口表里有网格不在基础表之内。网格名称：【" + dtWG.Rows[i]["网格名称"].ToString() + "】<br/>";
}
string strHomeBuildCheck = "select 序号,网格名称,小区名称,楼栋名称,网格名称+小区名称+楼栋名称 from dbo._SQ_Excel_PeopleInfo where (网格名称+小区名称+楼栋名称 not in (select sWGName+sHomeName+sBuildName from dbo.VIEW_SQ_WGHomeBuild where sID='" + sVillageCode + "')) AND 地区编码 = '" + sVillageCode + "' and sRandomID='" + sRandomID + "' order by 序号";
DataTable dtHomeBuild = SqlHelper.ExecuteSqlToTable(strHomeBuildCheck);
if (dtHomeBuild.Rows.Count > 0)
{
for (int i = 0; i < dtHomeBuild.Rows.Count; i++)
{
bCheckResult = false;
rowNum = dtHomeBuild.Rows[i]["序号"].ToString().Trim();
mes += "序号【" + rowNum + "】，错误说明：网格，小区，楼栋与基本表不对应。网格名称：【" + dtHomeBuild.Rows[i]["网格名称"].ToString() + "】---小区名称：【" + dtHomeBuild.Rows[i]["小区名称"].ToString() + "】---楼栋名称：【" + dtHomeBuild.Rows[i]["楼栋名称"].ToString() + "】<br/>";
}
}
string strIDCardCheck = "select 身份证号 from dbo._SQ_Excel_PeopleInfo WHERE 地区编码 = '" + sVillageCode + "' and sRandomID='" + sRandomID + "' group by 身份证号 having COUNT(1)>1";
DataTable dtIDCard = SqlHelper.ExecuteSqlToTable(strIDCardCheck);
if (dtIDCard.Rows.Count > 0)
{
bCheckResult = false;
for (int i = 0; i < dtIDCard.Rows.Count; i++)
{
string strRepeatIDCard = "SELECT * FROM _SQ_Excel_PeopleInfo WHERE [地区编码] = '" + sVillageCode + "' and sRandomID='" + sRandomID + "' AND 身份证号='" + dtIDCard.Rows[i]["身份证号"] + "' order by 序号";
DataTable dtRepeatIDCard = SqlHelper.ExecuteSqlToTable(strRepeatIDCard);
for (int j = 0; j < dtRepeatIDCard.Rows.Count; j++)
{
rowNum = dtRepeatIDCard.Rows[j]["序号"].ToString().Trim();
mes += "序号【" + rowNum + "】，错误说明：身份证有重复！重复号码为：【" + dtRepeatIDCard.Rows[i]["身份证号"].ToString() + "】<br/>";
}
}
}
string strIDCardLenCheck = "select 序号,身份证号,LEN(身份证号) as idLen from dbo._SQ_Excel_PeopleInfo where LEN(身份证号)!=18 and LEN(身份证号)!=15 AND 地区编码 = '" + sVillageCode + "' and sRandomID='" + sRandomID + "' order by 序号";
DataTable dtIDCardLen = SqlHelper.ExecuteSqlToTable(strIDCardLenCheck);
if (dtIDCardLen.Rows.Count > 0)
{
for (int i = 0; i < dtIDCardLen.Rows.Count; i++)
{
bCheckResult = false;
rowNum = dtIDCardLen.Rows[i]["序号"].ToString().Trim();
mes += "序号【" + rowNum + "】，错误说明：身份证位数不对！号码：【" + dtIDCardLen.Rows[i]["身份证号"].ToString() + "】---位数：【" + dtIDCardLen.Rows[i]["idLen"].ToString() + "】<br/>";
}
}
string strMZDictCheck = "select 序号,民族 from dbo._SQ_Excel_PeopleInfo where 民族 not in(select sValue from dbo.BaseInfo_dict where sTableCode='MZ') and 民族!='' AND 地区编码 = '" + sVillageCode + "' and sRandomID='" + sRandomID + "' order by 序号";
DataTable dtMZDict = SqlHelper.ExecuteSqlToTable(strMZDictCheck);
if (dtMZDict.Rows.Count > 0)
{
for (int i = 0; i < dtMZDict.Rows.Count; i++)
{
bCheckResult = false;
rowNum = dtMZDict.Rows[i]["序号"].ToString().Trim();
mes += "序号【" + rowNum + "】，错误说明：民族字段的值不在填表说明内！值为：【" + dtMZDict.Rows[i]["民族"].ToString() + "】<br/>";
}
}
string strXBDictCheck = "select 序号,性别 from dbo._SQ_Excel_PeopleInfo where 性别 not in(select sValue from dbo.BaseInfo_dict where sTableCode='XB') and 性别!='' AND 地区编码 = '" + sVillageCode + "' and sRandomID='" + sRandomID + "' order by 序号";
DataTable dtXBDict = SqlHelper.ExecuteSqlToTable(strXBDictCheck);
if (dtXBDict.Rows.Count > 0)
{
for (int i = 0; i < dtXBDict.Rows.Count; i++)
{
bCheckResult = false;
rowNum = dtXBDict.Rows[i]["序号"].ToString().Trim();
mes += "序号【" + rowNum + "】，错误说明：性别字段的值不在填表说明内！值为：【" + dtXBDict.Rows[i]["性别"].ToString() + "】<br/>";
}
}
string strJTGXDictCheck = "select 序号,与户主关系 from dbo._SQ_Excel_PeopleInfo where 与户主关系 not in(select sValue from dbo.BaseInfo_dict where sTableCode='JTGX') and 与户主关系!='' AND 地区编码 = '" + sVillageCode + "' and sRandomID='" + sRandomID + "' order by 序号";
DataTable dtJTGXDict = SqlHelper.ExecuteSqlToTable(strJTGXDictCheck);
if (dtJTGXDict.Rows.Count > 0)
{
for (int i = 0; i < dtJTGXDict.Rows.Count; i++)
{
bCheckResult = false;
rowNum = dtJTGXDict.Rows[i]["序号"].ToString().Trim();
mes += "序号【" + rowNum + "】，错误说明：与户主关系的值不在填表说明内！值为：【" + dtJTGXDict.Rows[i]["与户主关系"].ToString() + "】<br/>";
}
}
string strHJXZDictCheck = "select 序号,户口性质 from dbo._SQ_Excel_PeopleInfo where 户口性质 not in(select sValue from dbo.BaseInfo_dict where sTableCode='HJXZ') and 户口性质!='' AND 地区编码 = '" + sVillageCode + "' and sRandomID='" + sRandomID + "' order by 序号";
DataTable dtHJXZDict = SqlHelper.ExecuteSqlToTable(strHJXZDictCheck);
if (dtHJXZDict.Rows.Count > 0)
{
for (int i = 0; i < dtHJXZDict.Rows.Count; i++)
{
bCheckResult = false;
rowNum = dtHJXZDict.Rows[i]["序号"].ToString().Trim();
mes += "序号【" + rowNum + "】，错误说明：户口性质的值不在填表说明内！值为：【" + dtHJXZDict.Rows[i]["户口性质"].ToString() + "】<br/>";
}
}
string strRYXZDictCheck = "select 序号,人员性质 from dbo._SQ_Excel_PeopleInfo where 人员性质 not in(select sValue from dbo.BaseInfo_dict where sTableCode='RYXZ') and 人员性质!='' AND 地区编码 = '" + sVillageCode + "' and sRandomID='" + sRandomID + "' order by 序号";
DataTable dtRYXZDict = SqlHelper.ExecuteSqlToTable(strRYXZDictCheck);
if (dtRYXZDict.Rows.Count > 0)
{
for (int i = 0; i < dtRYXZDict.Rows.Count; i++)
{
bCheckResult = false;
rowNum = dtRYXZDict.Rows[i]["序号"].ToString().Trim();
mes += "序号【" + rowNum + "】，错误说明：人员性质的值不在填表说明内！值为：【" + dtRYXZDict.Rows[i]["人员性质"].ToString() + "】<br/>";
}
}
string strJTLBDictCheck = "select 序号,家庭类别 from dbo._SQ_Excel_PeopleInfo where 家庭类别 not in(select sValue from dbo.BaseInfo_dict where sTableCode='JTLB') and 家庭类别!='' AND 地区编码 = '" + sVillageCode + "' and sRandomID='" + sRandomID + "' order by 序号";
DataTable dtJTLBDict = SqlHelper.ExecuteSqlToTable(strJTLBDictCheck);
if (dtJTLBDict.Rows.Count > 0)
{
for (int i = 0; i < dtJTLBDict.Rows.Count; i++)
{
bCheckResult = false;
rowNum = dtJTLBDict.Rows[i]["序号"].ToString().Trim();
mes += "序号【" + rowNum + "】，错误说明：家庭类别的值不在填表说明内！值为：【" + dtJTLBDict.Rows[i]["家庭类别"].ToString() + "】<br/>";
}
}
string strMPHNotInFWInfo = "select 序号,门牌号,楼栋名称,小区名称 from dbo._SQ_Excel_PeopleInfo e"
+ " where 门牌号 not in (select sMPH from SQ_FWInfo f where e.楼栋编码=f.sBuildCode) AND 地区编码 = '" + sVillageCode + "' and sRandomID='" + sRandomID + "' order by 序号";
DataTable dtMPHNotInFWInfo = SqlHelper.ExecuteSqlToTable(strMPHNotInFWInfo);
if (dtMPHNotInFWInfo.Rows.Count > 0)
{
for (int i = 0; i < dtMPHNotInFWInfo.Rows.Count; i++)
{
bCheckResult = false;
rowNum = dtMPHNotInFWInfo.Rows[i]["序号"].ToString().Trim();
mes += "序号【" + rowNum + "】，错误说明：Excel表格里的门牌号不在生成的房屋内！小区名称：【" + dtMPHNotInFWInfo.Rows[i]["小区名称"] + "】---楼栋名称：【" + dtMPHNotInFWInfo.Rows[i]["楼栋名称"] + "】---门牌号：【" + dtMPHNotInFWInfo.Rows[i]["门牌号"] + "】<br/>";
}
}
//根据身份证号，判断人员数据是否已存在。若存在，不允许重复导入
string strExistData = "select e.* from dbo._SQ_Excel_PeopleInfo e join SQ_PeopleInfo p on e.身份证号=p.sIDCard where e.sRandomID='" + sRandomID + "' order by e.序号";
DataTable dtExistDataInfo = SqlHelper.ExecuteSqlToTable(strExistData);
if (dtExistDataInfo.Rows.Count > 0)
{
for (int i = 0; i < dtExistDataInfo.Rows.Count; i++)
{
bCheckResult = false;
rowNum = dtExistDataInfo.Rows[i]["序号"].ToString().Trim();
mes += "序号【" + rowNum + "】，错误说明：人员信息已存在，不允许重复导入！姓名：【" + dtExistDataInfo.Rows[i]["姓名"] + "】---身份证号：【" + dtExistDataInfo.Rows[i]["身份证号"] + "】<br/>";
}
}
if (mes == "")
{
mes = "数据校验成功！";
}
OutputResult(bCheckResult, mes);
}
#endregion
#region 一键导入家庭人口信息
/// <summary>
/// 一键导入家庭人口信息
/// </summary>
private void IntelligentImPort()
{
bool bInitResult = true;
string mes = "家庭人口信息生成成功！";
string sImportVillageCode = GetParam("sVillageCode");//社区编码
string sImportVillageName = GetParam("sVillageName");//地区控件选择的导入数据的地区---名称
string sRandomID = GetParam("sRandomID");//页面导入数据标识，Guid
List<string> sqlList = new List<string>();//执行语句
List<SqlParameter[]> paraList = new List<SqlParameter[]>();//执行语句相对应的参数
#region 导入家庭人口信息
string strPeopleSQL = "select * from _SQ_Excel_PeopleInfo where sRandomID='" + sRandomID + "' order by 网格编码";
DataTable dtPeople = SqlHelper.ExecuteSqlToTable(strPeopleSQL);
string strInsertFamilySQL = "insert into SQ_FamilyInfo(gID,sFamilyNo,sVillageCode,sFamilyFkDZ,sLBCode,iFlag,iDeleteMark,iSort,dCreateDate,sCreateUserRealname,MPHCode,iFL,sAreaCode,sTownCode,sHomeCode,sBuildCode,sWGCode,sShiCode,sShengCode) " +
"values(@sFamilyKey,@sFamilyNo,@sVillageCode,@sHKDZ,@sLBCode,1,0,0,getdate(),'数据导入',@sMPH,0,@sAreaCode,@sTownCode,@sHomeCode,@sBuildCode,@sWGCode,@sShiCode,@sShengCode)";
string strInsertPeopleSQL = "insert into SQ_PeopleInfo(gID,gFamliyID,sFamilyNo,sFamilyGX,sPeopName,sPeopNo,iBdhj,sIDCard,sSex,sNation,sLXDH,sHKXZ,sHJDZ,iSW,iRYXZ,iDeleteMark,iSort,dCreateDate,sCreateUserRealname,sAreaCode,sVillageCode,sTownCode,sHomeCode,sBuildCode,sWGCode,MPHCode,iZXState,sShiCode,sShengCode,dDate) " +
"values(NEWID(),@gPFamliyID,@sPFamilyNo,@sFamilyGX,@sPeopName,@sPeopNo,1,@sIDCard,@sSex,@sNation,@sLXDH,@sHKXZ,@sHJDZ,0,@iRYXZ,0,0,getdate(),'数据导入',@sAreaCode,@sPVillageCode,@sTownCode,@sHomeCode,@sBuildCode,@sWGCode,@MPHCode,0,@sShiCode,@sShengCode,@dPeopleDate)";
int iPeopleRows = dtPeople.Rows.Count;
for (int i = 0; i < iPeopleRows; i++)
{
string gPFamliyID = dtPeople.Rows[i]["家庭主键"].ToString();//家庭表主键
string sPFamilyNo = dtPeople.Rows[i]["家庭编码"].ToString();//家庭表编码
string sFamilyGX = GetDictCode("JTGX", dtPeople.Rows[i]["与户主关系"].ToString());//与户主关系
string sPeopName = dtPeople.Rows[i]["姓名"].ToString();//姓名
string sPeopNo = Guid.NewGuid().ToString().ToUpper();//人员编号
string sIDCard = dtPeople.Rows[i]["身份证号"].ToString();//身份证号
string sSex = GetDictCode("XB", dtPeople.Rows[i]["性别"].ToString());//性别
string sNation = GetDictCode("MZ", dtPeople.Rows[i]["民族"].ToString());//民族
string sLXDH = dtPeople.Rows[i]["联系电话"].ToString(); //联系电话
string sHKXZ = GetDictCode("HJXZ", dtPeople.Rows[i]["户口性质"].ToString());//户口性质
string sHJDZ = dtPeople.Rows[i]["户籍地址"].ToString(); //户籍地址
string iRYXZ = GetDictCode("RYXZ", dtPeople.Rows[i]["人员性质"].ToString());//人员性质
string sAreaCode = dtPeople.Rows[i]["县编码"].ToString();//县区编码
string sPVillageCode = dtPeople.Rows[i]["地区编码"].ToString();//居委村编码
string sTownCode = dtPeople.Rows[i]["乡镇编码"].ToString();//乡镇编码
string sHomeCode = dtPeople.Rows[i]["小区编码"].ToString();//小区编码
string sBuildCode = dtPeople.Rows[i]["楼栋编码"].ToString();//楼栋编码
string sWGCode = dtPeople.Rows[i]["网格编码"].ToString();//网格编码
string MPHCode = dtPeople.Rows[i]["门牌号"].ToString();//门牌号
string sShiCode = dtPeople.Rows[i]["市编码"].ToString();//市编码
string sShengCode = dtPeople.Rows[i]["省编码"].ToString();//省编码
DateTime? dPeopleDate = null;//出生日期
DateTime dTemp = GetDateFromIDCard(sIDCard);//
if (dTemp.ToString() != "1900-01-01")
{
dPeopleDate = dTemp;
}
sqlList.Add(strInsertPeopleSQL);
paraList.Add(new SqlParameter[]{
new SqlParameter("@gPFamliyID",gPFamliyID),
new SqlParameter("@sPFamilyNo",sPFamilyNo),
new SqlParameter("@sFamilyGX",sFamilyGX),
new SqlParameter("@sPeopName",sPeopName),
new SqlParameter("@sPeopNo",sPeopNo),
new SqlParameter("@sIDCard",sIDCard),
new SqlParameter("@sSex",sSex),
new SqlParameter("@sNation",sNation),
new SqlParameter("@sLXDH",sLXDH),
new SqlParameter("@sHKXZ",sHKXZ),
new SqlParameter("@sHJDZ",sHJDZ),
new SqlParameter("@iRYXZ",iRYXZ),
new SqlParameter("@sAreaCode",sAreaCode),
new SqlParameter("@sPVillageCode",sPVillageCode),
new SqlParameter("@sTownCode",sTownCode),
new SqlParameter("@sHomeCode",sHomeCode),
new SqlParameter("@sBuildCode",sBuildCode),
new SqlParameter("@sWGCode",sWGCode),
new SqlParameter("@MPHCode",MPHCode),
new SqlParameter("@sShiCode",sShiCode),
new SqlParameter("@sShengCode",sShengCode),
new SqlParameter("@dPeopleDate",dPeopleDate)
});
}
for (int i = 0; i < iPeopleRows; i++)
{
if (dtPeople.Rows[i]["与户主关系"].ToString() == "本人")
{
string sFamilyKey = dtPeople.Rows[i]["家庭主键"].ToString();//家庭表主键
string sFamilyNo = dtPeople.Rows[i]["家庭编码"].ToString();//家庭编号
string sVillageCode = dtPeople.Rows[i]["地区编码"].ToString();//地区编码
string sHKDZ = dtPeople.Rows[i]["户籍地址"].ToString();//户口地址
string sLBCode = GetDictCode("JTLB", dtPeople.Rows[i]["家庭类别"].ToString());//家庭类别
string sMPH = dtPeople.Rows[i]["门牌号"].ToString(); //门牌号
string sAreaCode = dtPeople.Rows[i]["县编码"].ToString();//县区编码
string sTownCode = dtPeople.Rows[i]["乡镇编码"].ToString();//乡镇编码
string sHomeCode = dtPeople.Rows[i]["小区编码"].ToString();//小区编码
string sBuildCode = dtPeople.Rows[i]["楼栋编码"].ToString();//楼栋编码
string sWGCode = dtPeople.Rows[i]["网格编码"].ToString();//网格编码
string sShiCode = dtPeople.Rows[i]["市编码"].ToString();//市编码
string sShengCode = dtPeople.Rows[i]["省编码"].ToString();//省编码
sqlList.Add(strInsertFamilySQL);
paraList.Add(new SqlParameter[]{
new SqlParameter("@sFamilyKey",sFamilyKey),
new SqlParameter("@sFamilyNo",sFamilyNo),
new SqlParameter("@sVillageCode",sVillageCode),
new SqlParameter("@sHKDZ",sHKDZ),
new SqlParameter("@sLBCode",sLBCode),
new SqlParameter("@sMPH",sMPH),
new SqlParameter("@sAreaCode",sAreaCode),
new SqlParameter("@sTownCode",sTownCode),
new SqlParameter("@sHomeCode",sHomeCode),
new SqlParameter("@sBuildCode",sBuildCode),
new SqlParameter("@sWGCode",sWGCode),
new SqlParameter("@sShiCode",sShiCode),
new SqlParameter("@sShengCode",sShengCode),
});
string strSelectPeople = "select gID,sPeopName,sIDCard,sFamilyGX from sq_PeopleInfo where gFamliyID='" + sFamilyKey + "' and iDeleteMark=0 ORDER BY sFamilyGX ";
DataTable dtPeoInFamily = SqlHelper.ExecuteSqlToTable(strSelectPeople);
string strPeopleNames = "";
string strIDCards = "";
for (int m = 0; m < dtPeoInFamily.Rows.Count; m++)
{
if (dtPeoInFamily.Rows[m]["sFamilyGX"].ToString() == "01")//如果是户主
{
strPeopleNames = dtPeoInFamily.Rows[m]["sPeopName"].ToString() + "," + strPeopleNames;
strIDCards = "," + dtPeoInFamily.Rows[m]["sIDCard"].ToString() + "," + strIDCards;
}
else
{
strPeopleNames += dtPeoInFamily.Rows[m]["sPeopName"].ToString() + ",";
strIDCards += "," + dtPeoInFamily.Rows[m]["sIDCard"].ToString() + ",";
}
}
int PeoInFamilyCount = dtPeoInFamily.Rows.Count;//家庭人口数
string strUpdateFamily = "update SQ_FamilyInfo set Peoples=@strPeopleNames, IDCards=@strIDCards, iJTRKS=@PeoInFamilyCount where gID=@sFamilyKey";
sqlList.Add(strUpdateFamily);
paraList.Add(new SqlParameter[]{
new SqlParameter("@strPeopleNames",strPeopleNames),
new SqlParameter("@strIDCards",strIDCards),
new SqlParameter("@PeoInFamilyCount",PeoInFamilyCount),
new SqlParameter("@sFamilyKey",sFamilyKey)
});
}
}
string strUpdateFamilyMPH = "update SQ_FamilyInfo set MPHCode=f.MPHCode from SQ_FWInfo f,_SQ_Excel_PeopleInfo p where SQ_FamilyInfo.MPHCode=f.sMPH and SQ_FamilyInfo.sBuildCode=f.sBuildCode and SQ_FamilyInfo.sVillageCode=p.地区编码 and p.sRandomID=@sRandomID ";
//更新字典项名称----性别名称，民族名称，与户主关系名称
strUpdateFamilyMPH += "update SQ_PeopleInfo set sSexName=d.sValue from dbo.BaseInfo_dict d, _SQ_Excel_PeopleInfo p where SQ_PeopleInfo.sSex=d.sCode and d.sTableCode='XB' and SQ_PeopleInfo.sVillageCode=p.地区编码 and p.sRandomID=@sRandomID ";
strUpdateFamilyMPH += "update SQ_PeopleInfo set sNationName=d.sValue from dbo.BaseInfo_dict d, _SQ_Excel_PeopleInfo p where SQ_PeopleInfo.sNation=d.sCode and d.sTableCode='MZ' and SQ_PeopleInfo.sVillageCode=p.地区编码 and p.sRandomID=@sRandomID ";
strUpdateFamilyMPH += "update SQ_PeopleInfo set sFamilyGXName=d.sValue from dbo.BaseInfo_dict d, _SQ_Excel_PeopleInfo p where SQ_PeopleInfo.sFamilyGX=d.sCode and d.sTableCode='JTGX' and SQ_PeopleInfo.sVillageCode=p.地区编码 and p.sRandomID=@sRandomID ";
sqlList.Add(strUpdateFamilyMPH);
paraList.Add(new SqlParameter[] {
new SqlParameter("@sRandomID",sRandomID)
});
#region 记录数据导入日志
string strInsertLog = "insert into _SQ_DataImportLog(sID,sVillageCode,sVillageName,iType,sUpLoadUserLoginName,sUpLoadUserRealName,dUpLoadDate)" +
" values(NEWID(),@sImportVillageCode,@sImportVillageName,2,@sUserName,@sRealName,getdate())";
string sUserName = GetSessionCurrentUserInfo.sUserName;//用户登陆名
string sRealName = GetSessionCurrentUserInfo.sRealName;//用户姓名
sqlList.Add(strInsertLog);
paraList.Add(new SqlParameter[] {
new SqlParameter("@sImportVillageCode",sImportVillageCode),
new SqlParameter("@sImportVillageName",sImportVillageName),
new SqlParameter("@sUserName",sUserName),
new SqlParameter("@sRealName",sRealName),
});
#endregion
#endregion
try
{
bInitResult = SqlHelper.ExecuteTrans(sqlList, paraList);
}
catch (Exception)
{
bInitResult = false;
mes = "家庭人口信息生成失败！";
throw;
}
OutputResult(bInitResult, mes);
}
/// <summary>
/// 根据字典名称获取字典编码
/// </summary>
/// <param name="tablecode"></param>
/// <param name="name"></param>
/// <returns></returns>
private string GetDictCode(string tablecode, string name)
{
string sql = "select top 1 scode from baseinfo_dict where sname='" + name + "' and stablecode='" + tablecode + "' and ideletemark=0 and iuse=1";
var obj = SqlHelper.ExecuteScalar(sql);
if (obj == null)
return "";
return obj.ToString();
}
/// <summary>
/// 身份证号转换日期
/// </summary>
/// <param name="sIDCard"></param>
/// <returns></returns>
private DateTime GetDateFromIDCard(string sIDCard)
{
sIDCard = sIDCard.Trim();
if (!string.IsNullOrEmpty(sIDCard))
{
string tmp = "";
DateTime dResult = DateTime.Parse("1900-01-01");
if (sIDCard.StartsWith("R") || sIDCard.StartsWith("r"))
{
return dResult;
}
if (sIDCard.Length == 18)
{
tmp = sIDCard.Substring(6, 8);//截取生日信息
tmp = tmp.Insert(4, "-").Insert(7, "-");//在字符串相应位置插入字符
}
else if (sIDCard.Length == 15)
{
//15位身份证号码
tmp = sIDCard.Substring(6, 2);
if (int.Parse(tmp) < 10)
tmp = "20" + tmp;
else
tmp = "19" + tmp;
tmp = tmp + '-' + sIDCard.Substring(8, 2) + '-' + sIDCard.Substring(10, 2);
}
if (DateTime.TryParse(tmp, out dResult))
{
return dResult;
}
}
return DateTime.Parse("1900-01-01");
}
#endregion
}
}
扫一扫
1041
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
