示例#1
0
        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();
        }
示例#2
0
        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();
        }
示例#3
0
        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();
        }