public static void UpgradeDB(SccompDbf sccomp)
{
using (SQLiteConnection connection = new SQLiteConnection(@"Data Source=" + sccomp.GetAbsolutePath() + SQLiteDbPrepare.taxonomy_matching_file_name + @";Version=3;"))
{
using (SQLiteCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "PRAGMA foreign_keys = 0;";
cmd.CommandText += "CREATE TABLE glacc_match_temp_table AS SELECT* FROM glacc_match;";
cmd.CommandText += "DROP TABLE glacc_match;";
cmd.CommandText += "CREATE TABLE glacc_match(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, accnum TEXT, depcod TEXT, taxodesc TEXT, taxodesc2 TEXT);";
cmd.CommandText += "INSERT INTO glacc_match(id, accnum, depcod, taxodesc, taxodesc2) SELECT id, accnum, depcod, taxodesc, taxodesc2 FROM glacc_match_temp_table;";
cmd.CommandText += "DROP TABLE glacc_match_temp_table;";
cmd.CommandText += "PRAGMA foreign_keys = 1;";
cmd.CommandText += "CREATE TABLE IF NOT EXISTS boj5_header(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, userId TEXT, accSource TEXT, meetingType TEXT, meetingNo TEXT, sourceDate DATE, totalCapital REAL, totalShare INTEGER, parValue REAL, thaiShareholder INTEGER, totalThaiShare INTEGER, foreignShareholder INTEGER, totalForeignShare INTEGER, headerStatus TEXT, yearEnd DATE);";
cmd.CommandText += "CREATE TABLE IF NOT EXISTS boj5_person(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, addrForeign TEXT, addrFull TEXT, addrNo TEXT, amphur TEXT, holderName TEXT, itemSeq INTEGER, moo TEXT, nationality TEXT, occupation TEXT, province TEXT, road TEXT, shId TEXT, shType TEXT, soi TEXT, surname TEXT, title TEXT, tumbol TEXT);";
cmd.CommandText += "CREATE TABLE IF NOT EXISTS boj5_detail(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, itemNo INTEGER, userId TEXT, shareNumber INTEGER, shareType TEXT, paidAmount REAL, asPaidAmount REAL, shareDocId TEXT, shareDocDate DATE, shareRegExist DATE, shareRegOmit DATE, boj5_person_id INTEGER NOT NULL, FOREIGN KEY(boj5_person_id) REFERENCES boj5_person(id));"; //, FOREIGN KEY(boj5_person_id) REFERENCES boj5_person(id)
cmd.CommandText += "CREATE TABLE IF NOT EXISTS options(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, key TEXT NOT NULL, value_num REAL, value_str TEXT, value_datetime DATETIME);";
//cmd.CommandText += "INSERT INTO boj5_header (id, userId, accSource, meetingType, meetingNo, sourceDate, totalCapital, ) VALUES ()";
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
}
}
//public SQLiteDbPrepare(SccompDbf sccomp) :
// base(new SQLiteConnection()
// {
// ConnectionString = new SQLiteConnectionStringBuilder() { DataSource = sccomp.GetAbsolutePath() + taxonomy_matching_file_name, ForeignKeys = true }.ConnectionString
// }, true)
//{
//}
//protected override void OnModelCreating(DbModelBuilder modelBuilder)
//{
// modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
// base.OnModelCreating(modelBuilder);
//}
//public DbSet<boj5_header> boj5_header { get; set; }
//public DbSet<boj5_person> boj5_person { get; set; }
//public DbSet<boj5_detail> boj5_detail { get; set; }
//public DbSet<glacc_match> glacc_match { get; set; }
public static void EnsureDbCreated(SccompDbf sccomp)
{
try
{
if (!Directory.Exists(sccomp.GetAbsolutePath()))
{
XMessageBox.Show("ค้นหาไดเร็คทอรี่ " + sccomp.GetAbsolutePath() + " ไม่พบ", "Error", MessageBoxButtons.OK, XMessageBoxIcon.Error);
return;
}
if (!File.Exists(sccomp.GetAbsolutePath() + SQLiteDbPrepare.taxonomy_matching_file_name))
{
SQLiteConnection.CreateFile(sccomp.GetAbsolutePath() + SQLiteDbPrepare.taxonomy_matching_file_name);
using (SQLiteConnection connection = new SQLiteConnection(@"Data Source=" + sccomp.GetAbsolutePath() + SQLiteDbPrepare.taxonomy_matching_file_name + @";Version=3;"))
{
using (SQLiteCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "CREATE TABLE IF NOT EXISTS glacc_match(id INTEGER PRIMARY KEY, accnum TEXT, depcod TEXT, taxodesc TEXT, taxodesc2 TEXT)";
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
}
}
UpgradeDB(sccomp);
}
catch (Exception ex)
{
XMessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, XMessageBoxIcon.Error);
}
}
public static LocalDbEntities DataSet(SccompDbf sccomp)
{
try
{
//@"metadata=res://*/Model.LocalDbModel.csdl|res://*/Model.LocalDbModel.ssdl|res://*/Model.LocalDbModel.msl;provider=System.Data.SQLite.EF6;provider connection string=\"data source=D:\Express\ExpressI\87\TAXONOMY.RDB\";"
string conn_str = "metadata=res://*/Model.LocalDbModel.csdl|res://*/Model.LocalDbModel.ssdl|res://*/Model.LocalDbModel.msl;provider=System.Data.SQLite.EF6;provider connection string=\"data source = " + sccomp.GetAbsolutePath() + "\\TAXONOMY.RDB\";";
return(new LocalDbEntities(conn_str));
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
public static List <GlaccDbf> GetGlaccList(SccompDbf sccomp)
{
List <GlaccDbf> glacc = new List <GlaccDbf>();
if (!Directory.Exists(sccomp.GetAbsolutePath()))
{
XMessageBox.Show("ค้นหาไดเร็คทอรี่ " + sccomp.GetAbsolutePath() + " ไม่พบ", "Error", MessageBoxButtons.OK, XMessageBoxIcon.Error);
return(glacc);
}
else
{
using (OleDbConnection conn = DbfTable.GetConnection(sccomp.GetAbsolutePath()))
{
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select * From glacc Order By accnum ASC";
DataTable dt = new DataTable();
conn.Open();
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
da.Fill(dt);
conn.Close();
foreach (DataRow row in dt.Rows)
{
int indent_space = !row.IsNull("level") ? (Convert.ToInt32(row.Field <decimal>("level")) - 1) * 4 : 0;
glacc.Add(new GlaccDbf
{
accnam = !row.IsNull("accnam") ? row.Field <string>("accnam").TrimEnd().AddIndent(indent_space) : string.Empty,
accnam2 = !row.IsNull("accnam2") ? row.Field <string>("accnam2").TrimEnd().AddIndent(indent_space) : string.Empty,
accnum = !row.IsNull("accnum") ? row.Field <string>("accnum").TrimEnd() : string.Empty,
acctyp = !row.IsNull("acctyp") ? row.Field <string>("acctyp").TrimEnd() : string.Empty,
group = !row.IsNull("group") ? row.Field <string>("group").TrimEnd() : string.Empty,
level = !row.IsNull("level") ? row.Field <decimal>("level") : -1,
nature = !row.IsNull("nature") ? row.Field <string>("nature").TrimEnd() : string.Empty,
parent = !row.IsNull("parent") ? row.Field <string>("parent").TrimEnd() : string.Empty,
status = !row.IsNull("status") ? row.Field <string>("status").TrimEnd() : string.Empty,
usedep = !row.IsNull("usedep") ? row.Field <string>("usedep").TrimEnd() : string.Empty
});
}
}
}
return(glacc);
}
}
public static IsinfoDbf GetIsinfo(SccompDbf sccomp)
{
if (!Directory.Exists(sccomp.GetAbsolutePath()))
{
XMessageBox.Show("ค้นหาไดเร็คทอรี่ " + sccomp.GetAbsolutePath() + " ไม่พบ", "Error", MessageBoxButtons.OK, XMessageBoxIcon.Error);
return(null);
}
else
{
using (OleDbConnection conn = DbfTable.GetConnection(sccomp.GetAbsolutePath()))
{
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select * From isinfo";
DataTable dt = new DataTable();
conn.Open();
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
da.Fill(dt);
conn.Close();
if (dt.Rows.Count == 0)
{
return(null);
}
return(new IsinfoDbf
{
addr01 = !dt.Rows[0].IsNull("addr01") ? dt.Rows[0].Field <string>("addr01").TrimEnd() : string.Empty,
addr01eng = !dt.Rows[0].IsNull("addr01eng") ? dt.Rows[0].Field <string>("addr01eng").TrimEnd() : string.Empty,
addr02 = !dt.Rows[0].IsNull("addr02") ? dt.Rows[0].Field <string>("addr02").TrimEnd() : string.Empty,
addr02eng = !dt.Rows[0].IsNull("addr02eng") ? dt.Rows[0].Field <string>("addr02eng").TrimEnd() : string.Empty,
engnam = !dt.Rows[0].IsNull("engnam") ? dt.Rows[0].Field <string>("engnam").TrimEnd() : string.Empty,
taxid = !dt.Rows[0].IsNull("taxid") ? dt.Rows[0].Field <string>("taxid").TrimEnd() : string.Empty,
telnum = !dt.Rows[0].IsNull("telnum") ? dt.Rows[0].Field <string>("telnum").TrimEnd() : string.Empty,
thinam = !dt.Rows[0].IsNull("thinam") ? dt.Rows[0].Field <string>("thinam").TrimEnd() : string.Empty,
trdreg = !dt.Rows[0].IsNull("trdreg") ? dt.Rows[0].Field <string>("trdreg").TrimEnd() : string.Empty,
});
}
}
}
}