private List <SalesReportContractorModel> GetSalesSumReportByContractorList(int yearFromNumber, int yearToNumber,
int monthFromNumber, int monthToNumber,
SidBy.Sklad.Domain.Enums.EntityEnum.DocumentTypeEnum type, int managerId)
{
return(GetSalesReportByContractorList(yearFromNumber, yearToNumber,
monthFromNumber, monthToNumber, type, false, managerId));
}
private decimal GetGrandTotalSum(int yearFromNumber, int yearToNumber,
int monthFromNumber, int monthToNumber,
SidBy.Sklad.Domain.Enums.EntityEnum.DocumentTypeEnum type, int managerId)
{
return(GetGrandTotal <decimal>(yearFromNumber, yearToNumber,
monthFromNumber, monthToNumber,
type, false, managerId));
}
private int GetGrandTotalQuantity(int yearFromNumber, int yearToNumber,
int monthFromNumber, int monthToNumber,
SidBy.Sklad.Domain.Enums.EntityEnum.DocumentTypeEnum type, int managerId)
{
return(GetGrandTotal <int>(yearFromNumber, yearToNumber,
monthFromNumber, monthToNumber,
type, true, managerId));
}
private List <SalesReportContractorModel> GetSalesReportByContractorList(int yearFromNumber, int yearToNumber,
int monthFromNumber, int monthToNumber,
SidBy.Sklad.Domain.Enums.EntityEnum.DocumentTypeEnum type, bool getQuantity, int managerId)
{
var result = new List <SalesReportContractorModel>();
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, true);
using (SqlConnection connection = SqlHelper.GetConnection(ConnectionString))
{
string sumString = (getQuantity) ? "[Quantity]" : "[SaleSum]";
string responsibleStr0 = String.Empty;
if (managerId >= 1)
{
responsibleStr0 = " AND Contractor.ResponsibleId = " + managerId + " ";
}
using (SqlCommand command = connection.GetCommand(" " +
"SELECT SupplierId, refunds.SupplierCode AS SupplierCode,SupplierRegion, " +
"RefundsSaleSum AS SaleSum,refunds.ContractorId AS ClientId, Contractor.Code AS ClientCode , Contractor.Region AS ClientRegion " +
"FROM ( " +
"SELECT " +
"ProductLine.[SupplierId], Contractor.Code AS SupplierCode, Contractor.Region AS SupplierRegion " +
" ,SUM(ProductLine." + sumString + ") AS RefundsSaleSum, Document.ContractorId AS ContractorId " +
"FROM [ProductLine] " +
" RIGHT JOIN Contractor ON [SupplierId] = Contractor.ContractorId " + responsibleStr0 +
"INNER JOIN " +
"Document ON ProductLine.DocumentId = Document.DocumentId " +
"WHERE Document.IsCommitted = 1 AND Document.CreatedOf >= '" + yearMonthFrom + "' AND Document.CreatedOf <='" + yearMonthTo + "' " +
"AND Document.DocumentTypeId = " + (int)type +
" GROUP BY [SupplierId],Contractor.Code ,Document.ContractorId , Contractor.Region" +
") refunds " +
"INNER JOIN Contractor On refunds.ContractorId = Contractor.ContractorId "
, CommandType.Text))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var item = new SalesReportContractorModel
{
Client = new ContractorGridSimpleModel
{
Code = reader["ClientCode"].ToString(),
ContractorId = Int32.Parse(reader["ClientId"].ToString()),
Region = reader["ClientRegion"].ToString()
},
Factory = new ContractorGridSimpleModel
{
Code = reader["SupplierCode"].ToString(),
ContractorId = Int32.Parse(reader["SupplierId"].ToString()),
Region = reader["SupplierRegion"].ToString()
},
};
if (type == EntityEnum.DocumentTypeEnum.Refunds)
{
if (getQuantity)
{
item.QuantityRefunds = Int32.Parse(reader["SaleSum"].ToString());
}
else
{
item.Refunds = decimal.Parse(reader["SaleSum"].ToString());
}
}
else if (type == EntityEnum.DocumentTypeEnum.Shipment)
{
if (getQuantity)
{
item.QuantityShipment = Int32.Parse(reader["SaleSum"].ToString());
}
else
{
item.Shipment = decimal.Parse(reader["SaleSum"].ToString());
}
}
result.Add(item);
}
}
}
}
return(result);
}
private T GetGrandTotal <T>(int yearFromNumber, int yearToNumber,
int monthFromNumber, int monthToNumber,
SidBy.Sklad.Domain.Enums.EntityEnum.DocumentTypeEnum type, bool getQuantity, int managerId)
{
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, true);
var thisType = default(T);
var typeCode = thisType.GetType();
TypeConverter converter = TypeDescriptor.GetConverter(typeof(T));
string responsibleFilter = String.Empty;
if (managerId >= 1)
{
responsibleFilter = " AND Contractor.ResponsibleId=" + managerId + " ";
}
using (SqlConnection connection = SqlHelper.GetConnection(ConnectionString))
{
string sumString = (getQuantity) ? "ProductLine.Quantity" : "ProductLine.SaleSum";
using (SqlCommand command = connection.GetCommand("" +
"SELECT SUM(" + sumString + ") AS SaleGrandTotal " +
"FROM ProductLine " + " RIGHT JOIN Contractor ON [SupplierId] = Contractor.ContractorId " + responsibleFilter +
" INNER JOIN " +
" Document ON ProductLine.DocumentId = Document.DocumentId " +
" WHERE Document.IsCommitted = 1 AND Document.CreatedOf >= '" + yearMonthFrom + "' AND Document.CreatedOf <='" + yearMonthTo + "' " +
" AND Document.DocumentTypeId =" + (int)type, CommandType.Text))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader["SaleGrandTotal"] != null)
{
if (getQuantity)
{
int returnValueInt;
Int32.TryParse(reader["SaleGrandTotal"].ToString(), out returnValueInt);
return((T)Convert.ChangeType(returnValueInt, typeCode));
}
else
{
decimal returnValueDecimal;
Decimal.TryParse(reader["SaleGrandTotal"].ToString(), out returnValueDecimal);
return((T)Convert.ChangeType(returnValueDecimal, typeCode));
//return (T)converter.ConvertFrom(returnValueDecimal);
}
}
}
}
}
}
return((T)converter.ConvertFrom(0));
}
