/// <summary>
/// add or update order doc
/// </summary>
/// <param name="doc"></param>
/// <returns></returns>
public async Task <int> AddOrUpdate(OrderDoc doc)
{
using (DatanetCMSWebEntities context = new DatanetCMSWebEntities())
{
context.OrderDocs.AddOrUpdate(doc);
return(await context.SaveChangesAsync());
}
}
private static Order RestoreFrom(OrderDoc orderDoc)
{
var priceAgreementType = orderDoc.PriceAgreementType.ToDomainModel <PriceAgreementType>();
return(priceAgreementType switch
{
PriceAgreementType.Non => RestoreWithNoPriceAgreement(orderDoc),
PriceAgreementType.Temporary => RestoreWithTemporaryPriceAgreement(orderDoc),
PriceAgreementType.Final => RestoreWithFinalPriceAgreement(orderDoc),
_ => throw new ArgumentOutOfRangeException(nameof(priceAgreementType), priceAgreementType, null)
});
public static OrderDocModel ToOrderDocModel(this OrderDoc doc)
{
var model = new OrderDocModel()
{
Id = doc.Id,
OrderId = doc.OrderId,
PoDocPath = doc.PoDocPath
};
return(model);
}
public static OrderDoc ToOrderDocModel(this OrderInfoModel model)
{
var orderDocModel = model.OrderDocs.FirstOrDefault();
if (!string.IsNullOrWhiteSpace(orderDocModel?.PoDocPath))
{
var orderDoc = new OrderDoc
{
PoDocPath = orderDocModel.PoDocPath
};
return(orderDoc);
}
return(null);
}
void ExecuteSqlServer()
{
OrderDoc orderDoc;
String thisIsOrderUpdate;
Double sum = 0;
Double amount = 0;
Double price = 0;
Double vat = 0;
Double total = 0;
String report;
report = "Процесс создания Заказов - запушен!";
try{
/* Обход прайсов */
foreach (Price pl in priceList)
{
sum = 0;
amount = 0;
price = 0;
vat = 0;
total = 0;
/* Создание основной информации документа заказ */
orderDoc = new OrderDoc();
orderDoc.docDate = DateTime.Today.Date;
orderDoc.docNumber = createDocNumber();
orderDoc.docName = "Заказ";
orderDoc.docCounteragent = pl.counteragentName;
orderDoc.docAutor = DataConfig.userName;
orderDoc.docSum = 0;
orderDoc.docVat = 0;
orderDoc.docTotal = 0;
orderDoc.docPurchasePlan = docPPNumber;
sqlServer = new SqlServer();
sqlServer.sqlCommandSelect.CommandText = "SELECT " +
"id, nomenclatureID, nomenclatureName, units, amount, " +
"name, price, manufacturer, remainder, term, discount1, discount2, discount3, discount4, code, series, article, " +
"counteragentName, counteragentPricelist, " +
"docPurchasePlan, docOrder " +
"FROM OrderNomenclature WHERE (docPurchasePlan = '" + docPPNumber + "' AND counteragentName = '" + pl.counteragentName + "')";
sqlServer.sqlCommandUpdate.CommandText = "UPDATE OrderNomenclature SET " +
"nomenclatureID = @nomenclatureID, nomenclatureName = @nomenclatureName, units = @units, amount = @amount, " +
"name = @name, price = @price, manufacturer = @manufacturer, remainder = @remainder, term = @term, " +
"discount1 = @discount1, discount2 = @discount2, discount3 = @discount3, discount4 = @discount4, " +
"code = @code, series = @series, article = @article, " +
"counteragentName = @counteragentName, counteragentPricelist = @counteragentPricelist, " +
"docPurchasePlan = @docPurchasePlan, docOrder = @docOrder " +
"WHERE ([id] = @id)";
sqlServer.sqlCommandUpdate.Parameters.Add("@nomenclatureID", SqlDbType.Int, 10, "nomenclatureID");
sqlServer.sqlCommandUpdate.Parameters.Add("@nomenclatureName", SqlDbType.VarChar, 255, "nomenclatureName");
sqlServer.sqlCommandUpdate.Parameters.Add("@units", SqlDbType.VarChar, 255, "units");
sqlServer.sqlCommandUpdate.Parameters.Add("@amount", SqlDbType.Float, 15, "amount");
sqlServer.sqlCommandUpdate.Parameters.Add("@name", SqlDbType.VarChar, 255, "name");
sqlServer.sqlCommandUpdate.Parameters.Add("@price", SqlDbType.Float, 15, "price");
sqlServer.sqlCommandUpdate.Parameters.Add("@manufacturer", SqlDbType.VarChar, 255, "manufacturer");
sqlServer.sqlCommandUpdate.Parameters.Add("@remainder", SqlDbType.Float, 15, "remainder");
sqlServer.sqlCommandUpdate.Parameters.Add("@term", SqlDbType.Date, 15, "term");
sqlServer.sqlCommandUpdate.Parameters.Add("@discount1", SqlDbType.Float, 15, "discount1");
sqlServer.sqlCommandUpdate.Parameters.Add("@discount2", SqlDbType.Float, 15, "discount2");
sqlServer.sqlCommandUpdate.Parameters.Add("@discount3", SqlDbType.Float, 15, "discount3");
sqlServer.sqlCommandUpdate.Parameters.Add("@discount4", SqlDbType.Float, 15, "discount4");
sqlServer.sqlCommandUpdate.Parameters.Add("@code", SqlDbType.VarChar, 255, "code");
sqlServer.sqlCommandUpdate.Parameters.Add("@series", SqlDbType.VarChar, 255, "series");
sqlServer.sqlCommandUpdate.Parameters.Add("@article", SqlDbType.VarChar, 255, "article");
sqlServer.sqlCommandUpdate.Parameters.Add("@counteragentName", SqlDbType.VarChar, 255, "counteragentName");
sqlServer.sqlCommandUpdate.Parameters.Add("@counteragentPricelist", SqlDbType.VarChar, 255, "counteragentPricelist");
sqlServer.sqlCommandUpdate.Parameters.Add("@docPurchasePlan", SqlDbType.VarChar, 255, "docPurchasePlan");
sqlServer.sqlCommandUpdate.Parameters.Add("@docOrder", SqlDbType.VarChar, 255, "docOrder");
sqlServer.sqlCommandUpdate.Parameters.Add("@id", SqlDbType.Int, 10, "id");
if (sqlServer.ExecuteFill("OrderNomenclature"))
{
thisIsOrderUpdate = orderMustBeUpdated(sqlServer.dataSet);
foreach (DataRow row in sqlServer.dataSet.Tables["OrderNomenclature"].Rows)
{
/* Привязываем к документу */
if (thisIsOrderUpdate == "")
{
row["docOrder"] = orderDoc.docNumber;
}
else
{
row["docOrder"] = thisIsOrderUpdate;
}
/* Вычисления */
price = (Double)row["price"];
amount = (Double)row["amount"];
sum += (price * amount);
}
/* Итоги вычислений */
sum = Math.Round(sum, 2);
vat = sum * DataConstants.ConstFirmVAT / 100;
vat = Math.Round(vat, 2);
total = sum + vat;
total = Math.Round(total, 2);
orderDoc.docSum = sum;
orderDoc.docVat = vat;
orderDoc.docTotal = total;
if (thisIsOrderUpdate == "") // Создаём новый заказ
/* Сохранение основных данных документа Заказ */
{
sqlQuery = new QuerySqlServer(DataConfig.serverConnection);
sqlQuery.SetCommand("INSERT INTO Orders " +
"(docDate, docNumber, docName, docCounteragent, " +
"docAutor, docSum, docVat, docTotal, docPurchasePlan) " +
"VALUES ('" + orderDoc.docDate + "', " +
"'" + orderDoc.docNumber + "', " +
"'" + orderDoc.docName + "', " +
"'" + orderDoc.docCounteragent + "', " +
"'" + orderDoc.docAutor + "', " +
"" + Conversion.DoubleToString(orderDoc.docSum) + ", " +
"" + Conversion.DoubleToString(orderDoc.docVat) + ", " +
"" + Conversion.DoubleToString(orderDoc.docTotal) + ", " +
"'" + orderDoc.docPurchasePlan + "')");
if (sqlQuery.Execute())
{
report += Environment.NewLine;
report += "Документ Заказ №" + orderDoc.docNumber + " - создан!";
if (sqlServer.ExecuteUpdate("OrderNomenclature"))
{
report += Environment.NewLine;
report += "Документ План закупок №" + docPPNumber + " - обновлён!";
}
else
{
report += Environment.NewLine;
report += "Документ План закупок №" + docPPNumber + " - ошибка обновления!";
}
}
else
{
report += Environment.NewLine;
report += "Документ Заказ №" + orderDoc.docNumber + " - ошибка создания!";
}
}
else // Обновляем данные в заказе
{
if (sqlServer.ExecuteUpdate("OrderNomenclature"))
{
report += Environment.NewLine;
report += "Документ План закупок №" + docPPNumber + " - обновлён!";
/* Перерасчет Заказа */
sqlQuery = new QuerySqlServer(DataConfig.serverConnection);
sqlQuery.SetCommand("UPDATE Orders SET " +
"docSum = " + Conversion.DoubleToString(orderDoc.docSum) + ", " +
"docVat = " + Conversion.DoubleToString(orderDoc.docVat) + ", " +
"docTotal = " + Conversion.DoubleToString(orderDoc.docTotal) + " " +
"WHERE ([docNumber] = '" + thisIsOrderUpdate + "')");
if (sqlQuery.Execute())
{
report += Environment.NewLine;
report += "Документ Заказ №" + thisIsOrderUpdate + " - обновлён!";
}
else
{
report += Environment.NewLine;
report += "Документ Заказ №" + thisIsOrderUpdate + " - ошибка обновления!";
}
}
else
{
report += Environment.NewLine;
report += "Документ План закупок №" + docPPNumber + " - ошибка обновления!";
}
}
}
else
{
MessageBox.Show("Не удалось загрузить перечень номенклатуры из документа" + docPPNumber + "" + Environment.NewLine +
"Создание Заказов на основании Плана закупок невозможно!", "Сообщение");
Dispose();
return;
}
}
DataForms.FClient.updateHistory("Orders");
/* Отчёт о проделанной работе */
Dispose();
Utilits.Console.Log("[ВВОД НА ОСНОВАНИИ]" + Environment.NewLine +
"Отчёт --------------------------------------------------------------------" +
Environment.NewLine + report + Environment.NewLine +
"------------------------------------------------------------------------------");
MessageBox.Show("Обработка Плана закупок №" + docPPNumber + " завершена!", "Сообщение");
}catch (Exception ex) {
Dispose();
Utilits.Console.Log("[ОШИБКА] " + ex.Message, false, true);
}
}
void createOrdersSqlServer()
{
Double sum = 0;
Double amount = 0;
Double price = 0;
Double vat = 0;
Double total = 0;
SqlServer sqlServer = null;
QuerySqlServer sqlQuery = null;
OrderDoc orderDoc;
try{
sqlServer = new SqlServer();
foreach (Price plist in priceList) // Обход прайсов
{
sum = 0;
amount = 0;
price = 0;
vat = 0;
total = 0;
/* Создание основной информации документа заказ */
orderDoc = new OrderDoc();
orderDoc.docDate = DateTime.Today.Date;
orderDoc.docNumber = createDocNumber();
orderDoc.docName = "Заказ";
orderDoc.docCounteragent = plist.counteragentName;
orderDoc.docAutor = DataConfig.userName;
orderDoc.docSum = 0;
orderDoc.docVat = 0;
orderDoc.docTotal = 0;
orderDoc.docPurchasePlan = docPPNumber;
sqlServer = new SqlServer();
sqlServer.sqlCommandSelect.CommandText = "SELECT " +
"id, nomenclatureID, nomenclatureName, units, amount, " +
"name, price, manufacturer, remainder, term, discount1, discount2, discount3, discount4, code, series, article, " +
"counteragentName, counteragentPricelist, " +
"docPurchasePlan, docOrder " +
"FROM OrderNomenclature WHERE (docPurchasePlan = '" + docPPNumber + "' AND counteragentName = '" + plist.counteragentName + "')";
sqlServer.sqlCommandUpdate.CommandText = "UPDATE OrderNomenclature SET " +
"nomenclatureID = @nomenclatureID, nomenclatureName = @nomenclatureName, units = @units, amount = @amount, " +
"name = @name, price = @price, manufacturer = @manufacturer, remainder = @remainder, term = @term, " +
"discount1 = @discount1, discount2 = @discount2, discount3 = @discount3, discount4 = @discount4, " +
"code = @code, series = @series, article = @article, " +
"counteragentName = @counteragentName, counteragentPricelist = @counteragentPricelist, " +
"docPurchasePlan = @docPurchasePlan, docOrder = @docOrder " +
"WHERE ([id] = @id)";
sqlServer.sqlCommandUpdate.Parameters.Add("@nomenclatureID", SqlDbType.Int, 10, "nomenclatureID");
sqlServer.sqlCommandUpdate.Parameters.Add("@nomenclatureName", SqlDbType.VarChar, 255, "nomenclatureName");
sqlServer.sqlCommandUpdate.Parameters.Add("@units", SqlDbType.VarChar, 255, "units");
sqlServer.sqlCommandUpdate.Parameters.Add("@amount", SqlDbType.Float, 15, "amount");
sqlServer.sqlCommandUpdate.Parameters.Add("@name", SqlDbType.VarChar, 255, "name");
sqlServer.sqlCommandUpdate.Parameters.Add("@price", SqlDbType.Float, 15, "price");
sqlServer.sqlCommandUpdate.Parameters.Add("@manufacturer", SqlDbType.VarChar, 255, "manufacturer");
sqlServer.sqlCommandUpdate.Parameters.Add("@remainder", SqlDbType.Float, 15, "remainder");
sqlServer.sqlCommandUpdate.Parameters.Add("@term", SqlDbType.Date, 15, "term");
sqlServer.sqlCommandUpdate.Parameters.Add("@discount1", SqlDbType.Float, 15, "discount1");
sqlServer.sqlCommandUpdate.Parameters.Add("@discount2", SqlDbType.Float, 15, "discount2");
sqlServer.sqlCommandUpdate.Parameters.Add("@discount3", SqlDbType.Float, 15, "discount3");
sqlServer.sqlCommandUpdate.Parameters.Add("@discount4", SqlDbType.Float, 15, "discount4");
sqlServer.sqlCommandUpdate.Parameters.Add("@code", SqlDbType.VarChar, 255, "code");
sqlServer.sqlCommandUpdate.Parameters.Add("@series", SqlDbType.VarChar, 255, "series");
sqlServer.sqlCommandUpdate.Parameters.Add("@article", SqlDbType.VarChar, 255, "article");
sqlServer.sqlCommandUpdate.Parameters.Add("@counteragentName", SqlDbType.VarChar, 255, "counteragentName");
sqlServer.sqlCommandUpdate.Parameters.Add("@counteragentPricelist", SqlDbType.VarChar, 255, "counteragentPricelist");
sqlServer.sqlCommandUpdate.Parameters.Add("@docPurchasePlan", SqlDbType.VarChar, 255, "docPurchasePlan");
sqlServer.sqlCommandUpdate.Parameters.Add("@docOrder", SqlDbType.VarChar, 255, "docOrder");
sqlServer.sqlCommandUpdate.Parameters.Add("@id", SqlDbType.Int, 10, "id");
if (sqlServer.ExecuteFill("OrderNomenclature")) // получаем перечень номенклатуры ПЗ
{
if (sqlServer.dataSet.Tables["OrderNomenclature"].Rows.Count <= 0)
{
continue; // пропускаем (нет номенклатуры по данному контрагенту)
}
foreach (DataRow row in sqlServer.dataSet.Tables["OrderNomenclature"].Rows)
{
/* Привязываем к документу */
row["docOrder"] = orderDoc.docNumber;
/* Вычисления */
price = (Double)row["price"];
amount = (Double)row["amount"];
sum += (price * amount);
}
/* Итоги вычислений */
sum = Math.Round(sum, 2);
vat = sum * DataConstants.ConstFirmVAT / 100;
vat = Math.Round(vat, 2);
total = sum + vat;
total = Math.Round(total, 2);
orderDoc.docSum = sum;
orderDoc.docVat = vat;
orderDoc.docTotal = total;
/* Создаём новый заказ */
sqlQuery = new QuerySqlServer(DataConfig.serverConnection);
sqlQuery.SetCommand("INSERT INTO Orders " +
"(docDate, docNumber, docName, docCounteragent, " +
"docAutor, docSum, docVat, docTotal, docPurchasePlan) " +
"VALUES ('" + orderDoc.docDate + "', " +
"'" + orderDoc.docNumber + "', " +
"'" + orderDoc.docName + "', " +
"'" + orderDoc.docCounteragent + "', " +
"'" + orderDoc.docAutor + "', " +
"" + Conversion.DoubleToString(orderDoc.docSum) + ", " +
"" + Conversion.DoubleToString(orderDoc.docVat) + ", " +
"" + Conversion.DoubleToString(orderDoc.docTotal) + ", " +
"'" + orderDoc.docPurchasePlan + "')");
if (sqlQuery.Execute())
{
/* Обновляем журнал Заказов */
DataForms.FClient.updateHistory("Orders");
Utilits.Console.Log("Ввод на основании: создан Заказ №" + orderDoc.docNumber + " для План закупок №" + docPPNumber);
/* Обновление номенклатуры ПЗ (добавляем номер документа Заказ) */
if (sqlServer.ExecuteUpdate("OrderNomenclature"))
{
Utilits.Console.Log("Ввод на основании: План заказов №" + docPPNumber + " обновлён.");
}
else
{
if (sqlServer != null)
{
sqlServer.Dispose();
}
if (sqlQuery != null)
{
sqlQuery.Dispose();
}
Utilits.Console.Log("[ОШИБКА] Ввод на основании: План закупок №" + docPPNumber + " не удалось одновить! Заказ №" + orderDoc.docNumber, false, true);
MessageBox.Show("Не удалось обновить План закупок №" + docPPNumber + " Создание заказов прервано!", "Сообщение");
return;
}
}
else
{
if (sqlServer != null)
{
sqlServer.Dispose();
}
if (sqlQuery != null)
{
sqlQuery.Dispose();
}
Utilits.Console.Log("[ОШИБКА] Ввод на основании: Не удалось создать Заказ для План закупок №" + docPPNumber, false, true);
MessageBox.Show("Не удалось создать Заказ для План закупок №" + docPPNumber, "Сообщение");
return;
}
}
else
{
if (sqlServer != null)
{
sqlServer.Dispose();
}
if (sqlQuery != null)
{
sqlQuery.Dispose();
}
Utilits.Console.Log("[ОШИБКА] Ввод на основании: Не удалось загрузить перечень номенклатуры из документа" + docPPNumber, false, true);
MessageBox.Show("Не удалось загрузить перечень номенклатуры из документа" + docPPNumber + "" + Environment.NewLine +
"Создание Заказов на основании Плана закупок невозможно!", "Сообщение");
return;
}
}
}catch (Exception ex) {
if (sqlServer != null)
{
sqlServer.Dispose();
}
if (sqlQuery != null)
{
sqlQuery.Dispose();
}
Utilits.Console.Log("[ОШИБКА] Ввод на основании: " + ex.Message, false, true);
}
if (sqlServer != null)
{
sqlServer.Dispose();
}
if (sqlQuery != null)
{
sqlQuery.Dispose();
}
MessageBox.Show("План закупок №" + docPPNumber + " был успешно обработан!" + Environment.NewLine + "Заказы созданы в соответствии с выбранными прайс-листами и номенклатурой! ", "Сообщение");
}