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