private void button2_Click(object sender, EventArgs e)
{
changingID = -1;
changingItem = null;
button1.Text = "přidat";
//button2.Enabled = false;
}
public void Add(String Name, float Price, float Amount, Company Company, int InvoiceNumber, Unit Unit, MaterialType MaterialType, Month Month, int Year, Currency Currency, DateTime ShippingDate, DateTime CourseDate, float Course, float Shipping, Deadline Deadline)
{
//String courseDateString = String.Format("{0:0000}-{1:00}-{2:00}",CourseDate.Year,CourseDate.Month,CourseDate.Day).Replace(" ","");
String queryString = String.Format("INSERT INTO `{0}` VALUES(default,'{1}',{2},{3},{4},{5},{6},{7},{8},{9},{10},'{11}','{12}',{13},{14},{15});",
_table, Name.Replace("'", "''"), Price.ToString().Replace(',', '.'), Amount.ToString().Replace(',', '.'), Company.ID, InvoiceNumber, Unit.ID, MaterialType.ID, Month.ID, Year, Currency.ID, DateTimeUtils.DateTimeToSQLDate(ShippingDate), DateTimeUtils.DateTimeToSQLDate(CourseDate), Course.ToString().Replace(',', '.'), Shipping.ToString().Replace(',', '.'), Deadline.ID);
_SQL.RunQuery(queryString);
}
public Correction(int ID, MaterialType MaterialType, float Amount, Deadline Deadline, float Price)
{
this.ID = ID;
this.MaterialType = MaterialType;
this.Amount = Amount;
this.Deadline = Deadline;
this.Price = Price;
}
public ProductExport(int id, Product product, float amount, Unit unit, String description, Month month, int year, Deadline deadline)
{
ID = id;
Product = product;
Amount = amount;
Unit = unit;
Description = description;
Month = month;
Year = year;
Deadline = deadline;
}
public Transfer(int id, Deadline deadline, MaterialType materialType, Company company, float amount, Unit unit,
float price, Currency currency, DateTime courseDate, float course)
{
ID = id;
Deadline = deadline;
MaterialType = materialType;
Company = company;
Amount = amount;
Unit = unit;
Price = price;
Currency = currency;
CourseDate = courseDate;
Course = course;
}
public Material(int id, String name, float price, float amount, Company company, int invoiceNumber, Unit unit,
MaterialType materialType, Month month, int year, Currency currency, DateTime shippingDate, DateTime courseDate,
float course, float shipping, Deadline deadline)
{
ID = id;
Name = name;
Price = price;
Amount = amount;
Company = company;
InvoiceNumber = invoiceNumber;
Unit = unit;
MaterialType = materialType;
Month = month;
Year = year;
Currency = currency;
ShippingDate = shippingDate;
CourseDate = courseDate;
Course = course;
ShippingPrice = shipping;
Deadline = deadline;
}
/// <summary>
/// Intersect between availible, transferred and used material.
/// </summary>
/// <param name="deadline">Last deadline</param>
/// <param name="endDate"></param>
/// <returns></returns>
public List<InventoryRow> GetAvailableMaterial(Deadline deadline, DateTime endDate)
{
List<InventoryRow> Buyed = GetBuyedMaterial(endDate);
List<InventoryRow> Transferred = GetTransferredMaterial(deadline);
List<InventoryRow> Used = GetUsedMaterial(deadline.EventDate, endDate);
foreach (InventoryRow tItem in Transferred)
{
bool found = false;
foreach (InventoryRow item in Buyed)
{
if (tItem.MaterialType.ToString() == item.MaterialType.ToString())
{
found = true;
item.Amount += tItem.Amount;
}
}
if (!found) {
Buyed.Add(tItem);
}
}
foreach (InventoryRow usedItem in Used)
{
bool found = false;
for (int i = 0; i < Buyed.Count; i++)
{
if (Buyed[i].MaterialType.ToString() == usedItem.MaterialType.ToString())
{
Buyed[i].Amount -= usedItem.Amount;
found = true;
}
}
if (!found) {
usedItem.Amount = -usedItem.Amount;
Buyed.Add(usedItem);
}
}
return Buyed;
}
public void MakeCorrection(MaterialType MaterialType, Deadline Deadline, float Amount, float Price)
{
Correction item = GetRecord(MaterialType.ID, Deadline.ID);
if (item == null)
{
Add(MaterialType, Amount, Deadline, Price);
}
else {
item.Amount = Amount;
item.Price = Price;
Change(item);
}
}
public void RemoveCorrection(MaterialType MaterialType, Deadline Deadline)
{
_SQL.RunQuery(String.Format("DELETE FROM `{0}` WHERE ID_MATTYPE={1} AND ID_DEADLINE={2}", _table, MaterialType.ID, Deadline.ID));
}
public void Add(MaterialType MaterialType, float Amount, Deadline Deadline, float Price)
{
_SQL.RunQuery(String.Format("INSERT INTO `{0}` VALUES(default,{1},{2},{3},{4});",
_table, MaterialType.ID, Amount.ToString().Replace(',', '.'), Deadline.ID, Price.ToString().Replace(',', '.')));
}
public NullTransfer(int id = 0, Deadline deadline = null, MaterialType materialType = null, Company company = null,
float amount = 0, Unit unit = null, float price = 0, Currency currency = null, DateTime courseDate = new DateTime(),
float course = 0)
: base(id, deadline, materialType, company, amount, unit, price, currency, courseDate, course)
{
}
public void refresh()
{
Status = "Synchronizuji...";
initState = true;
List<Company> clst = (new Companies(SQL)).getList();
if (!connector.IsConnected()) return;
if (companies != clst)
{
companies = clst;
((DataGridViewComboBoxColumn)dataGridView1.Columns["CompanyColumn"]).Items.Clear();
foreach (Company cmpn in companies)
{
((DataGridViewComboBoxColumn)dataGridView1.Columns["CompanyColumn"]).Items.Add(cmpn.ToString());
}
}
List<Unit> ulst = (new Units(SQL)).getList();
if (!connector.IsConnected()) return;
if (units != ulst)
{
units = ulst;
((DataGridViewComboBoxColumn)dataGridView1.Columns["UnitColumn"]).Items.Clear();
foreach (Unit unit in units)
{
((DataGridViewComboBoxColumn)dataGridView1.Columns["UnitColumn"]).Items.Add(unit.ToString());
}
}
List<MaterialType> mtlst = (new MaterialTypes(SQL)).getList();
if (!connector.IsConnected()) return;
if (materialTypes != mtlst)
{
materialTypes = mtlst;
((DataGridViewComboBoxColumn)dataGridView1.Columns["MaterialTypeColumn"]).Items.Clear();
foreach (MaterialType materialType in materialTypes)
{
((DataGridViewComboBoxColumn)dataGridView1.Columns["MaterialTypeColumn"]).Items.Add(materialType.ToString());
}
}
//List<Shipping> shlst = (new Shippings(SQL)).getList();
//if (!connector.IsConnected()) return;
/*
if (shippings != shlst)
{
shippings = shlst;
((DataGridViewComboBoxColumn)dataGridView1.Columns["ShippingColumn"]).Items.Clear();
foreach (Shipping shipping in shippings)
{
((DataGridViewComboBoxColumn)dataGridView1.Columns["ShippingColumn"]).Items.Add(shipping.ToString());
}
}
*/
lastDeadline = new Deadlines(SQL).GetLast();
//items = Materials.getList();
if (!connector.IsConnected()) return;
initState = false;
sumTextBox.Text = getSum().ToString();
Status = "......";
rowsAdded = false;
HandleSecurity();
}
private bool Contains(ComboBox.ObjectCollection items, Deadline item)
{
foreach (object itm in items) {
if (((Deadline)itm).ToString() == item.ToString())
return true;
}
return false;
}
private Correction ExtractCorrectionItem(List<Correction> corrs, MaterialType matType, Deadline deadline)
{
for(int i=0;i<corrs.Count;i++){
if(corrs[i].MaterialType.ID==matType.ID)
return corrs[i];
}
return Corrections.Default;
}
public void Add(Deadline Deadline, MaterialType MaterialType, Company Company, float Amount, Unit Unit, float Price,
Currency Currency, DateTime CourseDate, float Course)
{
_SQL.RunQuery(String.Format("INSERT INTO `{0}` VALUES(default,{1},{2},{3},{4},{5},{6},{7},'{8}',{9});",
_table, Deadline.ID, MaterialType.ID, Company.ID, Amount.ToString().Replace(',', '.'), Unit.ID, Price.ToString().Replace(',', '.'), Currency.ID, DateTimeUtils.DateTimeToSQLDate(CourseDate), Course.ToString().Replace(',', '.')));
}
public void refresh()
{
Status = "Synchronizuji...";
initState = true;
List<Company> clst = (new Companies(SQL)).getList();
if (!connector.IsConnected()) return;
List<Unit> ulst = (new Units(SQL)).getList();
if (!connector.IsConnected()) return;
if (units != ulst)
{
units = ulst;
((DataGridViewComboBoxColumn)dataGridView1.Columns["UnitColumn"]).Items.Clear();
foreach (Unit unit in units)
{
((DataGridViewComboBoxColumn)dataGridView1.Columns["UnitColumn"]).Items.Add(unit.ToString());
}
}
List<Product> plst = (new Products(SQL)).getList();
if (!connector.IsConnected()) return;
if (products != plst)
{
products = plst;
((DataGridViewComboBoxColumn)dataGridView1.Columns["ProductColumn"]).Items.Clear();
foreach (Product product in products)
{
((DataGridViewComboBoxColumn)dataGridView1.Columns["ProductColumn"]).Items.Add(product.ToString());
}
}
lastDeadline = new Deadlines(SQL).GetLast();
if (!connector.IsConnected()) return;
initState = false;
Status = "......";
rowsAdded = false;
HandleSecurity();
}
public void RollBack(Deadline deadline)
{
RollBack(deadline.ID);
}
/// <summary>
/// Gets material transfered from last period.
/// </summary>
/// <param name="deadline">Last deadline</param>
/// <returns></returns>
public List<InventoryRow> GetTransferredMaterial(Deadline deadline)
{
List<InventoryRow> rv = new List<InventoryRow>();
String query = String.Format("SELECT ID_MATTYPE AS ID_MATERIALTYPE, SUM(AMOUNT) AS TOTALAMOUNT, ID_UNIT FROM `TRANSFER` WHERE ID_DEADLINE={0} GROUP BY ID_MATTYPE, ID_UNIT ORDER BY ID_MATTYPE;", deadline.ID);
String netString = _SQL.RunQuery(query);
String[] lines = netString.Split('\n');
foreach (String line in lines)
{
if (line == "") continue;
rv.Add(parseLine(line));
}
return rv;
}
public void Change(Deadline item)
{
_SQL.RunQuery(String.Format("UPDATE `{0}` SET EVENTDATE='{1}' WHERE ID={2};", _table, DateTimeUtils.DateTimeToSQLDateTime(item.EventDate), item.ID));
}
public void Add(Product Product, float Amount, Unit Unit, String Description, Month Month, int Year, Deadline Deadline)
{
_SQL.RunQuery(String.Format("INSERT INTO `{0}` VALUES(default,{1},{2},{3},'{4}',{5},{6},{7});",
_table, Product.ID, Amount, Unit.ID, Description.Replace("'", "''"), Month.ID, Year, Deadline.ID));
}
/// <summary>
/// Gets list of availible material which is stored in MATERIAL and TRANSFER tables
/// </summary>
/// <param name="materialType"></param>
/// <param name="deadline"></param>
/// <param name="endDate"></param>
/// <returns></returns>
public List<InventoryMaterialRow> GetAvailibleMaterialList(MaterialType materialType, Deadline deadline, DateTime endDate)
{
List<InventoryMaterialRow> rv = new List<InventoryMaterialRow>();
String queryString = String.Format("SELECT IF(MATERIAL.SHIPPINGPRICE=0,MATERIAL.PRICE,(MATERIAL.SHIPPINGPRICE/IF(MATERIAL.COURSE<>0,MATERIAL.COURSE,1))+MATERIAL.PRICE) AS TPRICE, MATERIAL.AMOUNT, MATERIAL.ID_COMPANY, MATERIAL.ID_MATERIALTYPE, MATERIAL.CURRENCY, MATERIAL.COURSE, MATERIAL.ID_UNIT FROM MATERIAL WHERE (ID_DEADLINE IS NULL OR ID_DEADLINE=0) AND UNIX_TIMESTAMP(MATERIAL.SHIPPINGDATE)<UNIX_TIMESTAMP('{0}') AND MATERIAL.ID_MATERIALTYPE={1}", DateTimeUtils.DateTimeToSQLDateTime(endDate), materialType.ID);
queryString += String.Format(" UNION SELECT PRICE AS TPRICE, AMOUNT, ID_COMPANY, ID_MATTYPE AS ID_MATERIALTYPE, ID_CURRENCY AS CURRENCY, COURSE, ID_UNIT FROM TRANSFER WHERE ID_DEADLINE={0} AND UNIX_TIMESTAMP(COURSEDATE)<UNIX_TIMESTAMP('{1}') AND ID_MATTYPE={2}", deadline.ID, DateTimeUtils.DateTimeToSQLDateTime(endDate), materialType.ID);
queryString = "SELECT * FROM (" + queryString + ") AS INPUTMATERIAL WHERE 1=1 ORDER BY TPRICE ASC";
String netString = _SQL.RunQuery(queryString);
String[] lines = netString.Split('\n');
foreach (String line in lines)
{
if (line == "") continue;
rv.Add(
parseIMRLine(line)
);
}
return rv;
}
