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;
}
public ActionResult GetPaymentReport(PaymentQuery ins)
{
List<PaymentReport> report = reportrepo.GetPaymentReport(ins);
StringWriter sw = new StringWriter();
sw.WriteLine("\"Invoice Number \",\"Pink Slip Number\",\"Supplier\",\"Expected Pay Date\",\"Pay By Date\",\"Invoice Date\",\"State Date\",\"Actual Payment Date\","
+ "\"Payment Comment\",\"Payment Method\",\"Exculding Vat\",\"Including Vat\",\"Payment Amount\"");
string name = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString();
DateTime checker = new DateTime(1900, 1, 1);
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment;filename=Payments_from_" + ins.FromDate.ToShortDateString() + "_" + name + ".csv");
Response.ContentType = "text/csv";
sw.WriteLine("\"Paid\"");
foreach (PaymentReport ex in report)
{
if (!ex.ActualDate.Equals(checker.ToShortDateString()))
{
sw.WriteLine(string.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\",\"{8}\",\"{9}\",\"{10}\",\"{11}\",\"{12}\"",
ex.InvoiceNumber
, ex.PinkSlipNumber
, ex.Supplier
, ex.ExpectedPayDate
, ex.PayDate
, ex.InvoiceDate
, ex.StateDate
, ex.ActualDate
, ex.PaymentDescription
, ex.CashType
, ex.ExcludingVat
, ex.IncludingVat
, ex.Amount
));
}
}
sw.WriteLine("\" \"");
sw.WriteLine("\"Unpaid\"");
foreach (PaymentReport ex in report)
{
if (ex.ActualDate.Equals(checker.ToShortDateString()))
{
sw.WriteLine(string.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\",\"{8}\",\"{9}\",\"{10}\",\"{11}\",\"{12}\"",
ex.InvoiceNumber
, ex.PinkSlipNumber
, ex.Supplier
, ex.ExpectedPayDate
, ex.PayDate
, ex.InvoiceDate
, ex.StateDate
, "Unpaid" //ex.ActualDate
, ex.PaymentDescription
, ex.CashType
, ex.ExcludingVat
, ex.IncludingVat
, ex.Amount
));
}
}
Response.Write(sw.ToString());
Response.End();
return null;
}