public List<PaymentReport> GetPaymentReport(PaymentQuery query)
{
//...Create New Instance of Object...
List<PaymentReport> list = new List<PaymentReport>();
PaymentReport ins;
if (query.ToDate.Year.Equals(1))
{
query.ToDate = new DateTime(2015, 12, 31); ; //...Max date of Report (Can be changed anytime)
}
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
/*
"Select g.InvoiceNumber, g.PinkSlipNumber, g.ExpectedPayDate, g.PayDate, g.InvoiceDate, g.StateDate "
+ ", p.ActualDate, p.PaymentDescription, c.CashType, g.ExcludingVat, g.IncludingVat, p.Amount, s.Supplier "
+ " from t_GRVList g "
+ " inner join t_ProofOfPayment p on g.InvoiceNumber = p.InvoiceNumber "
+ " inner join t_Supplier s on g.SupplierID = s.SupplierID"
+ " inner join l_CashType c on p.CashTypeID = c.CashTypeID "
+ " where p.ActualDate >= '" + query.FromDate.ToShortDateString() + "' "
+ " and p.ActualDate <= '" + query.ToDate.ToShortDateString() + "' "
+ " and g.InvoiceNumber LIKE '%" + query.InvoiceNumber + "' "
+ " and g.PinkSlipNumber LIKE '%" + query.PinkSlip + "' "
+ " and p.CashTypeID LIKE '%" + query.Paymenthod + "' "
+ " and g.SupplierID LIKE '%" + query.Supplier + "'"
*/
//...SQL Commands...
cmdI = new SqlCommand("Select g.InvoiceNumber, g.PinkSlipNumber, g.ExpectedPayDate, g.PayDate, g.InvoiceDate, g.StateDate "
+ " , COALESCE(p.ActualDate,'') as ActualDate "
+ " , COALESCE(p.PaymentDescription,'') as PaymentDescription "
+ " , COALESCE(c.CashType,'') as CashType "
+ " , g.ExcludingVat, g.IncludingVat "
+ " , COALESCE(p.Amount,0) as Amount "
+ " , s.Supplier "
+ " from t_GRVList g "
+ " left join t_ProofOfPayment p on g.InvoiceNumber = p.InvoiceNumber "
+ " inner join t_Supplier s on g.SupplierID = s.SupplierID "
+ " left join l_CashType c on p.CashTypeID = c.CashTypeID "
+ " where "
+ " g.InvoiceNumber LIKE '%" + query.InvoiceNumber + "' "
+ " and (g.PayDate >= '" + query.FromDate.ToShortDateString() + "' "
+ " and g.PayDate <= '" + query.ToDate.ToShortDateString() + "' "
//+ " and s.SupplierTypeID = 2 )"
+ (query.Supplier.Equals("%") ? "" : " and g.SupplierID = " + query.Supplier)
+ ")"
+ " or ( p.ActualDate >= '" + query.FromDate.ToShortDateString() + "' "
+ " and p.ActualDate <= '" + query.ToDate.ToShortDateString() + "' "
//+ " and s.SupplierTypeID = 2 )"
+ (query.Supplier.Equals("%") ? "" : " and g.SupplierID = " + query.Supplier)
+ ")"
+ " and g.PinkSlipNumber LIKE '%" + query.PinkSlip + "' "
+ " and COALESCE(p.CashTypeID,'') LIKE '%" + query.Paymenthod + "' "
//+ " and g.SupplierID LIKE '%" + query.Supplier + "'"
+ " ORDER BY p.ActualDate DESC, s.Supplier", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins = new PaymentReport();
ins.ActualDate = Convert.ToDateTime(drI["ActualDate"]).ToShortDateString();
ins.Amount = drI["Amount"].ToString();
ins.CashType = drI["CashType"].ToString();
ins.ExcludingVat = drI["ExcludingVat"].ToString();
ins.ExpectedPayDate = Convert.ToDateTime(drI["ExpectedPayDate"]).ToShortDateString();
ins.IncludingVat = drI["IncludingVat"].ToString();
ins.InvoiceDate = Convert.ToDateTime(drI["InvoiceDate"]).ToShortDateString();
ins.InvoiceNumber = drI["InvoiceNumber"].ToString();
ins.PayDate = Convert.ToDateTime(drI["PayDate"]).ToShortDateString();
ins.PaymentDescription = drI["PaymentDescription"].ToString();
ins.PinkSlipNumber = drI["PinkSlipNumber"].ToString();
ins.StateDate = Convert.ToDateTime(drI["StateDate"]).ToShortDateString();
ins.Supplier = drI["Supplier"].ToString();
list.Add(ins);
}
}
//...Close Connections...
drI.Close();
con.Close();
//...Return...
return list;
}
public List <PaymentReport> GetPaymentReport(PaymentQuery query)
{
//...Create New Instance of Object...
List <PaymentReport> list = new List <PaymentReport>();
PaymentReport ins;
if (query.ToDate.Year.Equals(1))
{
query.ToDate = new DateTime(2015, 12, 31);; //...Max date of Report (Can be changed anytime)
}
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
/*
* "Select g.InvoiceNumber, g.PinkSlipNumber, g.ExpectedPayDate, g.PayDate, g.InvoiceDate, g.StateDate "
+ ", p.ActualDate, p.PaymentDescription, c.CashType, g.ExcludingVat, g.IncludingVat, p.Amount, s.Supplier "
+ " from t_GRVList g "
+ " inner join t_ProofOfPayment p on g.InvoiceNumber = p.InvoiceNumber "
+ " inner join t_Supplier s on g.SupplierID = s.SupplierID"
+ " inner join l_CashType c on p.CashTypeID = c.CashTypeID "
+ " where p.ActualDate >= '" + query.FromDate.ToShortDateString() + "' "
+ " and p.ActualDate <= '" + query.ToDate.ToShortDateString() + "' "
+ " and g.InvoiceNumber LIKE '%" + query.InvoiceNumber + "' "
+ " and g.PinkSlipNumber LIKE '%" + query.PinkSlip + "' "
+ " and p.CashTypeID LIKE '%" + query.Paymenthod + "' "
+ " and g.SupplierID LIKE '%" + query.Supplier + "'"
*/
//...SQL Commands...
cmdI = new SqlCommand("Select g.InvoiceNumber, g.PinkSlipNumber, g.ExpectedPayDate, g.PayDate, g.InvoiceDate, g.StateDate "
+ " , COALESCE(p.ActualDate,'') as ActualDate "
+ " , COALESCE(p.PaymentDescription,'') as PaymentDescription "
+ " , COALESCE(c.CashType,'') as CashType "
+ " , g.ExcludingVat, g.IncludingVat "
+ " , COALESCE(p.Amount,0) as Amount "
+ " , s.Supplier "
+ " from t_GRVList g "
+ " left join t_ProofOfPayment p on g.InvoiceNumber = p.InvoiceNumber "
+ " inner join t_Supplier s on g.SupplierID = s.SupplierID "
+ " left join l_CashType c on p.CashTypeID = c.CashTypeID "
+ " where "
+ " g.InvoiceNumber LIKE '%" + query.InvoiceNumber + "' "
+ " and (g.PayDate >= '" + query.FromDate.ToShortDateString() + "' "
+ " and g.PayDate <= '" + query.ToDate.ToShortDateString() + "' "
//+ " and s.SupplierTypeID = 2 )"
+ (query.Supplier.Equals("%") ? "" : " and g.SupplierID = " + query.Supplier)
+ ")"
+ " or ( p.ActualDate >= '" + query.FromDate.ToShortDateString() + "' "
+ " and p.ActualDate <= '" + query.ToDate.ToShortDateString() + "' "
//+ " and s.SupplierTypeID = 2 )"
+ (query.Supplier.Equals("%") ? "" : " and g.SupplierID = " + query.Supplier)
+ ")"
+ " and g.PinkSlipNumber LIKE '%" + query.PinkSlip + "' "
+ " and COALESCE(p.CashTypeID,'') LIKE '%" + query.Paymenthod + "' "
//+ " and g.SupplierID LIKE '%" + query.Supplier + "'"
+ " ORDER BY p.ActualDate DESC, s.Supplier", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins = new PaymentReport();
ins.ActualDate = Convert.ToDateTime(drI["ActualDate"]).ToShortDateString();
ins.Amount = drI["Amount"].ToString();
ins.CashType = drI["CashType"].ToString();
ins.ExcludingVat = drI["ExcludingVat"].ToString();
ins.ExpectedPayDate = Convert.ToDateTime(drI["ExpectedPayDate"]).ToShortDateString();
ins.IncludingVat = drI["IncludingVat"].ToString();
ins.InvoiceDate = Convert.ToDateTime(drI["InvoiceDate"]).ToShortDateString();
ins.InvoiceNumber = drI["InvoiceNumber"].ToString();
ins.PayDate = Convert.ToDateTime(drI["PayDate"]).ToShortDateString();
ins.PaymentDescription = drI["PaymentDescription"].ToString();
ins.PinkSlipNumber = drI["PinkSlipNumber"].ToString();
ins.StateDate = Convert.ToDateTime(drI["StateDate"]).ToShortDateString();
ins.Supplier = drI["Supplier"].ToString();
list.Add(ins);
}
}
//...Close Connections...
drI.Close();
con.Close();
//...Return...
return(list);
}