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 Composition(int id, Product product, MaterialType materialType, float amount, Unit unit)
{
ID = id;
Product = product;
MaterialType = materialType;
Amount = amount;
Unit = unit;
}
public InventoryMaterialRow(float price, float amount, Unit unit, Company company, MaterialType materialType, Currency currency, float course)
{
Price = price;
Amount = amount;
Unit = unit;
Company = company;
MaterialType = materialType;
Currency = currency;
Course = course;
}
public DeadlineTransferRow(int idMaterlial, int idTransfer, MaterialType materialType, float amount, Unit unit, float price, Company company, DateTime courseDate, float course, Currency currency)
{
IDMaterial = idMaterlial;
IDTransfer = idTransfer;
MaterialType = materialType;
Amount = amount;
Unit = unit;
Price = price;
Company = company;
CourseDate = courseDate;
Course = course;
Currency = currency;
}
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;
}
public MaterialUnitPair(MaterialType m, Unit u)
{
MaterialType = m;
Unit = u;
}
public void Add(Product Product, MaterialType MaterialType, float Amount, Unit Unit)
{
_SQL.RunQuery(String.Format("INSERT INTO `{0}` VALUES(default,{1},{2},{3},{4});",
_table, Product.ID, MaterialType.ID, Amount.ToString().Replace(',', '.'), Unit.ID));
}
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 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 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));
}
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 Change(MaterialType item)
{
_SQL.RunQuery(String.Format("UPDATE `{0}` SET NAME='{1}' WHERE ID={2};", _table, item.Name.Replace("'", "''"), item.ID));
}
public void Add(MaterialType item)
{
Add(item.Name);
}
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)
{
}
private float CountTransferGroupTotalAmount(Dictionary<MaterialType, List<DeadlineTransferRow>> inputDict, MaterialType key)
{
List<DeadlineTransferRow> workList = inputDict[key];
float amount = 0;
foreach (DeadlineTransferRow item in workList)
{
amount += item.Amount;
}
return amount;
}
private float CountTransferGroupAvgPrice(Dictionary<MaterialType, List<DeadlineTransferRow>> inputDict, MaterialType key)
{
List<DeadlineTransferRow> workList = inputDict[key];
float price = 0;
foreach (DeadlineTransferRow item in workList) {
price += item.Price * item.Course;
}
return (workList.Count > 0) ? price / workList.Count : 0;
}
private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
if ((dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value == null)&&(cellValue=="")) return;
else if ((dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value!=null)&&(cellValue == dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString()))
return;
if ((addID != -1) && (addID != e.RowIndex))
rowsAdded = false;
else if ((addID != -1) && (addID == e.RowIndex))
rowsAdded = true;
if (initState) return;
foreach(String collumn in nummericIntCollums){
int intOut = 0;
if (dataGridView1.Rows[e.RowIndex].Cells[collumn].Value == null) continue;
if (!int.TryParse(dataGridView1.Rows[e.RowIndex].Cells[collumn].Value.ToString(), out intOut))
{
dataGridView1.Rows[e.RowIndex].Cells[collumn].ErrorText = "Zadejte číselnou hodnotu!";
}
else {
dataGridView1.Rows[e.RowIndex].Cells[collumn].ErrorText = "";
}
}
foreach (String collumn in nummericFloatCollums)
{
float floatOut = 0;
if (dataGridView1.Rows[e.RowIndex].Cells[collumn].Value == null) continue;
if (!float.TryParse(dataGridView1.Rows[e.RowIndex].Cells[collumn].Value.ToString().Replace('.',','), out floatOut))
{
dataGridView1.Rows[e.RowIndex].Cells[collumn].ErrorText = "Zadejte číselnou hodnotu!";
}
else
{
dataGridView1.Rows[e.RowIndex].Cells[collumn].ErrorText = "";
}
}
bool rowError = false;
foreach (DataGridViewCell cell in dataGridView1.Rows[e.RowIndex].Cells) {
if (cell.ErrorText != "")
{
rowError = true;
break;
}
}
String name = "";
if (dataGridView1.Rows[e.RowIndex].Cells["NameColumn"].Value != null)
name = dataGridView1.Rows[e.RowIndex].Cells["NameColumn"].Value.ToString();
if (name == "") return;
int id = 0;
if (!rowsAdded) id = items[e.RowIndex].ID;/* id = int.Parse(dataGridView1.Rows[e.RowIndex].Cells["IDColumn"].Value.ToString());*/
MaterialType materialTypeItem = new MaterialType(
(int)parseCell(0,e.RowIndex,"IDColumn"),
name
);
if (rowError) return;
if (rowsAdded)
{
Status = "Přidávám novou položku...";
MaterialTypes.Add(materialTypeItem);
if (!connector.IsConnected()) return;
dataGridView1.BeginInvoke(new Action(()=>{
reload();
dataGridView1.FirstDisplayedScrollingRowIndex = dataGridView1.Rows.Count - 1;
//dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells.Count - 1].Selected = true;
//dataGridView1.BeginEdit(true);
}));
addID = -1;
dataGridView1.Rows[dataGridView1.NewRowIndex].ReadOnly = false;
dataGridView1.Rows[dataGridView1.NewRowIndex].DefaultCellStyle = dataGridView1.DefaultCellStyle;
refresh();
}
else
{
if (items[e.RowIndex] != materialTypeItem)
{
Status = "Provádím úpravy...";
MaterialTypes.Change(materialTypeItem);
if (!connector.IsConnected()) return;
}
}
rowsAdded = false;
Status = ".....";
}
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 InventoryRow(MaterialType materialType, float amount, Unit unit)
{
MaterialType = materialType;
Amount = amount;
Unit = unit;
}
/// <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;
}
private Material parseLine(String line)
{
String IDString = MySQLDriver.parseCollumn(line, "ID", true);
String NameString = MySQLDriver.parseCollumn(line, "NAME");
String PriceString = MySQLDriver.parseCollumn(line, "PRICE", true).Replace('.', ',');
String AmountString = MySQLDriver.parseCollumn(line, "AMOUNT", true).Replace('.', ',');
String IDCompanyString = MySQLDriver.parseCollumn(line, "ID_COMPANY", true);
String InvoiceNumberString = MySQLDriver.parseCollumn(line, "INVOICE_NUMBER", true);
String IDUnitString = MySQLDriver.parseCollumn(line, "ID_UNIT", true);
String MonthString = MySQLDriver.parseCollumn(line, "MONTH", true);
String YearString = MySQLDriver.parseCollumn(line, "YEAR", true);
String CurrencyString = MySQLDriver.parseCollumn(line, "CURRENCY", true);
String ShippingDateString = MySQLDriver.parseCollumn(line, "SHIPPINGDATE", true);
String CourseDateString = MySQLDriver.parseCollumn(line, "COURSEDATE", true);
String CourseString = MySQLDriver.parseCollumn(line, "COURSE", true).Replace('.', ',');
String MaterialTypeIdString = MySQLDriver.parseCollumn(line, "ID_MATERIALTYPE", true);
String ShippingString = MySQLDriver.parseCollumn(line, "SHIPPINGPRICE", true).Replace('.',',');
String DeadlineString = MySQLDriver.parseCollumn(line, "ID_DEADLINE", true);
int matTypeId = int.Parse(MaterialTypeIdString);
MaterialType materialTypeItem = new MaterialType(0, "");
if (matTypeId != 0)
{
materialTypeItem = _materialTypes.GetRecord(matTypeId);
}
Month actmonth = new Months().getMonth(int.Parse(MonthString));
Deadline dead = _deadlines.GetRecord(int.Parse(DeadlineString));
if (dead == null)
{
dead = Deadlines.Default;
}
try
{
return new Material(int.Parse(IDString), NameString, float.Parse(PriceString), float.Parse(AmountString), _companies.GetRecord(int.Parse(IDCompanyString)),
int.Parse(InvoiceNumberString), _units.GetRecord(int.Parse(IDUnitString)), materialTypeItem, actmonth, int.Parse(YearString),
_currencies.getCurrency(int.Parse(CurrencyString)), DateTimeUtils.SQLDateToDateTime(ShippingDateString), DateTimeUtils.SQLDateToDateTime(CourseDateString),
float.Parse(CourseString), float.Parse(ShippingString), dead);
}
catch (Exception) {
_SQL.getConnector().setError();
return Default;
}
}
