private string ImportPublic(int importId, string importFolder, string sourceFilePath)
{
logger.Debug("Importing Public data");
var done = CopyItem(importId, importFolder, sourceFilePath, XEnum.ImportItemType.Public);
if (!done)
{
logger.Debug("Source file not provided");
return(""); // Do nothing if user hasn't select a file for this table
}
// Import to database
string targetFilePath = importFolder + "\\Processed\\" + targetFileNames[(int)XEnum.ImportItemType.Public];
var excelColumns = "[分行名称], [支行名称], [客户姓名], [借款人企业性质], [组织机构代码], [合同编号], [借据编号], [借据开始日期], [借据结束日期], [行业门类], [行业大类], [行业中类], [行业小类], [贷款期限(月)], [币种], [发放后投向行业门类], [发放后投向行业大类], [发放后投向行业中类], [发放后投向行业小类], [业务类别], [授信品种], [核算项目名称], [七级分类], [客户信用等级], [客户规模(行内）], [客户规模(行外）], [本金逾期天数], [欠息天数], [贷款余额], [利率], [浮动利率], [主要担保方式], [保证金比例], [正常余额], [逾期余额], [非应计余额], [贷款账号], [是否涉农], [是否政府融资平台]";
var dbColumns = "OrgName, OrgName2, CustomerName, OrgType, OrgCode, ContractNo, DueBillNo, LoanStartDate, LoanEndDate, IndustryType1, IndustryType2, IndustryType3, IndustryType4, TermMonth, CurrencyType, Direction1, Direction2, Direction3, Direction4, OccurType, BusinessType, SubjectNo, ClassifyResult, CreditLevel, MyBankIndTypeName, ScopeName, OverdueDays, OweInterestDays, Balance1, ActualBusinessRate, RateFloat, VouchTypeName, BailRatio, NormalBalance, OverdueBalance, BadBalance, LoanAccount, IsAgricultureCredit, IsINRZ";
var result = "";
var table = SourceTable.GetById(XEnum.ImportItemType.Public);
for (int sheetIndex = 1; sheetIndex <= table.Sheets.Count; sheetIndex++)
{
result = ImportTable(importId, targetFilePath, XEnum.ImportItemType.Public, excelColumns, dbColumns, "PublicType", sheetIndex, sheetIndex);
if (!String.IsNullOrEmpty(result))
{
return(result);
}
}
if (string.IsNullOrEmpty(result))
{
result = AssignOrgId(importId, XEnum.ImportItemType.Public);
}
return(result);
}
private string ImportWjflSF(int importId, string importFolder)
{
logger.Debug("Importing WjflSF data");
// Import to database
string targetFilePath = importFolder + "\\Processed\\" + targetFileNames[(int)XEnum.ImportItemType.WjflSF];
var table = SourceTable.GetById(XEnum.ImportItemType.WjflSF);
for (int sheetIndex = 1; sheetIndex <= table.Sheets.Count; sheetIndex++)
{
var excelColumns = "[行名], [客户名称], [贷款余额], [违约金额], [七级分类], [欠息金额], [放款日期], [到期日期], [逾期天数], [欠息天数], [担保方式], [行业], [客户类型], [贷款类型], [是否本月新增], [备注]";
var dbColumns = "OrgName, CustomerName, CapitalAmount, OweCapital, DangerLevel, OweInterestAmount, LoanStartDate, LoanEndDate, OverdueDays, OweInterestDays, DanBaoFangShi, Industry, CustomerType, LoanType, IsNew, [Comment]";
if (sheetIndex > 1)
{
excelColumns = excelColumns.Replace(", [违约金额]", "");
dbColumns = dbColumns.Replace(", OweCapital", "");
}
var result = ImportTable(importId, targetFilePath, XEnum.ImportItemType.WjflSF, excelColumns, dbColumns, "WjflType", sheetIndex, sheetIndex);
if (!String.IsNullOrEmpty(result))
{
return(result);
}
}
logger.Debug("Importing WjflSF done");
logger.Debug("Assigning OrgId to WjflSF");
var dao = new SqlDbHelper();
var sql = string.Format("UPDATE ImportWjflSF SET OrgId = dbo.sfGetOrgId(OrgName) WHERE ImportId = {0}", importId);
var count = dao.ExecuteNonQuery(sql);
logger.DebugFormat("Done. {0} rows affected", count);
return("");
}
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);
}
