/* Заносим данные в List<AllOrdersModel> */
private void SetOrdersToList(IDataRecord record)
{
AllOrdersModel adprModel = new AllOrdersModel();
ordersList.Add(adprModel);
for (int i = 0; i < record.FieldCount; i++)
{
ordersList[ordersList.Count - 1].WriteData(i, record[i]);
}
}
/* Заливаем новые/обновляем старые строки в БД */
public void btn_saveToDB_Click(object sender, EventArgs e)
{
newOrdersList = new List <AllOrdersModel> {
};
string specifier = "G";
string sqlStatement;
connection.Open();
progressBar1.Maximum = ordersList.Count;
progressBar1.Value = 0;
progressBar1.Visible = true;
for (int i = 0; i < ordersList.Count; i++)
{
sqlStatement = "INSERT INTO [Orders] ([AmazonOrderId], [MerchantOrderId], [PurchaseDate], [LastUpdatedDate], [OrderStatus], [FullfilmentChannel], [SalesChannel], [OrderChannel], [Url], [ShipServiceLevel], [ProductName], [Sku], [Asin], [ItemStatus], [Quantity], [Currency], [ItemPrice], [ItemTax], [ShippingPrice], [ShippingTax], [GiftWrapPrice], [GiftWrapTax], [ItemPromotionDiscount], [ShipPromotionDiscount], [ShipCity], [ShipState], [ShipPostalCode], [ShipCountry], [PromotionIds], [IsBusinessOrder], [PurchaseOrderNumber], [PriceDesignation], [MarketPlaceId]) VALUES ('" + ordersList[i].AmazonOrderId + "', '" + ordersList[i].MerchantOrderId + "', '" + ordersList[i].PurchaseDate.ToString("yyyy-MM-dd") + "', '" + ordersList[i].LastUpdatedDate.ToString("yyyy-MM-dd") + "', '" + ordersList[i].OrderStatus + "', '" + ordersList[i].FullfilmentChannel + "', '" + ordersList[i].SalesChannel + "', '" + ordersList[i].OrderChannel + "', '" + ordersList[i].Url + "', '" + ordersList[i].ShipServiceLevel + "', '" + ordersList[i].ProductName + "', '" + ordersList[i].Sku + "', '" + ordersList[i].Asin + "', '" + ordersList[i].ItemStatus + "', " + ordersList[i].Quantity + ", '" + ordersList[i].Currency + "', " + ordersList[i].ItemPrice.ToString(specifier, CultureInfo.InvariantCulture) + ", " + ordersList[i].ItemTax.ToString(specifier, CultureInfo.InvariantCulture) + ", " + ordersList[i].ShippingPrice.ToString(specifier, CultureInfo.InvariantCulture) + ", " + ordersList[i].ShippingTax.ToString(specifier, CultureInfo.InvariantCulture) + ", " + ordersList[i].GiftWrapPrice.ToString(specifier, CultureInfo.InvariantCulture) + ", " + ordersList[i].GiftWrapTax.ToString(specifier, CultureInfo.InvariantCulture) + ", " + ordersList[i].ItemPromotionDiscount.ToString(specifier, CultureInfo.InvariantCulture) + ", " + ordersList[i].ShipPromotionDiscount.ToString(specifier, CultureInfo.InvariantCulture) + ", '" + ordersList[i].ShipCity + "', '" + ordersList[i].ShipState + "', '" + ordersList[i].ShipPostalCode + "', '" + ordersList[i].ShipCountry + "', '" + ordersList[i].PromotionIds + "', '" + ordersList[i].IsBusinessOrder + "', '" + ordersList[i].PurchaseOrderNumber + "', '" + ordersList[i].PriceDesignation + "', " + ordersList[i].MarketPlaceId + ")";
try
{
SqlCommand command = new SqlCommand(sqlStatement, connection);
command.ExecuteScalar();
addedLines++;
}
catch (Exception ex)
{
if (ex.HResult == -2146232060)
{
AllOrdersModel om = new AllOrdersModel();
newOrdersList.Add(om);
for (int j = 0; j < om.FieldCount; j++)
{
newOrdersList[newOrdersList.Count - 1].WriteDataForUpdates(j, ordersList[i].ReadData(j));
}
}
}
progressBar1.Value++;
progressBar1.Refresh();
}
progressBar1.Visible = false;
connection.Close();
if (newOrdersList.Count > 0)
{
UpdateExistingOrdersInDB();
}
MessageBox.Show("Всего записей: " + allLines + "\nДобавлено новых записей: " + addedLines + "\nОбновлено записей: " + updatedLines);
}
/* Вытаскиваем строки из Excel */
public void GetOrdersFromExcel()//bool update)
{
openFileDialog1.Filter = "Выбери файл|*.csv;*.txt;*.xlsx";
openFileDialog1.Title = "Выбор файла для открытия";
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
using (ExcelPackage xlPackage = new ExcelPackage(new FileInfo(@openFileDialog1.FileName)))
{
label1.Text = @openFileDialog1.FileName;
ordersList = new List <AllOrdersModel> {
};
allLines = 0;
addedLines = 0;
updatedLines = 0;
ExcelWorksheet workSheet = xlPackage.Workbook.Worksheets.First();
var start = workSheet.Dimension.Start;
var end = workSheet.Dimension.End;
AllOrdersModel checkFields = new AllOrdersModel();
if (end.Column != checkFields.FieldCount)
{
MessageBox.Show("Выбранный файл не соответствует нужному формату отчета. Возможно, ошибочно был загружен некорректный файл. Попробуйте загрузить корректный файл.", "Ошибка");
return;
}
progressBar1.Maximum = end.Row;
progressBar1.Value = 0;
progressBar1.Visible = true;
for (int row = start.Row + 1; row <= end.Row; row++)
{
AllOrdersModel om = new AllOrdersModel();
ordersList.Add(om);
for (int col = start.Column; col <= end.Column; col++)
{
ordersList[ordersList.Count - 1].WriteData(col - 1, workSheet.Cells[row, col].Text);
}
ordersList[ordersList.Count - 1].MarketPlaceId = GetMarketPlaceIdByName(ordersList[ordersList.Count - 1].SalesChannel);
allLines++;
progressBar1.Value++;
progressBar1.Refresh();
}
progressBar1.Visible = false;
//if (!update)
//SetNewOrdersToDB();
}
}
}
/* Заносим данные в List<AdvertisingProductsModel> */
private void SetSalesToList(IDataRecord record, int _val)
{
AllOrdersModel adprModel = new AllOrdersModel();
for (int i = 0; i < record.FieldCount; i++)
{
adprModel.WriteDataForUpdates(i, record[i]);
}
if (_val == 7)
{
sales7daysList.Add(adprModel);
}
else if (_val == 30)
{
sales30daysList.Add(adprModel);
}
}
/* Заливаем новые/обновляем старые строки в БД */
public void btn_saveToDB_Click(object sender, EventArgs e)
{
if (ordersList.Count > 0)
{
if (MessageBox.Show("Загрузить отчет?", "Подтвердите действие", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
this.Enabled = false;
this.Cursor = Cursors.WaitCursor;
rtb_State.Text += "\n\nСохранение данных...";
rtb_State.Refresh();
newOrdersList = new List <AllOrdersModel> {
};
string specifier = "G";
string sqlStatement;
connection.Open();
progressBar1.Maximum = ordersList.Count;
progressBar1.Value = 0;
progressBar1.Visible = true;
for (int i = 0; i < ordersList.Count; i++)
{
if (ordersList[i].MarketPlaceId != -1 && (ordersList[i].ItemStatus.Equals("Shipped") || ordersList[i].ItemStatus.Equals("Shipping")))
{
sqlStatement = "INSERT INTO [Orders] ([AmazonOrderId], [MerchantOrderId], [PurchaseDate], [LastUpdatedDate], [OrderStatus], [FullfilmentChannel], [SalesChannel], [OrderChannel], [Url], [ShipServiceLevel], [ProductName], [Sku], [Asin], [ItemStatus], [Quantity], [Currency], [ItemPrice], [ItemTax], [ShippingPrice], [ShippingTax], [GiftWrapPrice], [GiftWrapTax], [ItemPromotionDiscount], [ShipPromotionDiscount], [ShipCity], [ShipState], [ShipPostalCode], [ShipCountry], [PromotionIds], [IsBusinessOrder], [PurchaseOrderNumber], [PriceDesignation], [MarketPlaceId]) VALUES ('" + ordersList[i].AmazonOrderId + "', '" + ordersList[i].MerchantOrderId + "', '" + ordersList[i].PurchaseDate.ToString("yyyy-MM-dd") + "', '" + ordersList[i].LastUpdatedDate.ToString("yyyy-MM-dd") + "', '" + ordersList[i].OrderStatus + "', '" + ordersList[i].FullfilmentChannel + "', '" + ordersList[i].SalesChannel + "', '" + ordersList[i].OrderChannel + "', '" + ordersList[i].Url + "', '" + ordersList[i].ShipServiceLevel + "', '" + ordersList[i].ProductName + "', '" + ordersList[i].Sku + "', '" + ordersList[i].Asin + "', '" + ordersList[i].ItemStatus + "', " + ordersList[i].Quantity + ", '" + ordersList[i].Currency + "', " + ordersList[i].ItemPrice.ToString(specifier, CultureInfo.InvariantCulture) + ", " + ordersList[i].ItemTax.ToString(specifier, CultureInfo.InvariantCulture) + ", " + ordersList[i].ShippingPrice.ToString(specifier, CultureInfo.InvariantCulture) + ", " + ordersList[i].ShippingTax.ToString(specifier, CultureInfo.InvariantCulture) + ", " + ordersList[i].GiftWrapPrice.ToString(specifier, CultureInfo.InvariantCulture) + ", " + ordersList[i].GiftWrapTax.ToString(specifier, CultureInfo.InvariantCulture) + ", " + ordersList[i].ItemPromotionDiscount.ToString(specifier, CultureInfo.InvariantCulture) + ", " + ordersList[i].ShipPromotionDiscount.ToString(specifier, CultureInfo.InvariantCulture) + ", '" + ordersList[i].ShipCity + "', '" + ordersList[i].ShipState + "', '" + ordersList[i].ShipPostalCode + "', '" + ordersList[i].ShipCountry + "', '" + ordersList[i].PromotionIds + "', '" + ordersList[i].IsBusinessOrder + "', '" + ordersList[i].PurchaseOrderNumber + "', '" + ordersList[i].PriceDesignation + "', " + ordersList[i].MarketPlaceId + ")";
try
{
SqlCommand command = new SqlCommand(sqlStatement, connection);
command.ExecuteScalar();
addedLines++;
}
catch (Exception ex)
{
if (ex.HResult == -2146232060)
{
AllOrdersModel om = new AllOrdersModel();
newOrdersList.Add(om);
for (int j = 0; j < om.FieldCount; j++)
{
newOrdersList[newOrdersList.Count - 1].WriteDataForUpdates(j, ordersList[i].ReadData(j));
}
}
}
}
progressBar1.Value++;
progressBar1.Refresh();
}
progressBar1.Visible = false;
connection.Close();
if (newOrdersList.Count > 0)
{
rtb_State.Text += "\n\nОбновление данных...";
rtb_State.Refresh();
UpdateExistingOrdersInDB();
}
rtb_State.Text += "\n\nВсего одобрено записей: " + (addedLines + updatedLines).ToString() + " из " + allLines + "\nДобавлено новых записей: " + addedLines + "\nОбновлено записей: " + updatedLines;
rtb_State.Refresh();
this.Cursor = Cursors.Default;
this.Enabled = true;
}
}
else
{
MessageBox.Show("Нет данных для сохранения", "Ошибка");
}
}