// Заявка на подоконники ВСЕХ производителей
public static void GetAllWindowsillExport(int supplyDocumentId)
{
FolderBrowserDialog fbDialog = new FolderBrowserDialog();
if (fbDialog.ShowDialog() != DialogResult.OK)
{
return;
}
// -- Информация по контрагенту фабрики окон
Contractor contractor = Contractor.GetContractor();
// -- Информация по документу
string supplyDocName = string.Empty;
DateTime supplyDocDate;
using (SqlCommand command = new SqlCommand())
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
command.Connection = dbinit.db;
command.CommandText = "SELECT name, dtdoc FROM supplydoc WHERE idsupplydoc = @idsupplydoc";
command.Parameters.AddWithValue("@idsupplydoc", supplyDocumentId);
DataTable datatable = new DataTable();
adapter.Fill(datatable);
supplyDocName = (string)datatable.Rows[0]["name"];
supplyDocDate = (DateTime)datatable.Rows[0]["dtdoc"];
}
List <IDocExporter> exporters = new List <IDocExporter>();
exporters.Add(new CrystalitSupplyExporter(supplyDocumentId));
exporters.Add(new TsvetoslavSupplyExporter(supplyDocumentId));
exporters.Add(new TbmSupplyExporter(supplyDocumentId));
foreach (IDocExporter exporter in exporters)
{
exporter.DocName = supplyDocName;
exporter.DocDate = supplyDocDate;
exporter.FileDirecoryName = fbDialog.SelectedPath;
exporter.Export();
}
}
internal static Contractor GetContractor()
{
if (_instance == null)
{
SqlConnection connection = dbinit.db;
using (SqlCommand command = new SqlCommand("SELECT name, inn, address FROM customer WHERE idcustomer = 40860", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
DataRow row = dataTable.Rows[0];
_instance = new Contractor((string)row["name"], (string)row["inn"], (string)row["address"]);
}
}
return(_instance);
}
public void Export()
{
DataTable tbmTable = new DataTable("TBM");
using (SqlCommand command = new SqlCommand())
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
command.Connection = dbinit.db;
command.CommandText = @"
SELECT
g.marking
--, g.name AS name
, sdp.thick
, sdp.comment AS manufact_name
, dbo.fo_getBarCodeValue_mc(sdp.fo_idmodelcalc) AS bar_code
, ROW_NUMBER() OVER(PARTITION BY sdp.comment ORDER BY sdp.fo_idmodelcalc) AS order_nn
, COUNT(*) OVER(PARTITION BY sdp.comment) AS order_count
FROM supplydocpos AS sdp
INNER JOIN good AS g ON sdp.idgood = g.idgood
WHERE sdp.idsupplydoc = @idsupplydoc
AND sdp.deleted IS NULL
AND g.idgoodtype = 13
AND g.idcustomer = 60520 -- Поставщик ТБМ";
command.Parameters.AddWithValue("@idsupplydoc", supplyDocumentId);
adapter.Fill(tbmTable);
}
XElement xWindowsills = new XElement("Windowsills");
foreach (DataRow row in tbmTable.Rows)
{
//string name = (string)row["name"];
string article = (string)row["marking"]; // Артикул
double length = ((int)row["thick"]) / 10.0;
string manufactName = (string)row["manufact_name"];
// -- "Номер в заказе"
int orderNN = Convert.ToInt32(row["order_nn"]);
// -- Общее количество в заказе
int orderCount = Convert.ToInt32(row["order_count"]);
string marking = string.Concat(manufactName, " ", orderNN, "/", orderCount);
// -- Штрих код
string barCode = (string)row["bar_code"];
xWindowsills.Add(new XElement("Windowsill",
//new XAttribute("Name", name),
new XAttribute("Article", article),
new XAttribute("Length", length.ToString()), //ToString дает запятую вместо точки.
new XAttribute("Marking", marking),
new XAttribute("BarCode", barCode)));
}
Contractor contractor = Contractor.GetContractor();
XElement xHead = new XElement("RequestInfo",
new XAttribute("CustCode", 31625),
new XAttribute("RequestDate", DocDate),
new XAttribute("DeliveryDate", DocDate.AddDays(2)),
new XAttribute("DeliveryAddress", contractor.Address));
XElement result = new XElement("Request",
new XComment("RequestInfo - Информация по заявке."),
new XComment("CustCode - шифр клиента"),
new XComment("RequestDate - Дата заявки"),
new XComment("DeliveryDate - Дата доставки"),
new XComment("DeliveryAddress - Адрес доставки"));
result.Add(xHead);
result.Add(new XComment("Windowsills - подоконники"));
result.Add(new XComment("Article - Артикул"));
result.Add(new XComment("Length - длина подоконика в см."));
result.Add(new XComment("Marking - маркировка"));
result.Add(new XComment("BarCode - Штрих код"));
result.Add(xWindowsills);
result.Save(FullFileName);
}
public override void Export()
{
Contractor contractor = Contractor.GetContractor();
// -- Заполняем информацию по подоконникам
DataSet tsvetoslav = new DataSet("Tsvetoslav");
using (SqlCommand command = new SqlCommand())
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
command.Connection = dbinit.db;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "FastExportGetTsvetoslav";
command.Parameters.AddWithValue("@idsupplydoc", supplyDocumentId);
adapter.Fill(tsvetoslav);
File.WriteAllBytes(FullFileName, fo_library.FastExport.Properties.Resources.TsvetoslavWindowsillSupplyTemplate);
Excel.Application excelapp = new Excel.Application();
excelapp.Visible = true;
Excel.Workbook excelappworkbook = excelapp.Workbooks.Open(FullFileName);
Excel.Sheets excelsheets = excelappworkbook.Worksheets;
Excel.Worksheet excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
excelworksheet.Cells[1, 3] = contractor.Inn;
excelworksheet.Cells[1, 4] = contractor.Name;
excelworksheet.Cells[4, 3] = contractor.Address;
excelworksheet.Cells[6, 4] = DocName;
int firstExcelRow = 9;
int currentExcelRow = 0;
int rowNumber = 0;
foreach (DataRow row in tsvetoslav.Tables[0].Rows)
{
currentExcelRow = firstExcelRow + rowNumber++;
string name = (string)row["name"];
int width = (int)row["width"];
int lenght = (int)row["thick"];
int count = Convert.ToInt32(row["qu"]);
//string marking = (string)row["marking"];
string manufactName = (string)row["manufact_name"];
string color = (string)row["color_name"];
color = color.Substring(0, color.IndexOf(' ') + 1);
excelworksheet.Cells[currentExcelRow, 1] = rowNumber;
excelworksheet.Cells[currentExcelRow, 2] = name;
((Excel.Range)excelworksheet.Cells[currentExcelRow, 3]).NumberFormat = "@";
excelworksheet.Cells[currentExcelRow, 3] = color;
excelworksheet.Cells[currentExcelRow, 4] = width;
excelworksheet.Cells[currentExcelRow, 5] = lenght;
excelworksheet.Cells[currentExcelRow, 6] = count;
excelworksheet.Cells[currentExcelRow, 7] = manufactName;
}
foreach (DataRow row in tsvetoslav.Tables[1].Rows)
{
currentExcelRow = firstExcelRow + rowNumber++;
string name = (string)row["name"];
string color = (string)row["color_name"];
int count = Convert.ToInt32(row["quantity"]);
color = color.Substring(0, color.IndexOf(' ') + 1);
excelworksheet.Cells[currentExcelRow, 1] = rowNumber;
excelworksheet.Cells[currentExcelRow, 2] = name;
((Excel.Range)excelworksheet.Cells[currentExcelRow, 3]).NumberFormat = "@";
excelworksheet.Cells[currentExcelRow, 3] = color;
excelworksheet.Cells[currentExcelRow, 6] = count;
}
}
}
public void Export_OLD()
{
Contractor contractor = Contractor.GetContractor();
// -- Заполняем информацию по подоконникам
DataSet tsvetoslav = new DataSet("Tsvetoslav");
using (SqlCommand command = new SqlCommand())
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
command.Connection = dbinit.db;
command.CommandText = @"SELECT
--g.marking
g.name AS name
, sdp.comment AS manufact_name
, sdp.width
, sdp.thick
, sdp.qu
, c.name AS color_name
FROM supplydocpos AS sdp
INNER JOIN good AS g ON sdp.idgood = g.idgood
INNER JOIN orderitem AS oi ON sdp.idorderitem = oi.idorderitem
LEFT JOIN color AS c ON oi.idcolorout = c.idcolor
WHERE sdp.idsupplydoc = @idsupplydoc
AND g.idcustomer = 43300 -- Поставщик Цветослав
AND sdp.deleted IS NULL
AND g.idgoodtype = 13
SELECT
g.name AS name
, c.name AS color_name
, SUM(sdp.qu) AS quantity
FROM supplydocpos AS sdp
INNER JOIN good AS g ON sdp.idgood = g.idgood
INNER JOIN orderitem AS oi ON sdp.idorderitem = oi.idorderitem
LEFT JOIN color AS c ON oi.idcolorout = c.idcolor
WHERE sdp.idsupplydoc = @idsupplydoc
AND g.idcustomer = 43300
AND sdp.deleted IS NULL
AND g.idgoodtype2 = 13
GROUP BY g.name, c.name";
command.Parameters.AddWithValue("@idsupplydoc", supplyDocumentId);
adapter.Fill(tsvetoslav);
File.WriteAllBytes(FullFileName, fo_library.FastExport.Properties.Resources.TsvetoslavWindowsillSupplyTemplate);
Excel.Application excelapp = new Excel.Application();
excelapp.Visible = true;
Excel.Workbook excelappworkbook = excelapp.Workbooks.Open(FullFileName);
Excel.Sheets excelsheets = excelappworkbook.Worksheets;
Excel.Worksheet excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
excelworksheet.Cells[1, 3] = contractor.Inn;
excelworksheet.Cells[1, 4] = contractor.Name;
excelworksheet.Cells[4, 3] = contractor.Address;
excelworksheet.Cells[6, 4] = DocName;
int firstExcelRow = 9;
int currentExcelRow = 0;
int rowNumber = 0;
foreach (DataRow row in tsvetoslav.Tables[0].Rows)
{
currentExcelRow = firstExcelRow + rowNumber++;
string name = (string)row["name"];
int width = (int)row["width"];
int lenght = (int)row["thick"];
int count = Convert.ToInt32(row["qu"]);
//string marking = (string)row["marking"];
string manufactName = (string)row["manufact_name"];
string color = (string)row["color_name"];
color = color.Substring(0, color.IndexOf(' ') + 1);
excelworksheet.Cells[currentExcelRow, 1] = rowNumber;
excelworksheet.Cells[currentExcelRow, 2] = name;
((Excel.Range)excelworksheet.Cells[currentExcelRow, 3]).NumberFormat = "@";
excelworksheet.Cells[currentExcelRow, 3] = color;
excelworksheet.Cells[currentExcelRow, 4] = width;
excelworksheet.Cells[currentExcelRow, 5] = lenght;
excelworksheet.Cells[currentExcelRow, 6] = count;
excelworksheet.Cells[currentExcelRow, 7] = manufactName;
}
foreach (DataRow row in tsvetoslav.Tables[1].Rows)
{
currentExcelRow = firstExcelRow + rowNumber++;
string name = (string)row["name"];
string color = (string)row["color_name"];
int count = Convert.ToInt32(row["quantity"]);
color = color.Substring(0, color.IndexOf(' ') + 1);
excelworksheet.Cells[currentExcelRow, 1] = rowNumber;
excelworksheet.Cells[currentExcelRow, 2] = name;
((Excel.Range)excelworksheet.Cells[currentExcelRow, 3]).NumberFormat = "@";
excelworksheet.Cells[currentExcelRow, 3] = color;
excelworksheet.Cells[currentExcelRow, 6] = count;
}
}
}
public override void Export()
{
Contractor contractor = Contractor.GetContractor();
// -- Заполняется информация по подоконникам Crystalit.
DataSet dataset = new DataSet("Crystalit");
using (SqlCommand command = new SqlCommand())
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
command.Connection = dbinit.db;
command.CommandText = @"fo_GetSupplySillForExport";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@idsupplydoc", supplyDocumentId);
adapter.Fill(dataset);
File.WriteAllBytes(FullFileName, fo_library.FastExport.Properties.Resources.SupplyWindowsillTemplate);
Excel.Application excelapp = new Excel.Application();
excelapp.Visible = true;
Excel.Workbook excelappworkbook = excelapp.Workbooks.Open(FullFileName);
Excel.Sheets excelsheets = excelappworkbook.Worksheets;
Excel.Worksheet excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
excelworksheet.Cells[1, 3] = contractor.Inn;
excelworksheet.Cells[1, 4] = contractor.Name;
excelworksheet.Cells[4, 3] = contractor.Address;
((Excel.Range)excelworksheet.Cells[1, 9]).NumberFormat = "@";
excelworksheet.Cells[1, 9] = DocName;
excelworksheet.Cells[2, 9] = DocDate;
int firstExcelRow = 7;
int currentExcelRow = 0;
int rowNumber = 0;
foreach (DataRow row in dataset.Tables[0].Rows)
{
currentExcelRow = firstExcelRow + rowNumber++;
// -- Получение артикля и цвета из "нашего" артикля
string rowMarking = (string)row["marking"];
string marking = string.Empty;
string color = string.Empty;
if (rowMarking.IndexOf("-") > 0)
{
marking = rowMarking.Substring(0, rowMarking.LastIndexOf("-"));
color = rowMarking.Substring(rowMarking.LastIndexOf("-") + 1);
}
else if (rowMarking.IndexOf("(") > 0)
{
marking = rowMarking.Substring(0, rowMarking.LastIndexOf("("));
color = rowMarking.Substring(rowMarking.LastIndexOf("(") + 1);
color = color.TrimEnd(')');
}
else
{
marking = rowMarking;
}
// -- Наименование материал
string name = (string)row["name"];
// -- Ширина
int width = (int)row["width"];
// -- Длина
int length = (int)row["thick"];
// -- Кол-во
int count = 1;
// -- Штрих код
string barCode = (string)row["bar_code"];
// -- Номер производственного задания
string manufactname = (string)row["manufact_name"];
// -- "Номер в заказе"
int orderNN = Convert.ToInt32(row["order_nn"]);
// -- Общее количество в заказе
int orderCount = Convert.ToInt32(row["order_count"]);
excelworksheet.Cells[currentExcelRow, 1] = rowNumber;
excelworksheet.Cells[currentExcelRow, 2] = marking;
((Excel.Range)excelworksheet.Cells[currentExcelRow, 3]).NumberFormat = "@";
excelworksheet.Cells[currentExcelRow, 3] = color;
excelworksheet.Cells[currentExcelRow, 4] = name;
excelworksheet.Cells[currentExcelRow, 5] = width;
excelworksheet.Cells[currentExcelRow, 7] = length;
excelworksheet.Cells[currentExcelRow, 8] = count;
excelworksheet.Cells[currentExcelRow, 9] = string.Concat(barCode, "|", manufactname, " ", orderNN, "/", orderCount);
}
foreach (DataRow row in dataset.Tables[1].Rows)
{
currentExcelRow = firstExcelRow + rowNumber++;
string name = (string)row["name"];
string marking = (string)row["marking"];
string bar_code = (string)row["bar_code"];
int order_count = (int)Convert.ToDouble(row["order_count"].ToString());
excelworksheet.Cells[currentExcelRow, 1] = rowNumber;
excelworksheet.Cells[currentExcelRow, 2] = marking;
excelworksheet.Cells[currentExcelRow, 4] = name;
excelworksheet.Cells[currentExcelRow, 8] = 1;
excelworksheet.Cells[currentExcelRow, 9] = string.Concat(bar_code, " ", order_count);
}
}
}