private void ImportRoutes()
{
string sql = " Truncate Table RouteMaster";
if (CheckTable("RouteMaster"))
{
if (MessageBox.Show("The Route Table do exists in the Database!\n" +
" The data will be deleted. Continue updating",
"Warning", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
ExecuteSQLDirect(sql);
}
else
{
return;
}
}
ExecuteSQLDirect(sql);
sql = "Select * from Route_List";
table = new DataTable();
old = new OldBlObj();
pbR.Value = 0;
table = old.GetDataTable(sql);
int idiv = table.Rows.Count;
ExportBulkQty(table, "RouteMaster");
pbR.Value = 100;
lbRM.Text = "Imported Records" + idiv;
}
private void ImportQty()
{
string sql = "Select * from Collection_tbl where Month(trDate)=" + dtM.Value.Month +
" And Year(trdate) = " + dtM.Value.Year + " Order By trDate ";
int i = 0, idiv = 0;
table = new DataTable();
old = new OldBlObj();
table = old.GetDataTable(sql);
idiv = table.Rows.Count / 100;
CheckRowIntegrity();
ExportBulkQty(table, "Collection_tbl");
pgbQty.Value = 100;
lmsgQ.Text = string.Format("Importing record {0,6}", table.Rows.Count);
}
private void ImportOther()
{
// Here we backup previous Main_Reg Table
string tabName = "Main_Reg_" + dtM.Value.Year.ToString().Trim() + "_" + String.Format("{0:MMM}", dtM.Value.AddMonths(-1));
int idiv = 0;
bool TableCreated = false;
// Getting data from old system
string sql = "Select * from Main_Reg";
table = new DataTable();
old = new OldBlObj();
table = old.GetDataTable(sql);
idiv = table.Rows.Count;
pbOth.Value = 0;
// Here we check previous table exists
if (CheckTable(tabName))
{
sql = "Drop Table " + tabName;
ExecuteSQLDirect(sql);
//sql = "Insert " + tabName + " Select * FROM Main_Reg";
//ExecuteSQLDirect(sql);
CreateTable(tabName);
TableCreated = true;
}
else
{
CreateTable(tabName);
TableCreated = true;
}
sql = "Truncate Table Main_Reg";
ExecuteSQLDirect(sql);
ExportBulkQty(table, "Main_Reg");
// Here we are going update present suppliers with OLD parameters
if (TableCreated)
{
sql = " Update Main_Reg Set DeductionRate = " + tabName + ".DeductionRate, isActive =" + tabName + ".isActive," +
"isBogus=" + tabName + ".isBogus From " + tabName + " Where Main_Reg.RegNo =" + tabName + ".RegNo";
ExecuteSQLDirect(sql);
}
// Here we marking active suppliers
sql = " Update Main_Reg Set isActive = 1 Where RegNo in ( Select Distinct RegNo From Collection_Tbl Where" +
" Month(trDate) = " + dtM.Value.Month + " AND Year(trDate) = " + dtM.Value.Year + ")";
ExecuteSQLDirect(sql);
lo.Text = String.Format("Imported Master Records = {0,6}", idiv);
pbOth.Value = 100;
contxt.Database.Connection.Close();
}
private void GetOldRoutes()
{
old = new OldBlObj();
string sql = " Select * from Route_List";
DataTable oldr = new DataTable();
oldr = old.GetDataTable(sql);
int reccnt = 0;
if (oldr.Rows.Count > 0)
{
this.Cursor = Cursors.WaitCursor;
foreach (DataRow dr in oldr.Rows)
{
address.AddressType = bookType.id;
string routeName = dr["rName"].ToString().Trim();
address.AddressName = routeName;
address.AddressLine01 = address.AddressName;
address.FactoryId = factory.id;
AddressBook abtemp = contxt.AddressBooks.FirstOrDefault(
o => o.AddressName.Trim() == routeName && o.AddressType == bookType.id);
if (abtemp == null)
{
//isNew = true; isEdit = false;
//Save();
address.CreatedBy = 1;
address.CreatedDate = DateTime.Now;
contxt.AddressBooks.Add(address);
contxt.Entry(address).State = System.Data.Entity.EntityState.Added;
}
else
{
//isNew = false; isEdit = true;
address.id = abtemp.id;
abtemp.ModifiedBy = 1;
abtemp.ModifiedDate = DateTime.Now;
abtemp.AddressName = address.AddressName;
abtemp.AddressLine01 = address.AddressLine01;
abtemp.AddressLine02 = address.AddressLine02;
abtemp.AddressBookType = address.AddressBookType;
abtemp.AddressType = bookType.id;
contxt.Entry(abtemp).CurrentValues.SetValues(address);// = System.Data.Entity.EntityState.Modified;
abtemp = null;
}
contxt.SaveChanges();
reccnt++;
if (reccnt % 100 == 0)
{
pgAb.Value++;
}
pgAb.Refresh();
address = new AddressBook();
//Application.
}
lmsg.Text = reccnt.ToString() + " Record updated";
if (reccnt < 100)
{
pgAb.Value = pgAb.Value + (100 - pgAb.Value);
}
pgAb.Refresh();
this.Cursor = Cursors.Default;
}
}
private void ImportOldData()
{
//System.Data.OleDb.OleDbConnection olecn;
//System.Data.OleDb.OleDbCommand olecmd;
//System.Data.OleDb.OleDbDataAdapter oleda;
//string oldcn = System.Configuration.ConfigurationManager.ConnectionStrings["blold"].ConnectionString;
//olecn = new System.Data.OleDb.OleDbConnection(oldcn);
string sql = "Select * from Main_Reg";
DataTable olds = new DataTable();
Application.UseWaitCursor = true;
OldBlObj old = new OldBlObj();
olds = old.GetDataTable(sql);
int reccnt = olds.Rows.Count;
int intval = reccnt / 100;
int newsup = 0;
reccnt = 0;
foreach (DataRow dr in olds.Rows)
{
supplier.RegNo = int.Parse(dr["RegNo"].ToString());
supplier.PayMode = dr["PayMode"].ToString();
supplier.BankAccount = dr["AcNumber"].ToString();
supplier.SavingRegistered = int.Parse(dr["SavingReg"].ToString() == "True" ? "1" :"0");
supplier.WelfareRegistered = dr["WelfareReg"].ToString() == "Yes" ? 1 : 0;
supplier.FactoryId = factory.id;
DateTime rd;
if (DateTime.TryParse(dr["RegDate"].ToString(), out rd))
{
supplier.RegDate = rd;
}
route = routes.Where(o => o.AddressName.Trim() == dr["RouteName"].ToString().Trim()).FirstOrDefault();
if (route == null)
{
throw new Exception("The route is not registered");
}
else
{
supplier.RouteId = route.id;
}
//if (dr["WelfareReg"].ToString() == "Yes")
// supplier.WelfareRegDate= DateTime.Parse(dr[])
SupplierMaster stemp = contxt.SupplierMasters.FirstOrDefault(o => o.RegNo == supplier.RegNo);
if (stemp == null)
{
isNew = true; isEdit = false;
address.CreatedBy = 1;
address.CreatedDate = DateTime.Now;
contxt.AddressBooks.Add(address);
contxt.Entry(address).State = System.Data.Entity.EntityState.Added;
contxt.SaveChanges();
contxt.Entry(address).Reload();
supplier.CreatedDate = DateTime.Now;
supplier.CreatedBy = 1;
supplier.AddressBookId = address.id;
contxt.SupplierMasters.Add(supplier);
contxt.Entry(supplier).State = System.Data.Entity.EntityState.Added;
newsup++;
}
else
{
isNew = false; isEdit = true;
AddressBook atemp = contxt.AddressBooks.Where(o => o.id == stemp.AddressBookId).FirstOrDefault();
if (atemp == null)
{
address.AddressType = bookType.id;
address.FactoryId = factory.id;
address.AddressName = dr["RegName"].ToString();
address.AddressLine01 = dr["Address"].ToString();
address.TP01 = dr["TPno"].ToString();
address.CreatedBy = 1;
address.CreatedDate = DateTime.Now;
contxt.AddressBooks.Add(address);
contxt.Entry(address).State = System.Data.Entity.EntityState.Added;
contxt.SaveChanges();
contxt.Entry(address).Reload();
}
address.AddressType = bookType.id;
address.FactoryId = factory.id;
address.id = atemp.id;
address.AddressName = dr["RegName"].ToString();
address.AddressLine01 = dr["Address"].ToString();
address.TP01 = dr["TPno"].ToString();
address.ModifiedBy = 1;
address.ModifiedDate = DateTime.Now;
contxt.Entry(atemp).CurrentValues.SetValues(address);
contxt.SaveChanges();
// Here we are going to update supplier
supplier.id = stemp.id;
supplier.AddressBookId = address.id;
supplier.ModifiedBy = 1;
supplier.ModifiedDate = DateTime.Now;
contxt.Entry(stemp).CurrentValues.SetValues(supplier);
contxt.SaveChanges();
}
reccnt++;
int upd = reccnt % 100 == 0 ? pgb.Value++ : 0;
pgb.Refresh();
address = new AddressBook();
supplier = new SupplierMaster();
}
Application.UseWaitCursor = false;
MessageBox.Show(" Suppliers imported =" + newsup);
}