private void LoadingShortNames(string path)
{
if (path != null)
{
using (Stream stream = File.OpenRead(path))
{
NDbfReader.Table table = NDbfReader.Table.Open(stream);
var reader = table.OpenReader(Encoding.GetEncoding(866));
if (reader != null)
{
while (reader.Read())
{
var type = ObjectSpace.FindObject <KladrType>(new BinaryOperator("Code", reader.GetString("KOD_T_ST")));
if (type == null)
{
type = ObjectSpace.CreateObject <KladrType>();
int level;
Int32.TryParse(reader.GetString("LEVEL"), out level);
type.Level = level;
type.ShortName = reader.GetString("SCNAME");
type.Name = reader.GetString("SOCRNAME");
type.Code = reader.GetString("KOD_T_ST");
}
}
ObjectSpace.CommitChanges();
}
}
}
}
public void LoadToDb(FileInfo dbfFile, string TableName, SqlConnection connection)
{
NDbfReader.Table dbfTable = NDbfReader.Table.Open(dbfFile.Open(FileMode.Open));
NDbfReader.Reader dbfReader = dbfTable.OpenReader(Encoding.GetEncoding(866));
SqlTransaction TRA = connection.BeginTransaction(("Bulk" + TableName));
SqlBulkCopy da = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, TRA);
da.BulkCopyTimeout = 60000;
da.DestinationTableName = string.Concat(SchemaName, ".", TableName);
DataTable servertable = ds.Tables[TableName];
List <DataRow> rows = new List <DataRow>();
while (dbfReader.Read())
{
DataRow newrow = servertable.NewRow();
foreach (NDbfReader.Column c in dbfTable.Columns)
{
newrow.SetField(c.Name, dbfReader.GetValue(c.Name));
}
rows.Add(newrow);
}
da.WriteToServer(rows.ToArray());
TRA.Commit();
dbfTable.Dispose();
dbfFile.Delete();
}
public void loadDBFToDb_House(FileInfo dbfFile, SqlConnection connection)
{
NDbfReader.Table dbfTable = NDbfReader.Table.Open(dbfFile.Open(FileMode.Open));
NDbfReader.Reader dbfReader = dbfTable.OpenReader(System.Text.Encoding.GetEncoding(866));
QueriesTableAdapter qta = new QueriesTableAdapter();
int c = 1;
guidparse gp = (string x) => { if (x == null)
{
return(null);
}
else
{
return(Guid.Parse(x));
} };
while (dbfReader.Read())
{
Int32 ESTSTATUS;
Int32.TryParse(dbfReader.GetValue("ESTSTATUS").ToString(), out ESTSTATUS);
if (!(bool)qta.CanInsert_tmpHouse_Query(Guid.Parse(dbfReader.GetValue("HOUSEID").ToString())) &&
(bool)qta.CanInsert_tmpHouseAO_Query(Guid.Parse(dbfReader.GetValue("AOGUID").ToString()))
)
{
qta.Insert_tmpHouse_Query(
dbfReader.GetString("POSTALCODE"),
dbfReader.GetString("IFNSFL"),
dbfReader.GetString("TERRIFNSFL"),
dbfReader.GetString("IFNSUL"),
dbfReader.GetString("TERRIFNSUL"),
dbfReader.GetString("OKATO"),
dbfReader.GetString("OKTMO"),
dbfReader.GetDateTime("UPDATEDATE"),
dbfReader.GetString("HOUSENUM"),
Int32.Parse(dbfReader.GetValue("ESTSTATUS").ToString()),
dbfReader.GetString("BUILDNUM"),
dbfReader.GetString("STRUCNUM"),
Int32.Parse(dbfReader.GetValue("STRSTATUS").ToString()),
Guid.Parse(dbfReader.GetValue("HOUSEID").ToString()),
Guid.Parse(dbfReader.GetValue("HOUSEGUID").ToString()),
Guid.Parse(dbfReader.GetValue("AOGUID").ToString()),
dbfReader.GetDateTime("STARTDATE").Value,
dbfReader.GetDateTime("ENDDATE").Value,
Int32.Parse(dbfReader.GetValue("STATSTATUS").ToString()),
gp(dbfReader.GetString("NORMDOC")),
Int32.Parse(dbfReader.GetValue("COUNTER").ToString()),
dbfReader.GetString("CADNUM"),
Int32.Parse(dbfReader.GetValue("DIVTYPE").ToString()));
}
c++;
LogInfo(c.ToString() + ". " + Guid.Parse(dbfReader.GetValue("HOUSEID").ToString()));
}
dbfTable.Dispose();
dbfFile.Delete();
}
public override void Load_DBFToDb()
{
try
{
using (NDbfReader.Table dbfTable = NDbfReader.Table.Open(File.Open(FileMode.Open)))
using (DBFDataReader dbfReader = new DBFDataReader(dbfTable, Encoding.GetEncoding(866)))
{
while (dbfReader.Read())
{
foreach (var c in dbfTable.Columns)
{
switch (Command.Parameters["@" + c.Name].DbType)
{
case System.Data.DbType.String:
if (c.Type == typeof(byte[]))
{
Command.Parameters["@" + c.Name].Value = dbfReader.GetValue(c).ToString();
}
else
{
Command.Parameters["@" + c.Name].Value = dbfReader.GetString(c);
}
break;
default:
Command.Parameters["@" + c.Name].Value = dbfReader.GetValue(c);
break;
}
}
Command.ExecuteNonQuery();
}
}
File.Delete();
}
catch (Exception e)
{
Logger.Logger.Error(e.Message);
}
}
public override void Load_DBFToDb()
{
using (SqlTransaction TRA = Connection.BeginTransaction(("Bulk" + TableName)))
{
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(Connection, SqlBulkCopyOptions.Default, TRA)
{
BulkCopyTimeout = 60000,
DestinationTableName = DestinationTableName
};
try
{
var errors = new List <object>();
using (NDbfReader.Table dbfTable = NDbfReader.Table.Open(File.Open(FileMode.Open)))
{
DBFDataReader dbfReader = new DBFDataReader(dbfTable, Encoding.GetEncoding(866));
foreach (var c in dbfTable.Columns)
{
sqlBulkCopy.ColumnMappings.Add(c.Name, c.Name);
}
sqlBulkCopy.WriteToServer(dbfReader);
TRA.Commit();
}
if (errors.Count == 0)
{
File.Delete();
}
}
catch (Exception e)
{
Logger.Logger.Error(e.Message);
}
finally
{
sqlBulkCopy.Close();
}
}
}
private void LoadingKladr(string path)
{
if (path != null)
{
int counter = 0;
int counterKladrRows = 0;
using (Stream stream = File.OpenRead(path))
{
NDbfReader.Table table = NDbfReader.Table.Open(stream);
var uow = new UnitOfWork(((DevExpress.ExpressApp.Xpo.XPObjectSpace)ObjectSpace).Session.DataLayer);
var reader = table.OpenReader(Encoding.GetEncoding(866));
if (reader != null && uow != null)
{
while (reader.Read())
{
string code = reader.GetString("CODE");
bool isModified = false;
var kladr = uow.FindObject <Kladr>(new BinaryOperator("Code", code));
if (kladr == null)
{
//если нет элемента с таким кодом, то создать
int status = -1;
Int32.TryParse(reader.GetString("STATUS"), out status);
kladr = new Kladr(uow);
kladr.Code = code;
// Импорт записи
kladr.Name = reader.GetString("NAME");;
kladr.CodePost = reader.GetString("INDEX");;
kladr.CodeOkato = reader.GetString("OCATD");;
kladr.Status = status;
kladr.CodeIfns = reader.GetString("GNINMB");
kladr.CodeIfnsTerr = reader.GetString("UNO");
isModified = true;
}
if (kladr.Type == null)
{
kladr.Type = uow.FindObject <KladrType>(new BinaryOperator("ShortName", reader.GetString("SOCR")));
isModified = true;
}
if (kladr.Level == 0)
{
kladr.Level = GetLevelFromCode(code);
isModified = true;
}
// Ищем родителя
if (kladr.Parent == null && kladr.Level > 1)
//if (kladr.Level > 1)
{
kladr.Parent = GetParent(code, kladr.Level, uow);
isModified = true;
}
counterKladrRows++;
if (isModified)
{
counter++;
// uow.Save(kladr);
//}
//if (counter % 100 == 0)
//{
uow.CommitChanges();
uow.Dispose();
uow = new UnitOfWork(((DevExpress.ExpressApp.Xpo.XPObjectSpace)ObjectSpace).Session.DataLayer);
Console.WriteLine(String.Format("\r\n{0}\r\n{1}\r\n", counter, counterKladrRows));
}
}
uow.CommitChanges();
}
uow.Dispose();
}
MessageBox.Show(String.Format(@"Всего записей в КЛАДР:{0} / Loaded/changed: {1}", counterKladrRows, counter));
}
}
private void sbtnCheck_Click(object sender, EventArgs e)
{
List <CR_FlightInfo> flightdel = new List <CR_FlightInfo>();
string f_lbaytv2 = @"\\10.100.8.108\phanbay\doantv\ddtvvfp6\solieu\lbaytv2.dbf";
string f_des = @"c:\temp\lbaytv2.dbf";
DateTime ngaymin = new DateTime(2018, 09, 01);
DateTime ngaymax = new DateTime(2018, 09, 30);
List <lbaytv2> lbtv = new List <lbaytv2>();
try {
using (ERMSEntities1 db = new ERMSEntities1())
{
flightdel = db.CR_FlightInfo.Where(x => x.IsDeleted == true && x.Date >= ngaymin && x.Date <= ngaymax).ToList();
}
File.Copy(f_lbaytv2, f_des, true);
FileStream flbtv2 = new FileStream(f_des, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
NDbfReader.Table table = NDbfReader.Table.Open(flbtv2);
NDbfReader.Reader readder = table.OpenReader();
while (readder.Read())
{
string codenv = readder.GetString("CODE_TV");
DateTime?sdate = readder.GetDateTime("START_DATE");
DateTime?edate = readder.GetDateTime("END_DATE");
string loai = readder.GetString("LOAI");
string flyno = readder.GetString("FLY_NO");
string cfg = readder.GetString("CFG");
string from = readder.GetString("FROM_PLACE");
string to = readder.GetString("END_PLACE");
if (sdate == null || edate == null)
{
continue;
}
if (ngaymin > edate || ngaymax < sdate)
{
continue;
}
if (codenv == null) //Không có Code_tv ==> Bỏ qua
{
continue;
}
if (loai != "FLY" || flyno == null || flyno.Substring(0, 1) == "R") //Không phải bay hoặc dự bị bỏ qua
{
continue;
}
var cb = flightdel.Where(x => x.Date == sdate && x.FlightNo == flyno && x.Routing == from + "-" + to).FirstOrDefault();
if (cb != null)
{
lbaytv2 item = new lbaytv2();
item.flightID = cb.FlightID;
item.code_tv = codenv;
item.start_date = (DateTime)sdate;
item.end_date = (DateTime)edate;
item.start_time = readder.GetString("START_TIME");
item.end_time = readder.GetString("END_TIME");
item.fly_no = flyno;
item.from_place = from;
item.end_place = to;
item.cfg = cfg;
item.loai = loai;
item.note = readder.GetString("NOTE");
lbtv.Add(item);
} //if
} //while
flbtv2.Dispose();
if (flightdel.Count > 0) //Ghi kết quả
{
Worksheet spreadst = base.spreadsheetControl1.ActiveWorksheet;
spreadst.Cells[0, 0].Value = "STT";
spreadst.Cells[0, 1].Value = "Date";
spreadst.Cells[0, 2].Value = "Flyno";
spreadst.Cells[0, 3].Value = "Routing";
spreadst.Cells[0, 4].Value = "Codetv";
int stt = 1, dong = 1;
foreach (var cb in flightdel)
{
spreadst.Cells[dong, 0].Value = stt;
spreadst.Cells[dong, 1].Value = cb.Date;
spreadst.Cells[dong, 2].Value = cb.FlightNo;
spreadst.Cells[dong, 3].Value = cb.Routing;
var totv = lbtv.Where(x => x.flightID == cb.FlightID).ToList();
if (totv.Count > 0)
{
foreach (var tv in totv)
{
spreadst.Cells[dong, 4].Value = "'" + tv.code_tv;
dong++;
}
}
else
{
dong++;
}
stt++;
}
}
MessageBox.Show("Complete!");
}
catch (Exception ex) { }
}
private void LoadingStreets(string path)
{
if (path != null)
{
int counter = 1;
int counterKladrRows = 0;
using (Stream stream = File.OpenRead(path))
{
NDbfReader.Table table = NDbfReader.Table.Open(stream);
var uow = new UnitOfWork(((DevExpress.ExpressApp.Xpo.XPObjectSpace)ObjectSpace).Session.DataLayer);
var reader = table.OpenReader(Encoding.GetEncoding(866));
if (reader != null && uow != null)
{
while (reader.Read())
{
string code = reader.GetString("CODE");
//if (code.Substring(0, 2) != "03")
//{
// continue;
//} // only Buryatia
bool isModified = false;
var street = uow.FindObject <Street>(new BinaryOperator("Code", code));
if (street == null)
{
//если нет элемента с таким кодом, то создать
street = new Street(uow);
street.Code = code;
// Импорт записи
street.Name = reader.GetString("NAME");;
street.CodePost = reader.GetString("INDEX");;
street.CodeOkato = reader.GetString("OCATD");;
//street.Status = status;
street.CodeIfns = reader.GetString("GNINMB");
street.CodeIfnsTerr = reader.GetString("UNO");
if (street.Type == null)
{
street.Type = uow.FindObject <KladrType>(new BinaryOperator("ShortName", reader.GetString("SOCR")));
isModified = true;
}
// Ищем родителя
if (street.City == null)
//if (kladr.Level > 1)
{
street.City = GetParent(code, uow);
isModified = true;
}
isModified = true;
}
counterKladrRows++;
if (counter % 1000 == 0)
{
uow.CommitChanges();
uow.Dispose();
uow = new UnitOfWork(((DevExpress.ExpressApp.Xpo.XPObjectSpace)ObjectSpace).Session.DataLayer);
Console.WriteLine(String.Format("\r\n{0}\r\n{1}\r\n", counter, counterKladrRows));
}
if (isModified)
{
counter++;
// uow.Save(kladr);
//}
}
}
uow.CommitChanges();
}
uow.Dispose();
}
MessageBox.Show(String.Format(@"Всего записей в КЛАДР:{0} / Loaded/changed: {1}", counterKladrRows, counter));
}
}
private void sbtnCollectInfo_Click(object sender, EventArgs e)
{
string f_lbaytv2 = @"\\10.100.8.108\phanbay\doantv\ddtvvfp6\solieu\lbaytv2.dbf";
string f_des = @"c:\temp\lbaytv2.dbf";
List <CR_FlightInfo> dscb = new List <CR_FlightInfo>();
List <CR_FlightInfo> kqcb = new List <CR_FlightInfo>();
List <lbaytv2> ctlb = new List <lbaytv2>();
bool flag;
DateTime ngaymin = new DateTime(2018, 09, 01);
DateTime ngaymax = new DateTime(2018, 09, 30);
FileStream flbtv2 = null;
try {
using (ERMSEntities1 db = new ERMSEntities1())
{
dscb = db.CR_FlightInfo.Where(x => x.Date >= ngaymin && x.Date <= ngaymax && x.IsDeleted == true).ToList();
}
//File.Copy(f_lbaytv2, f_des, true);
flbtv2 = new FileStream(f_des, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
NDbfReader.Table table = NDbfReader.Table.Open(flbtv2);
NDbfReader.Reader readder = table.OpenReader();
while (readder.Read())
{
string codenv = readder.GetString("CODE_TV");
DateTime?sdate = readder.GetDateTime("START_DATE");
DateTime?edate = readder.GetDateTime("END_DATE");
string loai = readder.GetString("LOAI");
string flyno = readder.GetString("FLY_NO");
string cfg = readder.GetString("CFG");
string from = readder.GetString("FROM_PLACE");
string to = readder.GetString("END_PLACE");
if (sdate == null || edate == null)
{
continue;
}
if (ngaymin > edate || ngaymax < sdate)
{
continue;
}
if (codenv == null) //Không có Code_tv ==> Bỏ qua
{
continue;
}
lbaytv2 item = new lbaytv2();
item.code_fly = readder.GetString("CODE_FLY");
item.code_tv = codenv;
item.from_place = from;
item.end_place = to;
item.loai = loai;
item.cfg = cfg;
item.fly_no = flyno;
item.start_date = (DateTime)sdate;
item.end_date = (DateTime)edate;
item.start_time = readder.GetString("START_TIME");
item.end_time = readder.GetString("END_TIME");
item.job = readder.GetString("JOB");
item.note = readder.GetString("NOTE");
item.type_apl = readder.GetString("TYPE_APL");
item.acf = readder.GetString("ACF");
item.status = readder.GetString("STATUS");
ctlb.Add(item);
} //While
MessageBox.Show(ctlb.Count.ToString());
} //Try
catch (Exception ex) {
}
finally
{
flbtv2.Dispose();
}
}
