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();
}
}
