Exemplo n.º 1
0
        protected string ImportTable(int importId, string filePath, XEnum.ImportItemType itemType, string excelColumns, string dbColumns, string dbColumns2, object dbValues2, int sheetIndex = 1, int roundIndex = 1)
        {
            int    columnCount = dbColumns.Split(',').Length;
            string suffix      = GetTableSuffix(itemType);

            logger.DebugFormat("Importing {0} to database", suffix);
            if (String.IsNullOrWhiteSpace(filePath) || !File.Exists(filePath))
            {
                return(string.Format("File {0} cannot be found", filePath ?? "<empty>"));
            }
            logger.Debug("Getting source table");
            var sourceTable   = SourceTable.GetById(itemType);
            var sheetEntry    = sourceTable.Sheets[sheetIndex - 1];
            var dataRowEnding = sheetEntry.DataRowEndingFlag;

            logger.DebugFormat("Ending is {0}", dataRowEnding == "" ? "empty string" : dataRowEnding);

            var             oleOpened = false;
            OleDbConnection oconn     = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");

            try {
                logger.Debug("Opening connection to " + filePath);
                oconn.Open();
                oleOpened = true;
                logger.Debug("Opened");

                DataTable dt     = oconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string    sheet1 = dt.Rows[(sheetIndex - 1) * 2][2].ToString();
                if (!IsSheetMatched(sheet1, sheetEntry.Name))
                {
                    logger.WarnFormat("Sheet \"{0}\" is not found at index {1}. This may be caused by extra sheets added. Searching in all sheets...", sheetEntry.Name, sheetIndex);
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        sheet1 = dt.Rows[i][2].ToString();
                        if (IsSheetMatched(sheet1, sheetEntry.Name))
                        {
                            logger.WarnFormat("Got sheet \"{0}\"", sheet1.Substring(0, sheet1.Length - 1));
                            break;
                        }
                    }
                }
                if (!IsSheetMatched(sheet1, sheetEntry.Name))
                {
                    var msg = string.Format("没有找到工作表\"{0}\"", sheetEntry.Name);
                    logger.Error(msg);
                    return(msg);
                }

                logger.Debug("Importing sheet " + sheet1.Substring(0, sheet1.Length - 1));

                var sql = new StringBuilder();
                sql.AppendFormat("SELECT {0} FROM [{1}]", excelColumns, sheet1);
                sql.AppendLine(GetImportWhereSql(itemType));
                var             s      = sql.ToString();
                OleDbCommand    ocmd   = new OleDbCommand(s, oconn);
                OleDbDataReader reader = ocmd.ExecuteReader();

                int dataRowIndex = 0;
                var dao          = new SqlDbHelper();
                if (sheetIndex == 1 && roundIndex == 1)                   // Delete existing records only when importing the first sheet
                {
                    dao.ExecuteNonQuery(string.Format("DELETE FROM Import{0} WHERE ImportId = {1}", suffix, importId));
                }

                sql.Clear();
                while (reader.Read())
                {
                    if (DataUtility.GetValue(reader, 0).Equals(dataRowEnding))                       // Going to end
                    {
                        break;
                    }
                    dataRowIndex++;
                    sql.AppendLine(GetInsertSql(reader, importId, suffix, columnCount, dbColumns, dbColumns2, dbValues2));
                    // Top 1 trial for exception track
                    if (dataRowIndex == 1)
                    {
                        try {
                            dao.ExecuteNonQuery(sql.ToString());
                            sql.Clear();
                        }
                        catch (Exception ex) {
                            logger.Error("Running INSERT: " + sql.ToString(), ex);
                            throw ex;
                        }
                    }
                    // Batch inserts
                    if (dataRowIndex > 1 && dataRowIndex % 1000 == 0)
                    {
                        dao.ExecuteNonQuery(sql.ToString());
                        sql.Clear();
                    }
                }
                if (sql.Length > 0)
                {
                    try {
                        dao.ExecuteNonQuery(sql.ToString());
                        sql.Clear();
                    }
                    catch (Exception ex) {
                        logger.Error("Running INSERT: " + sql.ToString(), ex);
                        throw ex;
                    }
                }
                logger.DebugFormat("{0} records imported.", dataRowIndex);

                if (itemType == XEnum.ImportItemType.Loan)
                {
                    // Cleanup zero-records since the oledb query cannot filter them out
                    var query   = string.Format("DELETE FROM ImportLoan WHERE ImportId = {0} AND ISNULL(CapitalAmount + OweCapital + OweYingShouInterest + OweCuiShouInterest, 0) = 0", importId);
                    var cleaned = dao.ExecuteNonQuery(query);
                    logger.DebugFormat("{0} records have been cleaned because of 4-zeros.", cleaned);
                }
            }
            catch (Exception ex) {
                logger.Error("Outest catch: ", ex);
                throw ex;
            }
            finally {
                if (oleOpened)
                {
                    oconn.Close();
                }
            }
            return(string.Empty);
        }
