示例#1
0
        public string uploadCSSHOPInfo(string filePath)
        {
            string    modePath = System.IO.Directory.GetCurrentDirectory() + "\\ExcelModel\\出售商户模板.xls"; //原始文件
            string    path     = filePath;                                                               //原始文件
            string    mes      = "";
            DataTable dt       = new DataTable();

            UTILITY.ExcelTools tool = new UTILITY.ExcelTools();
            tool.GetDataTable(System.IO.File.OpenRead(path), path, modePath, ref mes, ref dt);
            if (dt == null || dt.Rows.Count == 0)
            {
                return("空数据,导入失败!");
            }
            try
            {
                string b = db.UpLoadCSShopInfo(dt);
                if (b == "")
                {
                    return("");
                }
                else
                {
                    return(b);
                }
            }
            catch (Exception e)
            {
                return(e.Message);
            }
        }
示例#2
0
        public string UploadHouseInfo(string filePath, Dictionary <string, object> userinfo)
        {
            Dictionary <string, object> r = new Dictionary <string, object>();
            List <string> list            = new List <string>();
            string        modePath        = System.IO.Directory.GetCurrentDirectory() + "/WY_API/ExcelModel/房屋档案表模板.xls";//原始文件

            modePath = modePath.Replace("//", "/");
            string    path = filePath;//原始文件
            string    mes  = "";
            DataTable dt   = new DataTable();

            UTILITY.ExcelTools tool = new UTILITY.ExcelTools();
            tool.GetDataTable(System.IO.File.OpenRead(path), path, modePath, ref mes, ref dt);
            if (dt == null || dt.Rows.Count == 0)
            {
                return("空数据,导入失败!");
            }
            try
            {
                string b = db.UpLoadHouseInfo(dt, userinfo);
                if (b == "")
                {
                    return("");
                }
                else
                {
                    return(b);
                }
            }
            catch (Exception e)
            {
                return(e.Message);
            }
        }
示例#3
0
        [Consumes("multipart/form-data")]//此处为新增

        public IActionResult UploadExcelFiles([FromForm] IFormCollection formCollection)
        {
            Dictionary <string, object> r = new Dictionary <string, object>();

            try
            {
                FormFileCollection  fileCollection = (FormFileCollection)formCollection.Files;
                IFormFileCollection dd             = Request.Form.Files;
                IFormFile           file           = fileCollection[0];
                string path = file.FileName;
                //HttpFileCollection
                Stream             aa       = file.OpenReadStream();
                Stream             BB       = dd[0].OpenReadStream();
                string             modePath = System.IO.Directory.GetCurrentDirectory() + "\\ExcelModel\\组织结构模板.xlsx";//原始文件
                string             mes      = "";
                DataTable          dt       = new DataTable();
                UTILITY.ExcelTools tool     = new UTILITY.ExcelTools();
                tool.GetDataTable(aa, path, modePath, ref mes, ref dt);
                return(Json(r));
            }
            catch (Exception ex)
            {
                r["code"]    = -1;
                r["message"] = ex.Message;
            }
            return(Json(r));
        }
示例#4
0
        //public string truck(int PageSize, List<string> lst)
        //{
        //    string str = "";
        //    try
        //    {
        //        int truckNum = Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(lst.Count / PageSize)));
        //        for (int PageIndex = 1; PageIndex <= truckNum; PageIndex++)
        //        {
        //            int rowbegin = (PageIndex - 1) * PageSize;
        //            int rowend = PageIndex * PageSize;
        //            List<string> tempLst = new List<string>();
        //            if (rowend > lst.Count)
        //                rowend = lst.Count;
        //            for (int i = rowbegin; i < rowend; i++)
        //            {
        //                tempLst.Add(lst[i]);
        //            }
        //            str+=db.UploadOrgFileList(tempLst);
        //        }
        //        return str;
        //    }
        //    catch (Exception ex)
        //    {
        //        return ex.ToString();
        //    }
        //}

        public string UploadOrgFile(string filePath)
        {
            string    modePath = System.IO.Directory.GetCurrentDirectory() + "\\ExcelModel\\组织结构模板.xlsx"; //原始文件
            string    path     = filePath;                                                                //原始文件
            string    mes      = "";
            DataTable dt       = new DataTable();

            UTILITY.ExcelTools tool = new UTILITY.ExcelTools();
            tool.GetDataTable(System.IO.File.OpenRead(path), path, modePath, ref mes, ref dt);

            if (dt == null || dt.Rows.Count == 0)
            {
                return("空数据,导入失败!");
            }
            DataView dv = new DataView(dt);

            if (dt.Rows.Count != dv.ToTable(true, "组织机构编码").Rows.Count)
            {
                return("组织机构编码存在重复数据,导入失败!");
            }
            List <string> list = new List <string>();

            foreach (DataRow row in dt.Rows)
            {
                StringBuilder sb = new StringBuilder("insert into ts_uidp_org (ORG_ID,ORG_CODE,ORG_NAME,ORG_SHORT_NAME,ORG_CODE_UPPER,ISINVALID,ISDELETE,REMARK,DW_CODE) values ");
                sb.Append("('" + Guid.NewGuid().ToString() + "',");
                sb.Append("'" + getString(row["组织机构编码"]) + "',");
                sb.Append("'" + getString(row["组织机构名称"]) + "',");
                sb.Append("'" + getString(row["组织机构简称"]) + "',");
                sb.Append("'" + getString(row["上级组织机构编码"]) + "',");
                if (row["是否有效"] != null && row["是否有效"].ToString() == "是")
                {
                    sb.Append("'1',");
                }
                else
                {
                    sb.Append("'0',");
                }
                sb.Append("'1',");
                sb.Append("'" + getString(row["备注"]) + "','");
                sb.Append(getString(row["单位编码"]) + "')");
                list.Add(sb.ToString());
            }
            return(db.UploadOrgFileList(list));
        }
