Exemple #1
0
        private SalesReportModelItem GetTotalReport(int?contractorId, string yearMonthFrom, string yearMonthTo,
                                                    EntityEnum.DocumentTypeEnum doctype, int employeeId)
        {
            var result = new SalesReportModelItem();

            using (SqlConnection connection = SqlHelper.GetConnection(ConnectionString))
            {
                string contractorStr = " ";
                if (contractorId != null)
                {
                    contractorStr = " AND ContractorId = " + contractorId + " ";
                }

                using (SqlCommand command = connection.GetCommand(" " +
                                                                  "SELECT " +
                                                                  "SUM([Sum]) AS PurchaseSum, SUM([SaleSum]) AS SaleSum " +
                                                                  "FROM Document " +
                                                                  "WHERE DocumentTypeId = " + (int)doctype + GetEmployeeIdWhereString(employeeId) +
                                                                  "AND IsCommitted = 1 " + contractorStr +
                                                                  "AND CreatedOf >= '" + yearMonthFrom + "' AND CreatedOf < '" + yearMonthTo + "' ", CommandType.Text))

                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            //decimal purchaseSum = 0, saleSum = 0;
                            if (reader["PurchaseSum"] != DBNull.Value)
                            {
                                result.PurchaseSum = Convert.ToDecimal(reader["PurchaseSum"]);
                            }

                            if (reader["SaleSum"] != DBNull.Value)
                            {
                                result.SaleSum = Convert.ToDecimal(reader["SaleSum"]);
                            }

                            break;
                        }
                    }
                }
            }
            return(result);
        }
Exemple #2
0
        private JsonResult GetDocumentList(SkladJqGridModel gridModel,
                                           SkladDataContext datacontextModel, EntityEnum.DocumentTypeEnum docType)
        {
            string contractorIdStr = Request.QueryString["ContractorId"];

            if (!String.IsNullOrEmpty(contractorIdStr))
            {
                var contractor = datacontextModel.Contractors.Where(x =>
                                                                    x.Code.ToLower() == contractorIdStr.ToLower()).FirstOrDefault();
                if (contractor != null)
                {
                    // ContractorName
                    return(gridModel.DocumentGrid.DataBind(datacontextModel
                                                           .Documents.Include("Contractor").Where(x => x.ContractorId == contractor.ContractorId &&
                                                                                                  x.DocumentTypeId == (int)docType)));
                }
            }

            return(gridModel.DocumentGrid.DataBind(datacontextModel.Documents.Include("Contractor").Where(x =>
                                                                                                          x.DocumentTypeId == (int)docType)));
        }
Exemple #3
0
        private SalesReportModelItem GetReportByDate(DateTime date,
                                                     EntityEnum.DocumentTypeEnum docType, EntityEnum.ReportTypeEnum reportType, int employeeId)
        {
            var    result = new SalesReportModelItem();
            string datePartFrom = "", datePartTo = "";

            if (reportType == EntityEnum.ReportTypeEnum.ByMonth)
            {
                DateTime dateTo = date.AddMonths(1);
                datePartTo = SqlHelper.GetDateString(dateTo.Month, dateTo.Year);
            }
            else if (reportType == EntityEnum.ReportTypeEnum.ByDay)
            {
                DateTime dateTo = date.AddDays(1);
                datePartTo = SqlHelper.GetDateString(dateTo.Day, dateTo.Month, dateTo.Year);
            }
            datePartFrom = SqlHelper.GetDateString(date.Day, date.Month, date.Year);
            result       = GetTotalReport(null, datePartFrom, datePartTo, docType, employeeId);

            return(result);
        }
