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); }
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); }