Exemplo n.º 2
0
        private string UpdateWJFLSheet(int importId, OleDbDataReader reader)
        {
            var result = string.Empty;

            try {
                int readRows        = 0;
                int updatedRows     = 0;
                int failedRows      = 0;
                var sql             = new StringBuilder();
                var sqlSingle       = "";
                var firstColumn     = "";
                var dao             = new SqlDbHelper();
                var failedCustomers = new StringBuilder();

                while (reader.Read())
                {
                    firstColumn = DataUtility.GetValue(reader, 0);
                    if (string.IsNullOrEmpty(firstColumn) || firstColumn.Replace(" ", "").Equals("合计"))                       // Going to end
                    {
                        break;
                    }
                    readRows++;
                    sql.Clear();
                    //替换掉下面两行,解决五级分类中营业部与公司部混乱的问题
                    //sql.AppendLine("SELECT Id FROM ImportLoan");
                    //sql.AppendLine("WHERE OrgId = dbo.sfGetOrgId('{0}')");
                    sql.AppendLine("SELECT L.Id FROM ImportLoan L INNER JOIN Org O ON L.OrgId = O.Id");
                    sql.AppendLine("WHERE O.OrgNo = (SELECT OrgNo FROM Org WHERE Id = dbo.sfGetOrgId('{0}'))");
                    sql.AppendLine("	AND CustomerName = '{1}'");
                    sql.AppendLine("	AND CapitalAmount = {2}");
                    if (!string.IsNullOrEmpty(DataUtility.GetValue(reader, 3)))
                    {
                        sql.AppendLine("	AND LoanStartDate = '{3}'");
                    }
                    if (!string.IsNullOrEmpty(DataUtility.GetValue(reader, 4)))
                    {
                        sql.AppendLine("	AND LoanEndDate = '{4}'");
                    }
                    sql.AppendLine("	AND ImportId = '{5}'");
                    sqlSingle = string.Format(sql.ToString(), DataUtility.GetValue(reader, 0), DataUtility.GetValue(reader, 1), DataUtility.GetValue(reader, 2), DataUtility.GetValue(reader, 3), DataUtility.GetValue(reader, 4), importId);
                    var o = dao.ExecuteScalar(sqlSingle);
                    if (o == null)
                    {
                        failedRows++;
                        if (failedRows <= 10)
                        {
                            var msg = GetMismatchMessage(importId, DataUtility.GetValue(reader, 0), DataUtility.GetValue(reader, 1), DataUtility.GetValue(reader, 2), DataUtility.GetValue(reader, 3), DataUtility.GetValue(reader, 4));
                            failedCustomers.AppendLine(msg + "\r\n" + new string('-', 60));
                            logger.WarnFormat("No record matched for {0}-{1}-{2}-{3}-{4}", DataUtility.GetValue(reader, 0), DataUtility.GetValue(reader, 1), DataUtility.GetValue(reader, 2), DataUtility.GetValue(reader, 3), DataUtility.GetValue(reader, 4));
                        }
                        else
                        {
                            failedCustomers.AppendFormat("还有更多……\r\n", DataUtility.GetValue(reader, 1), DataUtility.GetValue(reader, 2), DataUtility.GetValue(reader, 3), DataUtility.GetValue(reader, 4));
                            logger.Warn("Stopped because of more un-matched records.");
                            break;
                        }
                    }
                    else
                    {
                        int loanId = (int)o;
                        sqlSingle = string.Format("UPDATE ImportLoan SET DangerLevel = '{0}' WHERE Id = {1} AND ISNULL(DangerLevel, '') != '{0}'", DataUtility.GetValue(reader, 5), loanId);
                        try {
                            var affected = dao.ExecuteNonQuery(sqlSingle);
                            updatedRows += affected;
                            if (affected > 0)
                            {
                                logger.DebugFormat("#{0} update to '{1}'", loanId, DataUtility.GetValue(reader, 5));
                            }
                        }
                        catch (Exception ex) {
                            logger.Error("Running: " + sql.ToString(), ex);
                            throw ex;
                        }
                    }
                }
                logger.DebugFormat("Rows read in toal: {0}", readRows);
                logger.DebugFormat("Rows updated: {0}", updatedRows);
                logger.DebugFormat("Rows not match: {0}", failedRows);
                if (failedRows == 1)
                {
                    result = Consts.MESSAGE_FORM_PREFIX + "下面客户的五级分类无法导入:\r\n" + failedCustomers.ToString() + "\r\n请确保新修改的五级分类Excel文件中,该客户的贷款余额、放款日期和到期日期格式正确。";
                }
                else if (failedRows > 1)
                {
                    result = Consts.MESSAGE_FORM_PREFIX + "下列客户的五级分类无法导入:\r\n" + (new string('-', 80)) + "\r\n" + failedCustomers.ToString() + "\r\n" + (new string('-', 80)) + "\r\n请确保新修改的五级分类Excel文件中,他们的贷款余额、放款日期和到期日期格式正确。";
                }
            }
            catch (Exception ex) {
                logger.Error("Outest catch", ex);
                return(ex.Message);
            }
            return(result);
        }