public Inventory(MySQLDriver driver)
{
_SQL = driver;
_deadlines = new Deadlines(_SQL);
_materialTypes = new MaterialTypes(_SQL);
_units = new Units(_SQL);
_companies = new Companies(_SQL);
_currencies = new Currencies(_SQL);
}
public Transfers(MySQLDriver driver)
{
this._SQL = driver;
_deadlines = new Deadlines(_SQL);
_units = new Units(_SQL);
_companies = new Companies(_SQL);
_materialTypes = new MaterialTypes(_SQL);
_currencies = new Currencies(_SQL);
}
public Materials(MySQLDriver driver, Companies companies, Units units, Deadlines deadlines)
{
this._SQL = driver;
_companies = companies;
_units = units;
_currencies = new Currencies(_SQL);
_materialTypes = new MaterialTypes(_SQL);
_deadlines = deadlines;
}
public override void init(bool firstRun = true)
{
Status = "Připojování k databázi...";
connector = Authorization.login(AppSettings.Nick, AppSettings.Password);
connector.onError = new Action(()=>connectorErrorHandler(connector));
SQL = new MySQLDriver(connector);
Transfers = new Transfers(SQL);
Currencies = new Currencies(SQL);
ExchangeRates = new ExchangeRate("http://www.cnb.cz/cs/financni_trhy/devizovy_trh/kurzy_devizoveho_trhu/denni_kurz.txt");
Status = "Inicializace...";
if (firstRun)
{
}
List<Currency> curlst = (Currencies).getList();
if (!connector.IsConnected()) return;
if (currencies != curlst)
{
currencies = curlst;
comboBox1.Items.Clear();
((DataGridViewComboBoxColumn)dataGridView1.Columns["CurrencyColumn"]).Items.Clear();
foreach (Currency cmpn in currencies)
{
((DataGridViewComboBoxColumn)dataGridView1.Columns["CurrencyColumn"]).Items.Add(cmpn.ToString());
comboBox1.Items.Add(cmpn.ToString());
}
}
Status = "Stahuji aktuální kurzovní lístek ČNB...";
comboBox1.Text = "EUR";
kurz = ExchangeRates.getByDate(Currencies.getCurrency("EUR"), DateTime.Now);
if (kurz == 0) MessageBox.Show("Dnešní kurzovní lístek není na webu ČNB k dispozici.");
if (!connector.IsConnected()) return;
textBox1.Text = kurz.ToString();
initState = true;
if (!connector.IsConnected())
{
handleError();
return;
}
reload();
refresh();
}
public bool MakeDeadline(DateTime deadlineDate)
{
ProductExports ProductExports = new ProductExports(_SQL);
Materials Materials = new Materials(_SQL, new Companies(_SQL), new Units(_SQL), new Deadlines(_SQL));
if (ProductExports.GetNonDateCount() != 0)
{
MessageBox.Show("Před vytvořením uzávěrky prosím nejdříve vyplňte datum exportu u všech exportovaných položek v sekci \"Prodej z faktur\"");
return false;
}
if (Materials.GetNonDateCount() != 0)
{
MessageBox.Show("Před vytvořením uzávěrky prosím nejdříve vyplňte datum splatnosti u všech nakoupených materiálů v sekci \"Nákup z faktur\"");
return false;
}
Deadline lastDeadline = this.GetLast();
//TODO: Price=>TotalPrice #done
//PRICE*(IF(COURSE<>0,COURSE,1))
String query = String.Format("SELECT * FROM (SELECT `MATERIAL`.ID AS ID_MATERIAL, 0 AS ID_TRANSFER, ID_MATERIALTYPE, AMOUNT, ID_UNIT, IF(MATERIAL.ID_SHIPPING=1,MATERIAL.PRICE,(IF(SHIPPING.CAPACITY<>0,(SHIPPING.PRICE*(MATERIAL.AMOUNT DIV SHIPPING.CAPACITY + IF(MATERIAL.AMOUNT MOD SHIPPING.CAPACITY<>0,1,0))),SHIPPING.PRICE)/IF(MATERIAL.COURSE<>0,MATERIAL.COURSE,1))+MATERIAL.PRICE)*IF(MATERIAL.COURSE<>0,MATERIAL.COURSE,1) AS PRICE, ID_COMPANY, 1 AS CURRENCY, 1 AS COURSE, COURSEDATE FROM `MATERIAL` INNER JOIN `SHIPPING` ON `MATERIAL`.ID_SHIPPING=`SHIPPING`.ID WHERE (MATERIAL.ID_DEADLINE=0 OR MATERIAL.ID_DEADLINE IS NULL) AND MATERIAL.SHIPPINGDATE < '{0}' UNION SELECT 0 AS ID_MATERIAL, ID AS ID_TRANSFER, ID_MATTYPE AS ID_MATERIALTYPE, AMOUNT, ID_UNIT, PRICE*(IF(COURSE<>0,COURSE,1)) AS PRICE, ID_COMPANY, 1 AS CURRENCY, 1 AS COURSE, COURSEDATE FROM `TRANSFER` WHERE ID_DEADLINE={1}) AS temptbl ORDER BY PRICE DESC",
DateTimeUtils.DateTimeToSQLDate(deadlineDate), lastDeadline.ID);
String netString = _SQL.RunQuery(query);
if (netString == "")
return false;
String[] lines = netString.Split('\n');
List<DeadlineTransferRow> inputMaterial = new List<DeadlineTransferRow>();
foreach (String line in lines)
{
if (line == "") continue;
inputMaterial.Add(parseTransferLine(line));
}
Inventory Inventory = new Inventory(_SQL);
List<InventoryRow> outputMaterial = Inventory.GetUsedMaterial(lastDeadline.EventDate, deadlineDate);
//calculate warehouse status
List<DeadlineTransferRow> toDelete = new List<DeadlineTransferRow>();
foreach (InventoryRow outRow in outputMaterial)
{
float amount = 0;
for (int i = 0; i < inputMaterial.Count;i++ )
{
DeadlineTransferRow inputRow = inputMaterial[i];
if (inputRow.MaterialType.ID == outRow.MaterialType.ID)
{
if ((amount + inputRow.Amount <= outRow.Amount) || (toDelete.Contains(inputRow)))
{
amount += inputRow.Amount;
if (!toDelete.Contains(inputRow))
{
toDelete.Add(inputRow);
}
//inputMaterial.Remove(inputRow);
}
else
{
DeadlineTransferRow newRow = inputRow;
newRow.Amount -= outRow.Amount - amount;
inputMaterial[inputMaterial.IndexOf(inputRow)] = newRow;
}
}
}
}
toDelete.ForEach(a =>
{
inputMaterial.Remove(a);
});
Dictionary<MaterialType, List<DeadlineTransferRow>> groupedItems = GroupByMaterialType(inputMaterial);
List<Correction> corrs = new Corrections(_SQL).GetList(lastDeadline.ID);
Currencies Currencies = new Currencies(_SQL);
Units Units = new Units(_SQL);
for (int i = 0; i < corrs.Count; i++) {
if (corrs[i].Amount == 0)
{
if(groupedItems.Keys.Contains(corrs[i].MaterialType))
groupedItems.Remove(corrs[i].MaterialType);
continue;
}
if (!groupedItems.Keys.Contains(corrs[i].MaterialType)) {
groupedItems.Add(corrs[i].MaterialType, new List<DeadlineTransferRow>());
groupedItems[corrs[i].MaterialType].Add(new DeadlineTransferRow(0, 0, corrs[i].MaterialType, corrs[i].Amount, Units.GetRecord("m^3"), corrs[i].Price, Companies.Default, new DateTime(), 1, Currencies.getCurrency("CZK")));
continue;
}
float itemAmount = CountTransferGroupTotalAmount(groupedItems, corrs[i].MaterialType);
if (corrs[i].Amount > itemAmount)
{
float itemAvgPrice = CountTransferGroupAvgPrice(groupedItems, corrs[i].MaterialType);
groupedItems[corrs[i].MaterialType].Add(new DeadlineTransferRow(0, 0, corrs[i].MaterialType, corrs[i].Amount - itemAmount, groupedItems[corrs[i].MaterialType][0].Unit, itemAvgPrice, Companies.Default, new DateTime(), 1, Currencies.getCurrency("CZK")));
}
if (corrs[i].Amount < itemAmount)
{
List<DeadlineTransferRow> workList = SortByPrice(groupedItems[corrs[i].MaterialType]);
if (corrs[i].Amount == 0)
workList.Clear();
else {
for (int j = 0; j < workList.Count; j++) {
if (itemAmount == corrs[i].Amount)
break;
if (itemAmount - workList[j].Amount > corrs[i].Amount)
{
itemAmount -= workList[j].Amount;
workList.RemoveAt(j);
}
else if (itemAmount - workList[j].Amount < corrs[i].Amount)
{
workList[j].Amount -= Math.Abs(corrs[i].Amount - itemAmount);
itemAmount = corrs[i].Amount;
}
}
}
workList.Remove(null);
if (itemAmount != 0)
groupedItems[corrs[i].MaterialType] = workList;
else
groupedItems.Remove(corrs[i].MaterialType);
}
}
inputMaterial = TransferGroupToList(groupedItems);
//add new deadline into Deadlines
Add(deadlineDate);
//add inputMaterial into Transfer
lastDeadline = this.GetLast();
Transfers Transfers = new Transfers(_SQL);
foreach (DeadlineTransferRow row in inputMaterial)
{
Transfers.Add(lastDeadline, row.MaterialType, row.Company, row.Amount, row.Unit, row.Price, row.Currency, row.CourseDate, row.Course);
}
//Set all MATERIALS in this deadline to closed state
query = String.Format("UPDATE `MATERIAL` SET ID_DEADLINE={1} WHERE (MATERIAL.ID_DEADLINE=0 OR MATERIAL.ID_DEADLINE IS NULL) AND MATERIAL.SHIPPINGDATE < '{0}'",
DateTimeUtils.DateTimeToSQLDateTime(deadlineDate), lastDeadline.ID);
_SQL.RunQuery(query);
return true;
}
private DeadlineTransferRow parseTransferLine(String line)
{
MaterialTypes MaterialTypes = new MaterialTypes(_SQL);
Units Units = new Units(_SQL);
String IDMaterialString = MySQLDriver.parseCollumn(line, "ID_MATERIAL", true);
String IDTransferString = MySQLDriver.parseCollumn(line, "ID_TRANSFER", true);
String MaterialTypeString = MySQLDriver.parseCollumn(line, "ID_MATERIALTYPE", true);
String AmountString = MySQLDriver.parseCollumn(line, "AMOUNT", true).Replace('.', ',');
String UnitString = MySQLDriver.parseCollumn(line, "ID_UNIT", true);
String PriceString = MySQLDriver.parseCollumn(line, "PRICE", true).Replace('.', ',');
String CompanyString = MySQLDriver.parseCollumn(line, "ID_COMPANY", true);
String CourseDateString = MySQLDriver.parseCollumn(line, "COURSEDATE");
String CourseString = MySQLDriver.parseCollumn(line, "COURSE", true).Replace('.', ',');
String CurrencyString = MySQLDriver.parseCollumn(line, "CURRENCY", true);
Companies Companies = new Companies(_SQL);
Currencies Currencies = new Currencies(_SQL);
return new DeadlineTransferRow(
int.Parse(IDMaterialString), int.Parse(IDTransferString), MaterialTypes.GetRecord(int.Parse(MaterialTypeString)),
float.Parse(AmountString), Units.GetRecord(int.Parse(UnitString)), float.Parse(PriceString),
Companies.GetRecord(int.Parse(CompanyString), true), DateTimeUtils.SQLDateToDateTime(CourseDateString),
float.Parse(CourseString), Currencies.getCurrency(int.Parse(CurrencyString))
);
}