private void btnThisMonthInvoiceSL_Click(object sender, EventArgs e) { string temp = lblInvoicethisMonthSL.Text; double value = 0; if (temp.Contains("£")) { temp = temp.Replace("£", ""); value = Convert.ToDouble(temp); } if (value > 0) { //open new form to display what doors are needed //also build string here and pass that over to allow for one form with minimal changes to the code //get the start and end date for the search DateTime dateStart = new DateTime(); string test; test = Convert.ToDateTime(cmbMonth.Text + " 01, " + cmbYear.Text).ToString("yyyy-MM-dd"); //test = test.ToString("yyyy-MM-dd"); dateStart = Convert.ToDateTime(test); string sql = "select a.id,COALESCE(b.id,0) as delivery_id,COALESCE(CAST(date_printed_delivery as nvarchar(max)),'N/A') as date_printed_delivery," + "COALESCE(CAST(date_printed_invoice as nvarchar(max)), 'N/A') as date_printed_invoice,g.[NAME],a.order_number,a.quantity_on_order, " + "quantity_same,f.status_description,a.date_completion,CASE WHEN COALESCE(e.payment_confirm, 0) = 0 THEN 'Not Comfirmed' WHEN COALESCE(e.payment_confirm,0) = 1 THEN 'Confirmed' END as [payment_confirmed], " + "COALESCE(h.line_total, 0) as Door_cost from dbo.door a " + "LEFT JOIN dbo.invoice_door b ON a.id = b.door_id " + "LEFT JOIN dbo.invoice c ON b.invoice_id = c.id " + "LEFT JOIN dbo.door_type d ON a.door_type_id = d.id " + "LEFT JOIN dbo.door_payment e ON a.id = e.door_id " + "LEFT JOIN dbo.[status] f on status_id = f.id " + "LEFT JOIN dbo.SALES_LEDGER g ON a.customer_acc_ref = g.ACCOUNT_REF " + "LEFT JOIN dbo.view_door_value h ON a.id = h.id " + "where date_printed_invoice is null AND" + " (slimline_y_n = -1) and(status_id = 1 or status_id = 2 or status_id = 3 or status_id = 5) AND " + "a.date_completion >= '" + dateStart.ToString("yyyy - MM - dd") + "' AND a.date_completion < DATEADD(month,1,'" + dateStart.ToString("yyyy - MM - dd") + "')" + " ORDER BY a.date_completion ASC"; //open the form frmInvoice frm = new frmInvoice(sql, 1); frm.ShowDialog(); } }
private void btnPrevInvoices_Click(object sender, EventArgs e) { string temp = lblProformaPrevious.Text; double value = 0; if (temp.Contains("£")) { temp = temp.Replace("£", ""); value = Convert.ToDouble(temp); } if (value > 0) { //open new form to display what doors are needed //also build string here and pass that over to allow for one form with minimal changes to the code //get the start and end date for the search DateTime dateStart = new DateTime(); string test; test = Convert.ToDateTime(cmbMonth.Text + " 01, " + cmbYear.Text).ToString("yyyy-MM-dd"); //test = test.ToString("yyyy-MM-dd"); dateStart = Convert.ToDateTime(test); string sql = "select a.id,CAST(date_created as date) as [date_created], " + "CASE WHEN CAST(date_printed_invoice as varchar) is null then 'Not printed' ELSE CAST(CAST(date_printed_invoice as date) as varchar) END as [date_printed_invoice], " + "CASE WHEN CAST(proforma_last_chased as varchar) is null then 'Not chased' ELSE CAST(CAST(proforma_last_chased as date) as varchar) END as [proforma_last_chased], " + "CASE WHEN b.[description] is null then 'N/A' ELSE b.[description] END as [description], " + "CASE WHEN notes is null then 'N/A' ELSE notes END as notes, " + "ROUND(COALESCE(b.cost,0),2) as [cost], " + "ROUND(COALESCE(b.delivery_cost,0),2) as [delivery_cost], " + "CASE WHEN is_install = -1 then 'Yes' ELSE 'No'END as install, " + "CASE WHEN is_slimline = -1 then 'Yes' ELSE 'No' END as slimline " + "FROM dbo.invoice a " + "INNER JOIN dbo.invoice_freehand b ON a.id = b.invoice_id " + "where a.date_created >= DATEADD(DAY,-60,'" + dateStart.ToString("yyyy - MM - dd") + "') AND a.date_created < DATEADD(month,1,'" + dateStart.ToString("yyyy - MM - dd") + "') and a.invoice_type_id = 3 " + "ORDER BY a.date_created ASC"; //open the form frmInvoice frm = new frmInvoice(sql, 2); frm.ShowDialog(); } }