예제 #1
0
        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;
        }