示例#5
0
        public string UploadUserFile(string filePath)
        {
            List <string> list     = new List <string>();
            string        modePath = System.IO.Directory.GetCurrentDirectory() + "\\ExcelModel\\用户.xlsx"; //原始文件
            string        path     = filePath;                                                            //原始文件
            string        mes      = "";
            DataTable     dt       = new DataTable();

            UTILITY.ExcelTools tool = new UTILITY.ExcelTools();
            tool.GetDataTable(System.IO.File.OpenRead(path), path, modePath, ref mes, ref dt);

            if (dt == null || dt.Rows.Count == 0)
            {
                return("空数据,导入失败!");
            }
            //DataView dv = new DataView(dt);
            //String[] str = {  "组织机构编码", "组织机构名称", "账号", "姓名", "员工编号", "性别", "办公电话", "手机", "邮箱", "访问IP", "账号类型","备注" };
            //dt = dv.ToTable(true, str);

            //if (dt.Rows.Count != dv.ToTable(true, "账号").Rows.Count)
            //{
            //    return "账号列存在重复数据,导入失败!";
            //}
            string error = GetDistinctSelf(dt, "账号");

            if (error != null && error.Length > 0)
            {
                return(error);
            }
            int truckNum = Convert.ToInt32(Convert.ToDecimal(dt.Rows.Count / 500));
            int yushu    = dt.Rows.Count % 500;

            if (yushu > 0)
            {
                truckNum++;
            }

            for (int j = 1; j < truckNum + 1; j++)
            {
                string        fengefu   = "";
                StringBuilder sb        = new StringBuilder();
                StringBuilder sbOrgUser = new StringBuilder();
                sbOrgUser.Append("insert into ts_uidp_org_user(ORG_ID,USER_ID)values ");
                sb.Append(" INSERT INTO ts_uidp_userinfo(USER_ID,USER_DOMAIN,USER_CODE,USER_NAME,USER_PASS,PHONE_MOBILE,PHONE_OFFICE," +
                          "USER_EMAIL,USER_IP,USER_SEX,AUTHENTICATION_TYPE,FLAG,REG_TIME,REMARK) values ");
                OrgDB     orgDB    = new OrgDB();
                DataTable dtOrg    = orgDB.fetchOrgList();
                string    result   = "";
                string    fengefu2 = "";
                int       rowbegin = (j - 1) * 500;
                int       rowend   = j * 500;
                if (rowend > dt.Rows.Count)
                {
                    rowend = dt.Rows.Count;
                }
                for (int i = rowbegin; i < rowend; i++)
                {
                    if (dt.Rows[i]["组织机构编码"] == null || dt.Rows[i]["账号"] == null)
                    {
                        result  += fengefu2 + "第" + (i + 2) + "行,组织机构编码或者账号不能为空!,导入失败!";
                        fengefu2 = ",";
                        continue;
                    }
                    if (dt.Rows[i]["组织机构编码"].ToString() == "" || dt.Rows[i]["账号"].ToString() == "")
                    {
                        result  += fengefu2 + "第" + (i + 2) + "行,组织机构编码或者账号不能为空!,导入失败!";
                        fengefu2 = ",";
                        continue;
                    }
                    DataRow[] OrgRow = dtOrg.Select("ORG_CODE='" + dt.Rows[i]["组织机构编码"].ToString().Trim() + "'");
                    if (OrgRow.Length <= 0)
                    {
                        result  += fengefu2 + "第" + (i + 2) + "行,系统中不存在此组织机构编码!,导入失败!";
                        fengefu2 = ",";
                        continue;
                    }
                    string id = Guid.NewGuid().ToString();
                    sbOrgUser.Append(fengefu + "('" + dt.Rows[i]["组织机构编码"].ToString().Trim() + "','" + id + "')");
                    sb.Append(fengefu + "('" + id + "',");
                    sb.Append("'" + getString(dt.Rows[i]["账号"]) + "',");
                    sb.Append("'" + getString(dt.Rows[i]["员工编号"]) + "',");
                    sb.Append("'" + getString(dt.Rows[i]["姓名"]) + "',");
                    sb.Append("'123456',");
                    sb.Append("'" + getString(dt.Rows[i]["手机"]) + "',");
                    sb.Append("'" + getString(dt.Rows[i]["办公电话"]) + "',");
                    sb.Append("'" + getString(dt.Rows[i]["邮箱"]) + "',");
                    sb.Append("'" + getString(dt.Rows[i]["访问IP"]) + "',");
                    if (dt.Rows[i]["性别"] != null && dt.Rows[i]["性别"].ToString() == "男")
                    {
                        sb.Append("1,");
                    }
                    else
                    {
                        sb.Append("0,");
                    }
                    if (dt.Rows[i]["账号类型"] != null && dt.Rows[i]["账号类型"].ToString() == "PTR账号")
                    {
                        sb.Append("'1',");
                    }
                    else
                    {
                        sb.Append("'0',");
                    }
                    sb.Append("1,'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',");
                    sb.Append("'" + getString(dt.Rows[i]["备注"]) + "')");
                    fengefu = ",";
                }
                if (sbOrgUser != null && sbOrgUser.Length > 0)
                {
                    list.Add(sbOrgUser.ToString());
                }
                if (sb != null && sb.Length > 0)
                {
                    list.Add(sb.ToString());
                }
            }

            //string sqlUpdate = "   update a  set a.ORG_ID=b.ORG_ID from ts_uidp_org_user a ,ts_uidp_org b where  a.ORG_ID=b.ORG_CODE ";
            //string sqlUpdate = "update ts_uidp_org_user a ,ts_uidp_org b set a.ORG_ID = b.ORG_ID where a.ORG_ID = b.ORG_CODE";
            if (db.GetDBType() == "MYSQL")
            {
                string sqlUpdate = "   update ts_uidp_org_user a ,ts_uidp_org b set a.ORG_ID = b.ORG_ID where a.ORG_ID = b.ORG_CODE";

                list.Add(sqlUpdate);
            }
            else if (db.GetDBType() == "SQLSERVER")
            {
                string sqlUpdate = "   update a  set a.ORG_ID=b.ORG_ID from ts_uidp_org_user a ,ts_uidp_org b where  a.ORG_ID=b.ORG_CODE ";

                list.Add(sqlUpdate);
            }
            else if (db.GetDBType() == "ORACLE")
            {
                string sqlUpdate = "  update ts_uidp_org_user a ,ts_uidp_org b set a.ORG_ID = b.ORG_ID where a.ORG_ID = b.ORG_CODE";

                list.Add(sqlUpdate);
            }

            return(db.UploadUserFile(list));
        }
