public static void addStock(MainRack rack)
{
try
{
int rackId = rack.RackId;
int subRackId;
foreach (SubRack sr in rack.Subracks)
{
subRackId = sr.SubRackId;
foreach (Stock stock in sr.Stock)
{
string qry = "insert into stock (article, color, size, description, rack_id, sub_rack_id, date, qty) " +
"values ('" + stock.Article + "', '" + stock.Color + "', '" + stock.Size + "', '" + stock.Desc + "', " + rackId + ", " + subRackId + ", " +
"'" + stock.Date.ToString("yyyy/MM/d") + "', " + stock.Qty + ")";
DBConnection.updateDB(qry);
}
}
}
catch (Exception exc)
{
MessageBox.Show("Something went wrong!\n" + exc, "Add Stock", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static void addRack(MainRack rack)
{
try
{
string qry = "insert into rack (name) values ('" + rack.Name + "')";
DBConnection.updateDB(qry);
}
catch (Exception exc)
{
MessageBox.Show("Something went wrong!\n" + exc, "Add Main Rack", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static void addSubRack(MainRack rack)
{
try
{
int rackId = rack.RackId;
LinkedList <SubRack> subracks = rack.Subracks;
foreach (SubRack sr in subracks)
{
string qry = "insert into sub_rack (rack_id, name) values (" + rackId + ", '" + sr.Name + "')";
DBConnection.updateDB(qry);
}
}
catch (Exception exc)
{
MessageBox.Show("Something went wrong!\n" + exc, "Add Sub Rack", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
{
try
{
string name = openFileDialog1.SafeFileName;
if (name.Contains(".xlsx") || name.Contains(".xls"))
{
_Application excel = new _Excel.Application();
Workbook wb;
Worksheet ws;
string path = "D:/LabelStock/" + name;
wb = excel.Workbooks.Open(path);
ws = wb.Worksheets[1];
for (int row = 5; row <= 26; row++)
{
string article;
if (ws.Cells[row, 1].Value2 is double)
{
article = "" + (int)ws.Cells[row, 1].Value2;
}
else
{
article = ws.Cells[row, 1].Value2;
}
string desc;
if (ws.Cells[row, 2].Value2 is double)
{
desc = "" + (int)ws.Cells[row, 2].Value2;
}
else
{
desc = ws.Cells[row, 2].Value2;
}
string color;
if (ws.Cells[row, 3].Value2 is double)
{
color = "" + (int)ws.Cells[row, 3].Value2;
}
else
{
color = ws.Cells[row, 3].Value2;
}
string size;
if (ws.Cells[row, 4].Value2 is double)
{
size = "" + (int)ws.Cells[row, 4].Value2;
}
else
{
size = ws.Cells[row, 4].Value2;
}
int rackId;
string rName;
if (ws.Cells[row, 5].Value2 is double)
{
rName = "" + (int)ws.Cells[row, 5].Value2;
}
else
{
rName = ws.Cells[row, 5].Value2;
}
MySqlDataReader reader = DBConnection.getData("select rack_id from rack where name='" + rName + "'");
if (reader.Read())
{
rackId = reader.GetInt32(0);
reader.Close();
int subRackId;
string srName;
if (ws.Cells[row, 6].Value2 is double)
{
srName = "" + (int)ws.Cells[row, 6].Value2;
}
else
{
srName = ws.Cells[row, 6].Value2;
}
reader = DBConnection.getData("select sub_rack_id from sub_rack where name='" + srName + "'");
if (reader.Read())
{
subRackId = reader.GetInt32(0);
reader.Close();
int qty = 0;
if (ws.Cells[row, 7].Value2 is double)
{
qty = (int)ws.Cells[row, 7].Value2;
qty *= (int)ws.Cells[row, 8].Value2;
}
else
{
qty = ws.Cells[row, 7].Value2;
qty *= ws.Cells[row, 8].Value2;
}
int tmpQty = 0;
row++;
while ((ws.Cells[row, 1].Value2 == null) && (ws.Cells[row, 7].Value2 != null))
{
tmpQty = 0;
if (ws.Cells[row, 7].Value2 is double)
{
tmpQty += (int)ws.Cells[row, 7].Value2;
tmpQty *= (int)ws.Cells[row, 8].Value2;
qty += tmpQty;
}
else
{
tmpQty += ws.Cells[row, 7].Value2;
tmpQty *= ws.Cells[row, 8].Value2;
qty += tmpQty;
}
row++;
}
row--;
MainRack mr = new MainRack(rackId);
SubRack sr = new SubRack(subRackId);
Stock s = new Stock(article, color, size, DateTime.Today, desc, qty);
LinkedList <Stock> stocks = new LinkedList <Stock>();
LinkedList <SubRack> sracks = new LinkedList <SubRack>();
stocks.AddFirst(s);
sr.Stock = stocks;
sracks.AddFirst(sr);
mr.Subracks = sracks;
try
{
Database.addStock(mr);
}
catch (Exception exc)
{
MessageBox.Show("Something wrong with the qty cell in excel file!\n" + exc, "File reader", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
if (!reader.IsClosed)
{
reader.Close();
}
MessageBox.Show("No Main Sub Rack - " + srName, "File reader", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
if (!reader.IsClosed)
{
reader.Close();
}
MessageBox.Show("No Main Rack - " + rName, "File reader", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
wb.Close();
excel.Quit();
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(excel);
}
}
catch (Exception exception)
{
MessageBox.Show("Something wrong with the excel file!\n" + exception, "File reader", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
