private void заказыНаОтсутствующийТоварToolStripMenuItem_Click_1(object sender, EventArgs e)
{
String sql = @"WITH
inc AS(
select i.date as[Дата],
i.id_disk ,
d.record_name as [Название диска],
SUM(i.count) as [Поступило на склад шт],
SUM(i.cost_price) as [Суммарная себестоимость]
from income i
inner join [Disk] d on d.id_disk =i.id_disk
group by i.date,i.id_disk,d.record_name
),
outcome AS(
select o.date as [Дата],d.id_disk,d.record_name,SUM(o.count) as [Всего заказов],SUM(o.price) as [На общую сумму]
from Orders o
inner join disk d
ON o.id_disk=d.id_disk
where o.is_delivered=1 and o.is_payment=1
group by d.id_disk,d.record_name,o.date
)
select * from
outcome
where id_disk NOT IN (SELECT id_disk from inc ) ";
ReportForm f = new ReportForm(sql);
f.Show();
}
private void оптовыеToolStripMenuItem_Click(object sender, EventArgs e)
{
/*только оплаченные заказы, количество дисков в которых >=минимаьному кол-ву шт для опта*/
String sql = @"
SELECT disk.record_name as [Диск],Orders.date as [Дата заказа], Orders.price AS[Продано за], Orders.count AS Количество, [Disk].rozn_price AS[Розничная цена на товар], Opt_price.price AS[Оптовая цена на товар],
Opt_price.min_count AS[мин.кол - во для оптовой продажи],
Orders.is_payment AS[Оплачено]
FROM[Disk] INNER JOIN
Disk_type ON[Disk].id_disk_type = Disk_type.id_disk_type INNER JOIN
Opt_price ON[Disk].id_opt_pric = Opt_price.id_opt_price INNER JOIN
Orders ON[Disk].id_disk = Orders.id_disk
where Orders.count>=Opt_price.min_count
and orders.is_payment=1;
";
ReportForm f = new ReportForm(sql);
f.Show();
}
private void дискиВНаличииToolStripMenuItem_Click_1(object sender, EventArgs e)
{
String sql = @"SELECT date as Дата,
id_disk as [ID диска],
record_name as [Название диска],
inc as [Всего поступило],
outc as [Всего продано] ,
inc-outc as [Остаток]
FROM(
SELECT orders.date ,disk.id_disk, [Disk].record_name,SUM(Income.count) as inc,SUM(Orders.count)as outc
FROM [Disk] INNER JOIN
Income ON [Disk].id_disk = Income.id_disk INNER JOIN
Orders ON [Disk].id_disk = Orders.id_disk
where Income.date=Orders.date
group by orders.date,disk.id_disk, [Disk].record_name
)tbl1";
ReportForm f = new ReportForm(sql);
f.Show();
}