示例#6
0
        public string UploadUserFileNew(string filePath)
        {
            //string modePath = System.IO.Directory.GetCurrentDirectory() + "\\ExcelModel\\用户.xlsx";//原始文件
            //string path = filePath;//原始文件
            //string mes = "";
            //
            //DataTable dt = new DataTable();
            //UTILITY.ExcelTools tool = new UTILITY.ExcelTools();
            //tool.GetDataTable(System.IO.File.OpenRead(path), path, modePath, ref mes, ref dt);

            List <string> list     = new List <string>();
            string        modePath = System.IO.Directory.GetCurrentDirectory() + "\\ExcelModel\\用户.xls"; //原始文件
            string        path     = filePath;                                                           //原始文件
            string        mes      = "";
            string        result   = "";
            DataTable     dt       = new DataTable();
            DataTable     userdt   = db.fetchUserList();

            UTILITY.ExcelTools tool = new UTILITY.ExcelTools();
            tool.GetDataTable(System.IO.File.OpenRead(path), path, modePath, ref mes, ref dt);
            if (dt == null || dt.Rows.Count == 0)
            {
                return("空数据,导入失败!");
            }
            string error = GetDistinctSelf(dt, "账号");
            //if (error != null && error.Length > 0)
            //{
            //    return error;
            //}
            int truckNum = Convert.ToInt32(Convert.ToDecimal(dt.Rows.Count / 500));
            int yushu    = dt.Rows.Count % 500;

            if (yushu > 0)
            {
                truckNum++;
            }
            for (int j = 1; j < truckNum + 1; j++)
            {
                string fengefu = "";
                //StringBuilder sb = new StringBuilder();
                //StringBuilder sbOrgUser = new StringBuilder();
                //sbOrgUser.Append("insert into ts_uidp_org_user(ORG_ID,USER_ID)values ");
                //sb.Append(" INSERT INTO ts_uidp_userinfo(USER_ID,USER_DOMAIN,USER_CODE,USER_NAME,USER_PASS,PHONE_MOBILE,PHONE_OFFICE," +
                //    "USER_EMAIL,USER_IP,USER_SEX,AUTHENTICATION_TYPE,FLAG,REG_TIME,REMARK) values ");
                OrgDB     orgDB    = new OrgDB();
                DataTable dtOrg    = orgDB.fetchOrgList();
                string    fengefu2 = "";
                int       rowbegin = (j - 1) * 500;
                int       rowend   = j * 500;
                if (rowend > dt.Rows.Count)
                {
                    rowend = dt.Rows.Count;
                }
                for (int i = rowbegin; i < rowend; i++)
                {
                    var       usercode = getString(dt.Rows[i]["账号"]);
                    DataRow[] rows     = userdt.Select("USER_DOMAIN='" + usercode + "'");
                    if (dt.Rows[i]["组织机构编码"] == null || dt.Rows[i]["账号"] == null)
                    {
                        result  += fengefu2 + "第" + (i + 2) + "行,组织机构编码或者账号不能为空!,导入失败!";
                        fengefu2 = ",";
                        continue;
                    }
                    if (dt.Rows[i]["组织机构编码"].ToString() == "" || dt.Rows[i]["账号"].ToString() == "")
                    {
                        result  += fengefu2 + "第" + (i + 2) + "行,组织机构编码或者账号不能为空!,导入失败!";
                        fengefu2 = ",";
                        continue;
                    }
                    if (dt.Rows[i]["账号类型"] == null || dt.Rows[i]["账号类型"].ToString() == "")
                    {
                        result  += fengefu2 + "第" + (i + 2) + "行,账号类型不能为空!,导入失败!";
                        fengefu2 = ",";
                        continue;
                    }
                    if (dt.Rows[i]["用户类型"] == null || dt.Rows[i]["用户类型"].ToString() == "")
                    {
                        result  += fengefu2 + "第" + (i + 2) + "行,用户类型不能为空!,导入失败!";
                        fengefu2 = ",";
                        continue;
                    }
                    if (dt.Rows[i]["员工姓名"] == null || dt.Rows[i]["员工姓名"].ToString() == "")
                    {
                        result  += fengefu2 + "第" + (i + 2) + "行,员工姓名不能为空!,导入失败!";
                        fengefu2 = ",";
                        continue;
                    }
                    if (dt.Rows[i]["性别"] == null || dt.Rows[i]["性别"].ToString() == "")
                    {
                        result  += fengefu2 + "第" + (i + 2) + "行,性别不能为空!,导入失败!";
                        fengefu2 = ",";
                        continue;
                    }
                    DataRow[] OrgRow = dtOrg.Select("ORG_CODE='" + dt.Rows[i]["组织机构编码"].ToString().Trim() + "'");
                    if (OrgRow.Length <= 0)
                    {
                        result  += fengefu2 + "第" + (i + 2) + "行,系统中不存在此组织机构编码!,导入失败!";
                        fengefu2 = ",";
                        continue;
                    }
                    if (rows.Length == 0)
                    {
                        StringBuilder sbOrgUser = new StringBuilder("insert into ts_uidp_org_user(ORG_ID,USER_ID)values");
                        string        id        = Guid.NewGuid().ToString();
                        sbOrgUser.Append("('" + dt.Rows[i]["组织机构编码"].ToString().Trim() + "','" + id + "')");
                        list.Add(sbOrgUser.ToString());
                        StringBuilder sb = new StringBuilder(" INSERT INTO ts_uidp_userinfo(USER_ID,AUTHENTICATION_TYPE,USER_DOMAIN,USER_TYPE,USER_PASS,USER_NAME,USER_CODE,USER_SEX,PHONE_OFFICE,PHONE_MOBILE," +
                                                             "USER_EMAIL,FLAG,REG_TIME,REMARK) values ");
                        sb.Append(fengefu + "('" + id + "',");
                        if (dt.Rows[i]["账号类型"] != null && dt.Rows[i]["账号类型"].ToString() == "PTR账号")
                        {
                            sb.Append("1,");
                        }
                        else
                        {
                            sb.Append("0,");
                        }
                        sb.Append("'" + getString(dt.Rows[i]["账号"]) + "',");
                        if (dt.Rows[i]["用户类型"] != null && dt.Rows[i]["用户类型"].ToString() == "普通用户")
                        {
                            sb.Append("1,");
                        }
                        else
                        {
                            sb.Append("0,");
                        }
                        sb.Append("'");
                        sb.Append(getString(dt.Rows[i]["用户密码"]) == "" ? UIDP.Security.SecurityHelper.StringToMD5Hash("123456") : UIDP.Security.SecurityHelper.StringToMD5Hash(getString(dt.Rows[i]["用户密码"])));
                        sb.Append("',");
                        sb.Append("'" + getString(dt.Rows[i]["员工姓名"]) + "',");
                        sb.Append("'" + getString(dt.Rows[i]["员工编号"]) + "',");
                        if (dt.Rows[i]["性别"] != null && dt.Rows[i]["性别"].ToString() == "男")
                        {
                            sb.Append("1,");
                        }
                        else
                        {
                            sb.Append("0,");
                        }
                        sb.Append("'" + getString(dt.Rows[i]["办公电话"]) + "',");
                        sb.Append("'" + getString(dt.Rows[i]["手机"]) + "',");
                        sb.Append("'" + getString(dt.Rows[i]["电子邮箱"]) + "',");

                        if (dt.Rows[i]["账号状态"] != null && dt.Rows[i]["账号状态"].ToString() == "禁用")
                        {
                            sb.Append("0,");
                        }
                        else
                        {
                            sb.Append("1,");
                        }
                        sb.Append("TO_DATE('" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss'),");
                        sb.Append("'" + getString(dt.Rows[i]["备注"]) + "')");
                        //fengefu = ",";
                        list.Add(sb.ToString());
                    }
                    else
                    {
                        foreach (var item in rows)
                        {
                            string sql = "update  ts_uidp_userinfo set ";
                            sql += " AUTHENTICATION_TYPE=";
                            sql += getString((dt.Rows[i]["账号类型"] != null && dt.Rows[i]["账号类型"].ToString() == "PTR账号") ? 1 : 0) + ",";
                            sql += " USER_DOMAIN='" + getString(dt.Rows[i]["账号"]) + "',";
                            sql += " USER_TYPE=";
                            sql += getString((dt.Rows[i]["用户类型"] != null && dt.Rows[i]["用户类型"].ToString() == "普通用户") ? 1 : 0) + ",";
                            sql += " USER_PASS='******'," : UIDP.Security.SecurityHelper.StringToMD5Hash(getString(dt.Rows[i]["用户密码"])) + "',";
                            sql += " USER_NAME='" + getString(dt.Rows[i]["员工姓名"]) + "',";
                            sql += " USER_CODE='" + getString(dt.Rows[i]["员工编号"]) + "',";
                            sql += " USER_SEX=";
                            sql += getString((dt.Rows[i]["性别"] != null && dt.Rows[i]["性别"].ToString() == "男") ? 1 : 0) + ",";

                            sql += " PHONE_MOBILE='" + getString(dt.Rows[i]["手机"]) + "',";
                            sql += " PHONE_OFFICE='" + getString(dt.Rows[i]["办公电话"]) + "',";
                            sql += " USER_EMAIL='" + getString(dt.Rows[i]["电子邮箱"]) + "',";
                            sql += " FLAG=";
                            sql += getString((dt.Rows[i]["账号状态"] != null && dt.Rows[i]["账号状态"].ToString() == "禁用") ? 0: 1) + ",";
                            sql += " REG_TIME=TO_DATE('" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss'),";
                            sql += " REMARK='" + getString(dt.Rows[i]["备注"]) + "'";
                            sql += " where USER_ID='" + item["USER_ID"].ToString() + "'";
                            list.Add(sql);
                            string sql2 = "update ts_uidp_org_user set ORG_ID=(SELECT ORG_ID FROM ts_uidp_org where ORG_CODE='" + dt.Rows[i]["组织机构编码"].ToString().Trim() + "') where USER_ID='" + item["USER_ID"].ToString() + "'";
                            list.Add(sql2);
                        }
                    }
                    //if (sbOrgUser != null && sbOrgUser.Length > 0)
                    //{
                    //    list.Add(sbOrgUser.ToString());
                    //}
                    //if (sb != null && sb.Length > 0)
                    //{
                    //    list.Add(sb.ToString());
                    //}
                }
                //if (sb.Length > 0)
                //{
                //    sb.Insert(0, " INSERT INTO ts_uidp_userinfo(USER_ID,AUTHENTICATION_TYPE,USER_DOMAIN,USER_TYPE,USER_PASS,USER_NAME,USER_CODE,USER_SEX,PHONE_OFFICE,PHONE_MOBILE," +
                //    "USER_EMAIL,FLAG,REG_TIME,REMARK) values ");
                //}
                //if (sbOrgUser.Length > 0)
                //{
                //    sbOrgUser.Insert(0, " insert into ts_uidp_org_user(ORG_ID,USER_ID)values ");
                //}
                //if (sb != null && sb.Length > 0)
                //{
                //    list.Add(sb.ToString());
                //}
                //if (sbOrgUser != null && sbOrgUser.Length > 0)
                //{
                //    list.Add(sbOrgUser.ToString());
                //}
            }

            if (db.GetDBType() == "MYSQL")
            {
                string sqlUpdate = "   update ts_uidp_org_user a ,ts_uidp_org b set a.ORG_ID = b.ORG_ID where a.ORG_ID = b.ORG_CODE";

                list.Add(sqlUpdate);
            }
            else if (db.GetDBType() == "SQLSERVER")
            {
                string sqlUpdate = "   update a  set a.ORG_ID=b.ORG_ID from ts_uidp_org_user a ,ts_uidp_org b where  a.ORG_ID=b.ORG_CODE ";

                list.Add(sqlUpdate);
            }
            else if (db.GetDBType() == "ORACLE")
            {
                //string sqlUpdate = "  update ts_uidp_org_user a ,ts_uidp_org b set a.ORG_ID = b.ORG_ID where a.ORG_ID = b.ORG_CODE";
                //string sqlUpdate = "UPDATE TS_UIDP_ORG_USER a SET a.ORG_ID = (SELECT ORG_ID FROM TS_UIDP_ORG b WHERE a.ORG_ID = b.ORG_CODE)";

                //list.Add(sqlUpdate);
            }
            if (result != "")
            {
                return(result);
            }
            else
            {
                return(db.UploadUserFile(list));
            }

            //DataView dv = new DataView(dt);
            //if (dt.Rows.Count != dv.ToTable(true, "账号").Rows.Count)
            //{
            //    return "账号列存在重复数据,导入失败!";
            //}
            //List<string> list = new List<string>();
            //string fengefu = "";
            //StringBuilder sb = new StringBuilder();
            //StringBuilder sbOrgUser = new StringBuilder();

            //string result = "";
            //string fengefu2 = "";
            //for (int i = 0; i < dt.Rows.Count; i++)
            //{
            //    var usercode = getString(dt.Rows[i]["账号"]);
            //    var dtt = userdt;
            //    DataRow[] rows = userdt.Select("USER_DOMAIN='" + usercode + "'");
            //    if (rows.Length == 0)
            //    {
            //        //sb.Append(" insert into ts_uidp_org (ORG_ID,ORG_CODE,ORG_NAME,ORG_SHORT_NAME,ORG_CODE_UPPER,ISINVALID,ISDELETE,REMARK) values ");

            //        string id = Guid.NewGuid().ToString();
            //        sbOrgUser.Append(fengefu + "('" + dt.Rows[i]["组织机构编码"].ToString().Trim() + "','" + id + "')");
            //        sb.Append(fengefu + "('" + id + "',");
            //        sb.Append("'" + getString(dt.Rows[i]["账号"]) + "',");
            //        sb.Append("'" + getString(dt.Rows[i]["员工编号"]) + "',");
            //        sb.Append("'" + getString(dt.Rows[i]["姓名"]) + "',");
            //        sb.Append("'123456',");
            //        sb.Append("'" + getString(dt.Rows[i]["手机"]) + "',");
            //        sb.Append("'" + getString(dt.Rows[i]["办公电话"]) + "',");
            //        sb.Append("'" + getString(dt.Rows[i]["邮箱"]) + "',");
            //        sb.Append("'" + getString(dt.Rows[i]["访问IP"]) + "',");
            //        if (dt.Rows[i]["性别"] != null && dt.Rows[i]["性别"].ToString() == "男")
            //        {
            //            sb.Append("1,");
            //        }
            //        else
            //        {
            //            sb.Append("0,");
            //        }
            //        if (dt.Rows[i]["账号类型"] != null && dt.Rows[i]["账号类型"].ToString() == "PTR账号")
            //        {
            //            sb.Append("'1',");
            //        }
            //        else
            //        {
            //            sb.Append("'0',");
            //        }
            //        sb.Append("1,'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',");
            //        sb.Append("'" + getString(dt.Rows[i]["备注"]) + "')");
            //        fengefu = ",";
            //    }
            //    else
            //    {
            //        foreach (var item in rows)
            //        {
            //            string sql = "update  ts_uidp_org set ";
            //            sql += " USER_DOMAIN='" + getString(dt.Rows[i]["组织机构编码"]) + "',";
            //            sql += " USER_CODE='" + getString(dt.Rows[i]["组织机构编码"]) + "',";
            //            sql += " USER_NAME='" + getString(dt.Rows[i]["组织机构编码"]) + "',";
            //            sql += " USER_PASS='******',";
            //            sql += " PHONE_MOBILE='" + getString(dt.Rows[i]["组织机构编码"]) + "',";
            //            sql += " PHONE_OFFICE='" + getString(dt.Rows[i]["组织机构编码"]) + "',";
            //            sql += " USER_EMAIL='" + getString(dt.Rows[i]["组织机构名称"]) + "',";
            //            sql += " USER_IP='" + getString(dt.Rows[i]["组织机构简称"]) + "',";
            //            sql += " USER_SEX='" + getString(dt.Rows[i]["上级组织机构编码"]) + "',";
            //            sql += " AUTHENTICATION_TYPE='" + getString((row["是否有效"] != null && row["是否有效"].ToString() == "是") ? 1 : 0) + "',";
            //            sql += " FLAG='" + getString(row["上级组织机构编码"]) + "',";
            //            sql += " REG_TIME='" + getString(row["上级组织机构编码"]) + "',";
            //            sql += " REMARK='" + getString(row["备注"]) + "'";
            //            sql += " where USER_ID='" + item["USER_ID"].ToString() + "' ;";
            //            list.Add(sql);
            //        }
            //    }
            //    //sqllst.Add(sb.ToString());
            //}
            //if (sb.Length > 0)
            //{
            //    sb.Insert(0, " INSERT INTO ts_uidp_userinfo(USER_ID,USER_DOMAIN,USER_CODE,USER_NAME,USER_PASS,PHONE_MOBILE,PHONE_OFFICE," +
            //    "USER_EMAIL,USER_IP,USER_SEX,AUTHENTICATION_TYPE,FLAG,REG_TIME,REMARK) values  ");
            //    sqllst.Add(sb.ToString());
            //}
            //if (sbOrgUser.Length > 0)
            //{
            //    sb.Insert(0, " insert into ts_uidp_org_user(ORG_ID,USER_ID)values ");
            //    sqllst.Add(sb.ToString());
            //}

            //string sqlUpdate = "update ts_uidp_org_user a ,ts_uidp_org b set a.ORG_ID = b.ORG_ID where a.ORG_ID = b.ORG_CODE";

            //list.Add(sbOrgUser.ToString());
            //list.Add(sb.ToString());
            //list.Add(sqlUpdate);
            //return db.UploadUserFile(list);
        }
示例#7
0
        public string UploadOrgFileNew(string filePath)
        {
            string    modePath = System.IO.Directory.GetCurrentDirectory() + "\\ExcelModel\\组织机构.xls"; //原始文件
            string    path     = filePath;                                                             //原始文件
            string    mes      = "";
            DataTable orgdt    = db.fetchOrgList();
            DataTable dt       = new DataTable();

            UTILITY.ExcelTools tool = new UTILITY.ExcelTools();
            tool.GetDataTable(System.IO.File.OpenRead(path), path, modePath, ref mes, ref dt);

            if (dt == null || dt.Rows.Count == 0)
            {
                return("空数据,导入失败!");
            }
            DataView dv = new DataView(dt);

            if (dt.Rows.Count != dv.ToTable(true, "组织机构编码").Rows.Count)
            {
                return("组织机构编码存在重复数据,导入失败!");
            }
            List <string> sqllst = new List <string>();

            int truckNum = Convert.ToInt32(Convert.ToDecimal(dt.Rows.Count / 500));
            int yushu    = dt.Rows.Count % 500;

            if (yushu > 0)
            {
                truckNum++;
            }

            for (int i = 1; i < truckNum + 1; i++)
            {
                //StringBuilder sb = new StringBuilder();
                string str      = "insert into ts_uidp_org (ORG_ID,ORG_CODE,ORG_NAME,ORG_SHORT_NAME,ORG_CODE_UPPER,ISINVALID,ISDELETE,REMARK,DW_CODE) values ";
                string fengefu  = "";
                int    rowbegin = (i - 1) * 500;
                int    rowend   = i * 500;
                if (rowend > dt.Rows.Count)
                {
                    rowend = dt.Rows.Count;
                }
                for (int j = rowbegin; j < rowend; j++)
                //foreach (DataRow row in dt.Rows)
                {
                    var orgname    = getString(dt.Rows[j]["组织机构简称"]);
                    var allorgname = getString(dt.Rows[j]["组织机构名称"]);
                    var dtt        = orgdt;
                    //DataRow[] rows = orgdt.Select("ORG_SHORT_NAME='" + getString(dt.Rows[j]["组织机构简称"])+ "'");
                    DataRow[] rows = orgdt.Select("ORG_CODE='" + getString(dt.Rows[j]["组织机构编码"]) + "' and ORG_CODE_UPPER='" + getString(dt.Rows[j]["上级组织机构编码"]) + "'");
                    if (rows.Length == 0)
                    {
                        StringBuilder sb = new StringBuilder(str);
                        sb.Append(fengefu + "('" + Guid.NewGuid().ToString() + "',");
                        sb.Append("'" + getString(dt.Rows[j]["组织机构编码"]) + "',");
                        sb.Append("'" + getString(dt.Rows[j]["组织机构名称"]) + "',");
                        sb.Append("'" + getString(dt.Rows[j]["组织机构简称"]) + "',");
                        sb.Append("'" + getString(dt.Rows[j]["上级组织机构编码"]) + "',");
                        if (dt.Rows[j]["是否有效"] != null && dt.Rows[j]["是否有效"].ToString() == "是")
                        {
                            sb.Append("'1',");
                        }
                        else
                        {
                            sb.Append("'0',");
                        }
                        sb.Append("'1',");
                        sb.Append("'" + getString(dt.Rows[j]["备注"]) + "','");
                        sb.Append(getString(dt.Rows[j]["单位编码"]) + "')");
                        //fengefu = ",";
                        sqllst.Add(sb.ToString());
                    }
                    else
                    {
                        foreach (var item in rows)
                        {
                            string sql = "update  ts_uidp_org set ";
                            sql += " ORG_CODE='" + getString(dt.Rows[j]["组织机构编码"]) + "',";
                            sql += " ORG_NAME='" + getString(dt.Rows[j]["组织机构名称"]) + "',";
                            sql += " ORG_SHORT_NAME='" + getString(dt.Rows[j]["组织机构简称"]) + "',";
                            //sql += " ORG_ID_UPPER='" + getString(d["parentId"]) + "',";
                            sql += " ORG_CODE_UPPER='" + getString(dt.Rows[j]["上级组织机构编码"]) + "',";
                            sql += " ISINVALID='" + getString((dt.Rows[j]["是否有效"] != null && dt.Rows[j]["是否有效"].ToString() == "是") ? 1 : 0) + "',";
                            sql += " REMARK='" + getString(dt.Rows[j]["备注"]) + "',";
                            sql += " DW_CODE='" + getString(dt.Rows[j]["单位编码"]) + "'";
                            sql += " where ORG_ID='" + item["ORG_ID"].ToString() + "'";
                            sqllst.Add(sql);
                        }
                    }
                    //sqllst.Add(sb.ToString());
                }
                //if (sb.Length > 0)
                //{
                //    sb.Insert(0, " insert into ts_uidp_org (ORG_ID,ORG_CODE,ORG_NAME,ORG_SHORT_NAME,ORG_CODE_UPPER,ISINVALID,ISDELETE,REMARK) values ");
                //}
                ////sb.Append(tempsb);
                //if (sb != null && sb.Length > 0)
                //{
                //    sqllst.Add(sb.ToString());
                //}
            }

            if (db.GetDBType() == "MYSQL")
            {
                string sql = @"update ts_uidp_org a,ts_uidp_org b set a.ORG_ID_UPPER=b.ORG_ID
                where a.ORG_CODE_UPPER=b.ORG_CODE";
                sqllst.Add(sql);
            }
            else if (db.GetDBType() == "SQLSERVER")
            {
                string sql = @"update a set a.ORG_ID_UPPER = b.ORG_ID from ts_uidp_org a,ts_uidp_org b
                where a.ORG_CODE_UPPER = b.ORG_CODE";
                sqllst.Add(sql);
            }
            else if (db.GetDBType() == "ORACLE")
            {
                string sql = @"UPDATE TS_UIDP_ORG a SET a.ORG_ID_UPPER =(SELECT ORG_ID FROM TS_UIDP_ORG b WHERE a.ORG_CODE_UPPER=b.ORG_CODE)";
                sqllst.Add(sql);
            }
            //sqllst.Add(sb.ToString());
            //return truck(1000, sqllst);
            return(db.UploadOrgFileList(sqllst));
        }