private string GetYWTargetFileName(XEnum.ImportItemType itemType, int orgId)
{
string fileName = targetFileNames[(int)itemType];
var dotIndex = fileName.LastIndexOf('.');
fileName = string.Format("{0}_{1}{2}", fileName.Substring(0, dotIndex), orgId, fileName.Substring(dotIndex));
return(fileName);
}
private string GetTableSuffix(XEnum.ImportItemType itemType)
{
var suffix = itemType.ToString();
var startAt = suffix.LastIndexOf('.');
suffix = suffix.Substring(startAt + 1);
return(suffix);
}
protected override string GetImportWhereSql(XEnum.ImportItemType itemType)
{
if (itemType == XEnum.ImportItemType.LoanSF)
{
return("WHERE [贷款状态] <> '结清'");
}
else
{
return(base.GetImportWhereSql(itemType));
}
}
private string AssignOrgId(int importId, XEnum.ImportItemType itemType)
{
if (!(itemType == XEnum.ImportItemType.Loan || itemType == XEnum.ImportItemType.Public || itemType == XEnum.ImportItemType.Private))
{
return("Invalid import item: " + itemType.ToString());
}
try {
var dao = new SqlDbHelper();
var sql = new StringBuilder();
int count = 0;
var suffix = GetTableSuffix(itemType);
logger.Debug("Assigning OrgId column to " + suffix);
sql.AppendLine("UPDATE Import" + suffix);
if (itemType == XEnum.ImportItemType.Loan)
{
sql.AppendLine("SET OrgId = dbo.sfGetOrgId(OrgNo)");
}
else
{
sql.AppendLine("SET OrgId = dbo.sfGetOrgId(OrgName2)");
}
sql.AppendLine("WHERE ImportId = {0} AND OrgId IS NULL");
count = dao.ExecuteNonQuery(string.Format(sql.ToString(), importId));
logger.DebugFormat("Done ({0} affected)", count);
if (itemType == XEnum.ImportItemType.Loan)
{
logger.Debug("Assigning OrgId4Report column to " + suffix);
sql.Clear();
sql.AppendLine("UPDATE ImportLoan");
sql.AppendLine("SET OrgId4Report = CASE WHEN OrgId IN (1, 2) THEN (CASE WHEN LEN(CustomerName) >= 5 THEN 1 ELSE 2 END) ELSE OrgId END");
sql.AppendLine("WHERE ImportId = {0} AND OrgId4Report IS NULL");
count = dao.ExecuteNonQuery(string.Format(sql.ToString(), importId));
logger.DebugFormat("Done ({0} affected)", count);
}
}
catch (Exception ex) {
return(ex.Message);
}
return(string.Empty);
}
private string ImportYW(XEnum.ImportItemType itemType, int importId, string importFolder, string sourceFilePath)
{
logger.DebugFormat("Importing {0} data", itemType.ToString());
var result = "";
var filePathes = sourceFilePath.Split('|');
for (int i = 0; i < filePathes.Length; i++)
{
var filePath = filePathes[i];
if (string.IsNullOrEmpty(filePath))
{
logger.Debug("Source file not provided");
continue;
}
var orgId = GetOrgId4YW(filePath);
// Do this check before any action
if (orgId == 0)
{
var msg = "不能确定业务状况表数据的所属银行：" + filePath;
logger.Error(msg);
return(msg);
}
var done = CopyItem(importId, importFolder, filePath, itemType);
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\\" + GetYWTargetFileName(itemType, orgId);
var excelColumns = "*";
var dbColumns = "SubjectCode, SubjectName, LastDebitBalance, LastCreditBalance, CurrentDebitChange, CurrentCreditChange, CurrentDebitBalance, CurrentCreditBalance";
result = ImportTable(importId, targetFilePath, itemType, excelColumns, dbColumns, "OrgId", orgId, 1, i + 1);
if (!string.IsNullOrEmpty(result))
{
return(result);
}
}
return(result);
}
protected virtual string GetImportWhereSql(XEnum.ImportItemType itemType)
{
var sql = "";
if (itemType == XEnum.ImportItemType.Loan)
{
sql = "WHERE [贷款状态] <> '结清'";
}
else if (itemType == XEnum.ImportItemType.Public)
{
sql = "WHERE [分行名称] LIKE '%长安银行榆林分行%'";
}
else if (itemType == XEnum.ImportItemType.Private)
{
sql = "WHERE [二级分行] LIKE '%长安银行榆林分行%'";
}
else if (itemType == XEnum.ImportItemType.YWNei || itemType == XEnum.ImportItemType.YWWai)
{
sql = "WHERE LEN([科目代号]) > 2";
}
return(sql);
}
public static SourceTable GetById(XEnum.ImportItemType sourceType)
{
return(GetById((int)sourceType));
}
public static AI_ImportColumn GetByAlias(XEnum.ImportItemType itemType, string alias)
{
return(GetByAlias((int)itemType, alias));
}
public static List <AI_ImportColumn> GetList(XEnum.ImportItemType itemType)
{
return(GetList((int)itemType));
}
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);
}
protected string ImportTable(int importId, string filePath, XEnum.ImportItemType itemType, string excelColumns, string dbColumns, int sheetIndex = 1, int roundIndex = 1)
{
return(ImportTable(importId, filePath, itemType, excelColumns, dbColumns, null, null, sheetIndex, roundIndex));
}
protected bool CopyItem(int importId, string importFolder, string sourceFilePath, XEnum.ImportItemType itemType)
{
int itemTypeId = (int)itemType;
if (sourceFilePath.Length == 0 || !File.Exists(sourceFilePath))
{
return(false);
}
string targetFileName = this.targetFileNames[itemTypeId];
if (itemType == XEnum.ImportItemType.YWNei || itemType == XEnum.ImportItemType.YWWai || itemType == XEnum.ImportItemType.Loan)
{
var orgId = GetOrgId4YW(sourceFilePath);
targetFileName = GetYWTargetFileName(itemType, orgId);
}
//Original
var originalFolder = importFolder + @"\Original\";
if (!Directory.Exists(originalFolder))
{
Directory.CreateDirectory(originalFolder);
}
File.Copy(sourceFilePath, originalFolder + @"\" + targetFileName, true);
//Processed
var processedFolder = importFolder + @"\Processed\";
if (!Directory.Exists(processedFolder))
{
Directory.CreateDirectory(processedFolder);
}
File.Copy(sourceFilePath, processedFolder + @"\" + targetFileName, true);
logger.Debug("Process copied item for " + itemType.ToString());
ExcelHelper.ProcessCopiedItem(processedFolder + @"\" + targetFileName, itemType);
logger.Debug("Updating ImportItem table");
var dao = new SqlDbHelper();
var sql = new StringBuilder();
sql.AppendFormat("SELECT ISNULL(MAX(Id), 0) FROM ImportItem WHERE ImportId = {0} AND ItemType = {1}", importId, itemTypeId);
var importItemId = (int)dao.ExecuteScalar(sql.ToString());
if (importItemId == 0)
{
sql.Clear();
sql.AppendLine(string.Format("INSERT INTO ImportItem (ImportId, ItemType, FilePath) VALUES ({0}, {1}, '{2}')", importId, itemTypeId, sourceFilePath));
sql.AppendLine("SELECT SCOPE_IDENTITY()");
importItemId = (int)((decimal)dao.ExecuteScalar(sql.ToString()));
logger.Debug("New record created. ImportItemId = " + importItemId.ToString());
}
else
{
sql.Clear();
sql.AppendFormat("UPDATE ImportItem SET FilePath = '{0}', ModifyDate = getdate() WHERE Id = {1}", sourceFilePath, importItemId);
dao.ExecuteNonQuery(sql.ToString());
logger.Debug("Existing record updated. ImportItemId = " + importItemId.ToString());
}
return(true);
}