Exemple #4
0
        public SalesReportDataModel GetSalesReport(int yearFromNumber, int yearToNumber,
                                                   int monthFromNumber, int monthToNumber,
                                                   EntityEnum.DocumentTypeEnum docType, EntityEnum.ReportTypeEnum reportType, int employeeId)
        {
            var result = new List <SalesReportModel>();
            var data   = new SalesReportDataModel();
            List <SalesReportPeriodModel> periods = new List <SalesReportPeriodModel>();

            if (monthFromNumber > 12 || monthToNumber > 12 || monthToNumber < 1 || monthFromNumber < 1 ||
                yearFromNumber < 1901 || yearToNumber < 1901)
            {
                throw new ArgumentException();
            }

            string yearMonthFrom = SqlHelper.GetDateString(monthFromNumber, yearFromNumber);
            string yearMonthTo   = SqlHelper.GetDateString(monthToNumber, yearToNumber);

            using (SqlConnection connection = SqlHelper.GetConnection(ConnectionString))
            {
                string datePartInSelect = " ", datePartInGroupBy = " ", datePartInOrderBy = " ",
                       datePartCreatedOf = ",  [CreatedOf] " /*, employeeStr = ""*/;

                if (reportType == EntityEnum.ReportTypeEnum.ByMonth)
                {
                    datePartInSelect  = " DATEPART(Year, CreatedOf) Year, DATEPART(Month, CreatedOf) Month, ";
                    datePartInGroupBy = " DATEPART(Year, CreatedOf), DATEPART(Month, CreatedOf), ";
                    datePartInOrderBy = ", Year, Month ";
                    datePartCreatedOf = " ";
                }

                using (SqlCommand command = connection.GetCommand(" " +
                                                                  "SELECT " + datePartInSelect + " [Document].[ContractorId] AS DocumentContractorId, " +
                                                                  "[Contractor].Code AS ContractorCode " + datePartCreatedOf + ", SUM([Sum]) AS PurchaseSum, " +
                                                                  "SUM([SaleSum]) AS SaleSum " +
                                                                  "FROM [Document] " +
                                                                  "INNER JOIN Contractor on Document.ContractorId = Contractor.ContractorId " +
                                                                  "WHERE DocumentTypeId = " + (int)docType + " AND IsCommitted = 1 " + GetEmployeeIdWhereString(employeeId) +
                                                                  "AND CreatedOf >= '" + yearMonthFrom + "' AND CreatedOf < '" + yearMonthTo + "' " +
                                                                  "GROUP BY " + datePartInGroupBy + " [Contractor].Code " + datePartCreatedOf + ",[Document].[ContractorId] " +
                                                                  "ORDER BY [Document].[ContractorId] ASC " + datePartInOrderBy + " " + datePartCreatedOf
                                                                  , CommandType.Text))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        SalesReportModel item = null;

                        while (reader.Read())
                        {
                            DateTime createdOf = GetDate(reader, reportType);

                            decimal purchaseSum    = Convert.ToDecimal(reader["PurchaseSum"]);
                            decimal saleSum        = Convert.ToDecimal(reader["SaleSum"]);
                            int     contractorId   = Int32.Parse(reader["DocumentContractorId"].ToString());
                            string  contractorCode = reader["ContractorCode"].ToString();

                            if (periods.Where(x => x.Period == createdOf).FirstOrDefault() == null)
                            {
                                periods.Add(
                                    new SalesReportPeriodModel
                                {
                                    Period     = createdOf,
                                    ReportItem = GetReportByDate(createdOf, docType, reportType, employeeId),
                                });
                            }

                            if (item == null)
                            {
                                item = new SalesReportModel {
                                    ContractorModel = new ContractorCodeIdModel {
                                        ContractorCode = contractorCode,
                                        ContractorId   = contractorId,
                                    },
                                    Refunds  = GetTotalReport(contractorId, yearMonthFrom, yearMonthTo, EntityEnum.DocumentTypeEnum.Refunds, employeeId),
                                    SubTotal = GetTotalReport(contractorId, yearMonthFrom, yearMonthTo, EntityEnum.DocumentTypeEnum.Shipment, employeeId),
                                }
                            }
                            ;
                            if (item.ContractorModel.ContractorId != contractorId)
                            {
                                result.Add(item);
                                item = new SalesReportModel
                                {
                                    ContractorModel = new ContractorCodeIdModel
                                    {
                                        ContractorCode = contractorCode,
                                        ContractorId   = contractorId
                                    },
                                    Refunds     = GetTotalReport(contractorId, yearMonthFrom, yearMonthTo, EntityEnum.DocumentTypeEnum.Refunds, employeeId),
                                    SubTotal    = GetTotalReport(contractorId, yearMonthFrom, yearMonthTo, EntityEnum.DocumentTypeEnum.Shipment, employeeId),
                                    ReportItems = new List <SalesReportModelItem>()
                                };
                            }

                            if (item.ReportItems == null)
                            {
                                item.ReportItems = new List <SalesReportModelItem>();
                            }

                            item.ReportItems.Add(new SalesReportModelItem
                            {
                                CreatedOf   = createdOf,
                                PurchaseSum = purchaseSum,
                                SaleSum     = saleSum
                            });
                        }
                        // Check if contractor already exists

                        result.Add(item);
                    }
                }
            }

            /*
             * string yearMonthFrom = SqlHelper.GetDateString(monthFromNumber,yearFromNumber);
             * string yearMonthTo = SqlHelper.GetDateString(monthToNumber, yearToNumber);
             */
            data.RefundsGrandTotal = GetTotalReport(null, yearMonthFrom, yearMonthTo, EntityEnum.DocumentTypeEnum.Refunds, employeeId);
            data.GrandTotal        = GetTotalReport(null, yearMonthFrom, yearMonthTo, EntityEnum.DocumentTypeEnum.Shipment, employeeId);

            data.ReportModel = result;
            //List<DateTime> datesList = dates.ToList();
            //datesList.Sort();
            data.Periods = periods.OrderBy(x => x.Period).ToList();
            return(data);
        }
    }