示例#1
0
        public decimal GetTotalPaymentsByCustomerId(int CustomerId, DateTime InvoiceDate)
        {
            GenericQuery query = new GenericQuery();
            string       tot   = "0";

            try
            {
                string sql = "select sum(p.amount)as TotPayments from payment p";
                sql += " join salesinvoiceheader s on p.invoiceid=s.invoiceid";
                sql += " where s.accountnumber = '" + CustomerId.ToString() + "'";
                sql += " and s.invoicedate <='" + InvoiceDate.ToShortDateString() + "'";
                DataSet ds = query.GetDataSet(false, sql);
                tot = ds.Tables[0].Rows[0][0].ToString();
                ds  = null;
                if (tot == String.Empty || tot == null)
                {
                    tot = "0";
                }
            }
            catch (Exception ex)
            {
                log.Write(ex.Message, "GetTotalPaymentsByCustomerId");
                throw(ex);
            }
            return(decimal.Parse(tot));
        }
        public decimal GetTotalInvoicesByCustomerId(int CustomerId, DateTime ShipDate)
        {
            GenericQuery query = new GenericQuery();
            string       tot   = "0";
            string       sql   = "select sum(totaldue) from salesorderheader h";

            // sql += " join salesorderdetail d on h.salesorderid=d.salesorderid";
            sql += " where h.customerid=" + CustomerId.ToString() + " and status != 6 and ShipDate <='" + ShipDate.ToShortDateString() + "'";
            try
            {
                DataSet ds = query.GetDataSet(false, sql);
                tot = ds.Tables[0].Rows[0][0].ToString();
                ds  = null;
                if (tot == String.Empty || tot == null)
                {
                    tot = "0";
                }
            }
            catch (Exception ex)
            {
                log.Write(ex.Message, "GetTotalPaymentsByCustomerId");
                throw (ex);
            }
            return(decimal.Parse(tot));
        }
示例#3
0
        public DataSet GetSalesTerritoryHistory(int TerritoryId)
        {
            string sql = "select * from salesterritoryhistory where territoryid=" + TerritoryId.ToString();

            sql += " and enddate is null";
            GenericQuery query = new GenericQuery();
            DataSet      ds    = null;

            try
            {
                ds = query.GetDataSet(false, sql);
            }
            catch (Exception ex)
            {
                log.Write(ex.Message, "GetSalesTerritoryHistory");
                throw (ex);
            }
            return(ds);
        }
示例#4
0
        public DataSet GetSalesTerritoryBySalesPerson()
        {
            DataSet ds = new DataSet();

            try
            {
                string sql = "select ter.territoryid,ter.name [Territory],(select distinct(salespersonid) from salesterritoryhistory his where his.territoryid=ter.territoryid and startdate <= getdate() and (enddate >= getdate() or enddate is null)) as SalesPersonID,";
                sql += "(select distinct(ID) from salesterritoryhistory his where his.territoryid=ter.territoryid and startdate <= getdate() and (enddate >= getdate() or enddate is null)) as ID,";
                sql += "(select (rtrim(emp.firstName) + ' ' + rtrim(emp.middleName) + ' ' + rtrim(emp.lastName)) from salesterritoryhistory his,employee emp where his.salespersonid=emp.employeeid and his.territoryid=ter.territoryid and startdate <= getdate() and (enddate >= getdate() or enddate is null)) as [Sales Person],";
                sql += "(select distinct(startdate) from salesterritoryhistory his where his.territoryid=ter.territoryid and startdate <= getdate() and (enddate >= getdate() or enddate is null)) as [Start Date],";
                sql += "(select distinct(enddate) from salesterritoryhistory his where his.territoryid=ter.territoryid and startdate <= getdate() and (enddate >= getdate() or enddate is null)) as [End Date] ";
                sql += " from salesterritory ter";
                GenericQuery q = new GenericQuery();
                ds = q.GetDataSet(false, sql);
            }
            catch (Exception ex)
            {
                log.Write(ex.Message, "GetSalesTerritoryBySalesPerson");
                throw(ex);
            }

            return(ds);
        }