public void TestCreateSQL_WithJoin_SecondLevel_TwoBranches()
{
//-------------Setup Test Pack ------------------
Source source = new Source("MySource", "MY_SOURCE");
Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE");
Source branch1 = new Source("JoinBranch1", "MY_BRANCH_1");
Source branch2 = new Source("JoinBranch2", "MY_BRANCH_2");
Source.Join join = CreateAndAddJoin(source, joinSource);
Source.Join branchJoin1 = CreateAndAddJoin(joinSource, branch1);
Source.Join branchJoin2 = CreateAndAddJoin(joinSource, branch2);
SourceDB sourceDB = new SourceDB(source);
//-------------Execute test ---------------------
string sql = sourceDB.CreateSQL(GetSqlFormatter(), CreateAliases(source, joinSource, branch1, branch2));
//-------------Test Result ----------------------
Source.Join.JoinField joinField = join.JoinFields[0];
Source.Join.JoinField joinFieldBranch1 = branchJoin1.JoinFields[0];
Source.Join.JoinField joinFieldBranch2 = branchJoin2.JoinFields[0];
string expectedSql = string.Format("((({0} a1 JOIN {1} a2 ON a1.{4} = a2.{5}) JOIN {2} a3 ON a2.{6} = a3.{7}) JOIN {3} a4 ON a2.{8} = a4.{9})",
sourceDB.EntityName, joinSource.EntityName, branch1.EntityName, branch2.EntityName,
joinField.FromField.FieldName, joinField.ToField.FieldName,
joinFieldBranch1.FromField.FieldName, joinFieldBranch1.ToField.FieldName,
joinFieldBranch2.FromField.FieldName, joinFieldBranch2.ToField.FieldName);
Assert.AreEqual(expectedSql, sql);
}
public void ResetSourceDB()
{
SourceDB sourceDB = new SourceDB();
sourceDB.RunSQLScript("Reset");
Console.WriteLine("Resetted SourceDB");
}
public void TestCreateSQL_WithJoin_NoFields()
{
//-------------Setup Test Pack ------------------
string tableName = "MY_SOURCE";
Source source = new Source("MySource", tableName);
string joinTableName = "MY_JOINED_TABLE";
Source joinSource = new Source("JoinSource", joinTableName);
Source.Join join = new Source.Join(source, joinSource);
source.Joins.Add(join);
SourceDB sourceDB = new SourceDB(source);
//-------------Execute test ---------------------
Exception exception = null;
try
{
sourceDB.CreateSQL(GetSqlFormatter(), CreateAliases(source, joinSource));
} catch (Exception ex)
{
exception = ex;
}
//-------------Test Result ----------------------
Assert.IsNotNull(exception, "An error was expected when creating SQL with joins that have no fields");
Assert.IsInstanceOf(typeof(HabaneroDeveloperException), exception);
string expectedMessage = string.Format("SQL cannot be created for the source '{0}' because it has a join to '{1}' without join fields",
sourceDB.Name, join.ToSource.Name);
StringAssert.Contains(expectedMessage, exception.Message);
}
public void TestCreateSQL_WithJoin_TwoFields()
{
//-------------Setup Test Pack ------------------
Source source = new Source("MySource", "MY_SOURCE");
Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE");
Source.Join join = CreateAndAddJoin(source, joinSource);
QueryField fromField = new QueryField("FromField2", "FROM_FIELD2", source);
QueryField toField = new QueryField("ToField2", "TO_FIELD2", joinSource);
join.JoinFields.Add(new Source.Join.JoinField(fromField, toField));
Source.Join.JoinField joinField1 = join.JoinFields[0];
Source.Join.JoinField joinField2 = join.JoinFields[1];
SourceDB sourceDB = new SourceDB(source);
//-------------Execute test ---------------------
string sql = sourceDB.CreateSQL(GetSqlFormatter(), CreateAliases(source, joinSource));
//-------------Test Result ----------------------
string expectedSql = string.Format("({0} a1 JOIN {1} a2 ON a1.{2} = a2.{3} AND a1.{4} = a2.{5})",
source.EntityName, joinSource.EntityName,
joinField1.FromField.FieldName, joinField1.ToField.FieldName,
joinField2.FromField.FieldName, joinField2.ToField.FieldName);
Assert.AreEqual(expectedSql, sql);
}
public int selectID(string sourceName)
{
sourceDB = new SourceDB();
source = new Source();
source.setName(sourceName);
return(sourceDB.selectSourceId(source));
}
public void TestCreateSQL_WithInheritanceJoin_TwoLevels()
{
//-------------Setup Test Pack ------------------
Source source = new Source("MySource", "MY_SOURCE");
Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE");
Source joinSource2 = new Source("JoinSource2", "MY_JOINED_TABLE_2");
Source.Join join = CreateAndAddInheritanceJoin(source, joinSource);
Source.Join join2 = CreateAndAddInheritanceJoin(joinSource, joinSource2);
SourceDB sourceDB = new SourceDB(source);
//-------------Execute test ---------------------
string sql = sourceDB.CreateSQL(GetSqlFormatter(), CreateAliases(source, joinSource, joinSource2));
//-------------Test Result ----------------------
Source.Join.JoinField joinField = join.JoinFields[0];
Source.Join.JoinField joinField2 = join2.JoinFields[0];
string expectedSql = string.Format("(({0} a1 JOIN {1} a2 ON a1.{2} = a2.{3}) JOIN {4} a3 ON a2.{5} = a3.{6})",
sourceDB.EntityName,
joinSource.EntityName, joinField.FromField.FieldName, joinField.ToField.FieldName,
joinSource2.EntityName, joinField2.FromField.FieldName, joinField2.ToField.FieldName);
Assert.AreEqual(expectedSql, sql);
}
public string selectSourceName(int ID)
{
source = new Source();
sourceDB = new SourceDB();
source.setID(ID);
sourceDB.selectSourceName(source);
return(source.getName());
}
private void BindSource()
{
List <Source> srcList = SourceDB.GetAllSource();
ddlSource.DataSource = srcList;
ddlSource.DataTextField = "NAME";
ddlSource.DataValueField = "ID";
ddlSource.DataBind();
}
public string getSourcePhone3(string sourceName)
{
source = new Source();
sourceDB = new SourceDB();
int ID = this.selectID(sourceName);
source.setID(ID);
sourceDB.selectPhone3(source);
return(source.getPhone3());
}
public string getSourceEmployee1(string sourceName)
{
source = new Source();
sourceDB = new SourceDB();
int ID = this.selectID(sourceName);
source.setID(ID);
sourceDB.selectEmployeeName1(source);
return(source.getEmployeeName1());
}
public string getSourceAddress(string sourceName)
{
source = new Source();
sourceDB = new SourceDB();
int ID = this.selectID(sourceName);
source.setID(ID);
sourceDB.selectSourceAdress(source);
return(source.getAddress());
}
public void TestCreateSQL_Simple()
{
//-------------Setup Test Pack ------------------
string tableName = "MY_SOURCE";
Source source = new Source("MySource", tableName);
SourceDB sourceDB = new SourceDB(source);
//-------------Execute test ---------------------
string sql = sourceDB.CreateSQL(GetSqlFormatter(), CreateAliases(source));
//-------------Test Result ----------------------
Assert.AreEqual(tableName + " a1", sql);
}
public void TestCreateSQL_Simple_WithDelimiter()
{
//-------------Setup Test Pack ------------------
const string tableName = "MY_SOURCE";
Source source = new Source("MySource", tableName);
SourceDB sourceDB = new SourceDB(source);
//-------------Execute test ---------------------
string sql = sourceDB.CreateSQL(new SqlFormatter("[", "]", "TOP", ""), CreateAliases(source));
//-------------Test Result ----------------------
Assert.AreEqual(string.Format("[{0}] a1", tableName), sql);
}
public void fillComboboxSourceName(ComboBox combobox)
{
combobox.Items.Clear();
connection = new DBConnection();
sourceDB = new SourceDB();
SqlDataReader reader = sourceDB.fillComboboxSourceName();
while (reader.Read())
{
combobox.Items.Add(reader["sourceName"]);
}
connection.close();
}
public void TestCreateSourceDB()
{
//-------------Setup Test Pack ------------------
Source source = new Source("MySource", "MY_SOURCE");
//-------------Execute test ---------------------
SourceDB sourceDB = new SourceDB(source);
//-------------Test Result ----------------------
Assert.AreEqual(source.Name, sourceDB.Name);
Assert.AreEqual(source.EntityName, sourceDB.EntityName);
Assert.AreEqual(source.Joins.Count, sourceDB.Joins.Count);
}
public void RecordDataMigrateJob(PerformContext context)
{
context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} RecordDataMigrateJob Running ...");
// 从SourceDB查询出符合条件的FinaSettlementlist
var finaSettlement10s = SourceDB.Queryable <Models.SourceDB.FinaSettlement>().Where(it => SqlFunc.Between(it.CreateDate, DateTime.Now.AddHours(-1), DateTime.Now)).ToList();
context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} RecordDataMigrateJob 查询SourceDB.AreaList 个数为" + finaSettlement10s.Count.ToString());
if (finaSettlement10s.Count == 0)
{
context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} 更新个数为" + finaSettlement10s.Count.ToString() + ",停止更新！");
}
else
{
var progressBar = context.WriteProgressBar();
var insertConnt = 0;
foreach (Models.SourceDB.FinaSettlement item in finaSettlement10s.WithProgress(progressBar))
{
Models.TargetDB.FinaSettlement targetItem = new Models.TargetDB.FinaSettlement();
targetItem.ID = item.ID;
targetItem.SerialNumber = item.SerialNumber;
targetItem.ClerkID = item.ClerkID;
targetItem.ClerkNum = item.ClerkNum;
targetItem.Date = item.CreateDate;
targetItem.Company = item.Company;
targetItem.Reckoner = item.Reckoner;
targetItem.Money = item.Money;
targetItem.ClearingForm = item.ClearingForm;
targetItem.OpeningBank = item.OpeningBank;
targetItem.Account = item.Account;
targetItem.PrintCount = item.PrintCount;
targetItem.PaymentWay = item.PaymentWay;
targetItem.Verifier = item.Verifier;
targetItem.Status = item.Status;
targetItem.Creater = item.Creater;
targetItem.CreateDate = item.CreateDate;
targetItem.UpdateDate = item.UpdateDate;
targetItem.Remark = item.Remark;
targetItem.FundContent = "0.0";
// 为什么一个一个插入都不行？
int insert = TargetDB.Insertable(targetItem).ExecuteCommand();
insertConnt += insert;
}
context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} RecordDataMigrateJob 插入TargetDB.AreaList 个数为" + insertConnt);
}
context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} TrySqlSugarJob Running Over ...");
}
/// <summary>
/// DataMigrateJob 数据迁移job
/// </summary>
/// <param name="context"></param>
//[RecurringJob("*/1 * * * *")]
//[System.ComponentModel.DisplayName("BaseDataMigrateJob")]
//[Queue("jobs")]
public void BaseDataMigrateJob(PerformContext context)
{
context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} DataMigrateJob Running ...");
var areaLists = SourceDB.Queryable <AreaList>().ToList();
context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} DataMigrateJob 查询SourceDB.AreaList 个数为" + areaLists.Count.ToString());
var deleteCount = TargetDB.Deleteable <AreaList>().ExecuteCommand();
context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} DataMigrateJob 删除TargetDB.AreaList 个数为" + deleteCount);
var insertConnt = TargetDB.Insertable(areaLists.ToArray()).ExecuteCommand();
context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} DataMigrateJob 插入TargetDB.AreaList 个数为" + insertConnt);
context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} TrySqlSugarJob Running Over ...");
}
public void updateSource(string previousName, string name, string address, string EmployeeName1, string EmployeeName2, string phone1, string phone2, string phone3)
{
source = new Source();
sourceDB = new SourceDB();
int ID = this.selectID(previousName);
source.setID(ID);
source.setName(name);
source.setEmployeeName1(EmployeeName1);
source.setEmployeeName2(EmployeeName2);
source.setAddress(address);
source.setPhone1(phone1);
source.setPhone2(phone2);
source.setPhone3(phone3);
sourceDB.update(source);
}
public void TestCreateSQL_LeftJoin()
{
//-------------Setup Test Pack ------------------
Source source = new Source("MySource", "MY_SOURCE");
Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE");
Source.Join join = CreateAndAddJoin(source, joinSource);
join.JoinType = Source.JoinType.LeftJoin;
SourceDB leftJoinSourceDB = new SourceDB(source);
//-------------Execute test ---------------------
string sql = leftJoinSourceDB.CreateSQL(GetSqlFormatter(), CreateAliases(source, joinSource));
//-------------Test Result ----------------------
StringAssert.Contains("LEFT JOIN", sql);
}
public void ProcessBatchFromSourceDBToResultDB(int amount)
{
int leftToDo = amount;
int batch = 50;
SourceDB sourceDB = new SourceDB();
int imageFilesInSourceDB = sourceDB.GetImageFilesCount();
ResultDB resultDB = new ResultDB();
int imageFilesInResultDB = resultDB.GetImageFilesCount();
int diffResultDBandSourceDB = imageFilesInSourceDB - imageFilesInResultDB;
if (diffResultDBandSourceDB == 0)
{
Console.WriteLine("No new images in sourceDB to process");
return;
}
while (leftToDo > 0)
{
if (leftToDo <= batch)
{
batch = leftToDo;
}
if (leftToDo > diffResultDBandSourceDB)
{
batch = diffResultDBandSourceDB;
}
if (batch == 0)
{
break;
}
CopyJpgFiles(ETLcontroller.sourceDBDataFolder, ETLcontroller.stageDBDataUnporssedFolder, batch);
InsertDataIntoStageDBFromUnprocessed();
ExtractMetadataIntoStageDB();
ImageRegonitionPrediction();
SortFilesInStageDBData();
MoveFilesFromStageDBDataToResultDBData();
LoadDataFromStageDBtoResultDB();
ResetStageDB();
leftToDo -= 50;
}
}
public void TestCreateSourceDB_WithJoins()
{
//-------------Setup Test Pack ------------------
Source source = new Source("MySource", "MY_SOURCE");
Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE");
Source.Join join = new Source.Join(source, joinSource);
source.Joins.Add(join);
//-------------Execute test ---------------------
SourceDB sourceDB = new SourceDB(source);
//-------------Test Result ----------------------
Assert.AreEqual(source.Name, sourceDB.Name);
Assert.AreEqual(source.EntityName, sourceDB.EntityName);
Assert.AreEqual(source.Joins, sourceDB.Joins);
}
public void TestCreateSQL_WithJoin()
{
//-------------Setup Test Pack ------------------
Source source = new Source("MySource", "MY_SOURCE");
Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE");
Source.Join join = CreateAndAddJoin(source, joinSource);
SourceDB sourceDB = new SourceDB(source);
//-------------Execute test ---------------------
string sql = sourceDB.CreateSQL(GetSqlFormatter(), CreateAliases(source, joinSource));
//-------------Test Result ----------------------
Source.Join.JoinField joinField = join.JoinFields[0];
string expectedSql = string.Format("({0} a1 JOIN {1} a2 ON a1.{2} = a2.{3})", source.EntityName, joinSource.EntityName,
joinField.FromField.FieldName, joinField.ToField.FieldName);
Assert.AreEqual(expectedSql, sql);
}
public void TestCreateSQL_WithJoin_WithDelimiter()
{
//-------------Setup Test Pack ------------------
Source source = new Source("MySource", "MY_SOURCE");
Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE");
Source.Join join = CreateAndAddJoin(source, joinSource);
SourceDB sourceDB = new SourceDB(source);
//-------------Execute test ---------------------
SqlFormatter myFormatter = new SqlFormatter("[", "]", "TOP", "");
string sql = sourceDB.CreateSQL(myFormatter, CreateAliases(source, joinSource));
//-------------Test Result ----------------------
Source.Join.JoinField joinField = join.JoinFields[0];
string expectedSql = string.Format("([{0}] a1 JOIN [{1}] a2 ON a1.[{2}] = a2.[{3}])", source.EntityName, joinSource.EntityName,
joinField.FromField.FieldName, joinField.ToField.FieldName);
Assert.AreEqual(expectedSql, sql);
}
public void CopyJpgFiles(string sourceFilePath, string destFilePath, int amount)
{
FileMover fileMover = new FileMover();
StreamReader sr = new StreamReader(StageDB.stageDBDataLog);
string line = sr.ReadLine();
int lastImageFileIndex = 0;
if (line != null)
{
lastImageFileIndex = Int32.Parse(line);
}
sr.Close();
for (int i = 1; i <= amount; i++)
{
SourceDB sourceDB = new SourceDB();
ImageFile imageFile = sourceDB.GetImageFile(lastImageFileIndex + i);
fileMover.CopyImageFile(imageFile, stageDBDataUnporssedFolder);
}
if (lastImageFileIndex == 0)
{
lastImageFileIndex = amount;
}
else
{
lastImageFileIndex += amount;
}
fileMover.UpdateTxtFile(lastImageFileIndex.ToString(), StageDB.stageDBDataLog);
//StageDB.stageDBDataLog
//fileMover.CopyJpgFiles(sourceFilePath, destFilePath, amount);
}
public Boolean insertSource(string name, string address, string EmployeeName1, string EmployeeName2, string phone1, string phone2, string phone3)
{
source = new Source();
sourceDB = new SourceDB();
source.setName(name);
source.setEmployeeName1(EmployeeName1);
source.setEmployeeName2(EmployeeName2);
source.setAddress(address);
source.setPhone1(phone1);
source.setPhone2(phone2);
source.setPhone3(phone3);
check = sourceDB.checkSource(source);
if (check == true)
{
return(false);
}
else
{
sourceDB.insert(source);
return(true);
}
}
public void Test_CreateSQL_ShouldUseAliasesInJoins()
{
//---------------Set up test pack-------------------
var mysource = new Source("mysource");
QueryField fieldOnMySource = new QueryField("testfield", "testfield", mysource);
Source joinedTableSource = new Source("myjoinedtosource");
QueryField fieldOnJoinedTableSource = new QueryField("testfield", "testfield", joinedTableSource);
mysource.Joins.Add(new Source.Join(mysource, joinedTableSource));
mysource.Joins[0].JoinFields.Add(new Source.Join.JoinField(fieldOnMySource, fieldOnJoinedTableSource));
SourceDB sourceDB = new SourceDB(mysource);
SqlFormatter sqlFormatter = new SqlFormatter("[", "]", "", "LIMIT");
IDictionary <string, string> aliases = new Dictionary <string, string>()
{
{ mysource.ToString(), "a1" }, { joinedTableSource.ToString(), "a2" }
};
//---------------Execute Test ----------------------
string sql = sourceDB.CreateSQL(sqlFormatter, aliases);
//---------------Test Result -----------------------
StringAssert.AreEqualIgnoringCase(
"([mysource] a1 JOIN [myjoinedtosource] a2 on a1.[testfield] = a2.[testfield])", sql);
}
private void AppendFrom(StringBuilder builder)
{
SourceDB source = new SourceDB(_selectQuery.Source);
builder.AppendFormat(" FROM {0}", source.CreateSQL(_sqlFormatter, Aliases));
}
public void InsertDataIntoSourceDB()
{
SourceDB sourceDB = new SourceDB();
sourceDB.InsertAllImageFilesFromFolder(sourceDBDataFolder);
}
