private bool SalesDataObjectIsValid(SalesStatistics salesStat)
{
bool result = false;
if (!string.IsNullOrEmpty(salesStat.Product.Name) && !string.IsNullOrEmpty(salesStat.Product.SKU))
{
result = true;
}
return(result);
}
private int GetDailySaleForMonth(DateTime currentDate, List <SalesStatistics> futureSalesForProduct)
{
int dailySale = 0;
int daysInMonth = DateTime.DaysInMonth(currentDate.Year, currentDate.Month);
if (futureSalesForProduct.Exists(s => s.PeriodEnd.Month.Equals(currentDate.Month)))
{
SalesStatistics productSalesForMonth = futureSalesForProduct.Where(s => s.PeriodEnd.Month.Equals(currentDate.Month)).Single();
dailySale = productSalesForMonth.ExpectedSales / daysInMonth;
}
else
{
dailySale = futureSalesForProduct.First().ExpectedSales / daysInMonth; //bør nok laves om
}
return(dailySale);
}
public StatisticsTestData(
SalesStatistics repositoryContent,
string from,
string to,
DateTime?expectedFrom,
DateTime?expectedTo,
HttpStatusCode responseStatusCode,
string response)
{
RepositoryContent = repositoryContent;
From = from;
To = to;
ExpectedFrom = expectedFrom;
ExpectedTo = expectedTo;
ResponseStatusCode = responseStatusCode;
Response = response;
}
public void CalculateBrandSale()
{
BrandSaleList.Clear();
foreach (var item in Brands)
{
for (int i = 0; i < 12; i++)
{
SalesStatistics s = new SalesStatistics
{
Product = new Product(),
PeriodStart = new DateTime(2017, i + 1, 1)
};
s.Product.Brand = item;
s.QuantitySold = ProductStatistics.Where(x => x.Product.Brand != null && x.Product.Brand.Name == item.Name && x.PeriodStart.Month == i + 1).Sum(x => x.QuantitySold);
BrandSaleList.Add(s);
}
}
}
public List <SalesStatistics> ReadProductsSalesInfoFromCSV(string pFilePath)
{
List <SalesStatistics> productSalesStaticstic = new List <SalesStatistics>();
DateTime periodstart = FindStartDateInFileName(pFilePath);
DateTime periodEnd = periodstart.AddDays(DateTime.DaysInMonth(periodstart.Year, periodstart.Month) - 1);
using (StreamReader reader = new StreamReader(pFilePath, _readerEncoding))
{
reader.ReadLine(); // Skipping the first line.
while (reader.EndOfStream == false)
{
string[] currentTuple = reader.ReadLine().Split(',');
SalesStatistics prodStat = new SalesStatistics
{
PeriodStart = periodstart,
PeriodEnd = periodEnd
};
bool quantityFound = false;
int counter = 1;
while (quantityFound == false || counter < 3)
{
if (IsNumber(currentTuple[counter])) // den solgte mængde vil i alle tilfælde være den første værdi i en række, som kun er et tal. Derfor prøver vi tryParse på alle værdier i tuplen.
{
prodStat.QuantitySold = Convert.ToInt32(currentTuple[counter]);
quantityFound = true;
}
counter++;
}
prodStat.Product = new Product // Ændres til at tjekke database efter matchende produkt.
{
Name = currentTuple.First(),
SKU = currentTuple.Last()
};
if (SalesDataObjectIsValid(prodStat))
{
productSalesStaticstic.Add(prodStat);
}
}
}
return(productSalesStaticstic);
}
public void Init()
{
p1.LeadTimeDays = 14;
p2.LeadTimeDays = 30;
p3.LeadTimeDays = 7;
dataStorage = TestDB.Instance;
for (int i = 0; i < p1stat.Length; i++)
{
int DaysInMonth = DateTime.DaysInMonth(2017, i + 1);
p1stat[i] = new SalesStatistics()
{
PeriodStart = new DateTime(2017, i + 1, 1),
PeriodEnd = new DateTime(2017, i + 1, DaysInMonth - 1),
QuantitySold = 50,
Product = p1
};
p2stat[i] = new SalesStatistics()
{
PeriodStart = new DateTime(2017, i + 1, 1),
PeriodEnd = new DateTime(2017, i + 1, DaysInMonth - 1),
QuantitySold = 20,
Product = p2
};
p3stat[i] = new SalesStatistics()
{
PeriodStart = new DateTime(2017, i + 1, 1),
PeriodEnd = new DateTime(2017, i + 1, DaysInMonth - 1),
QuantitySold = 30,
Product = p3
};
}
// Gemmer testdata i databasen
dataStorage.InsertProductSale(p1stat.ToList());
dataStorage.InsertProductSale(p2stat.ToList());
dataStorage.InsertProductSale(p3stat.ToList());
dataStorage.InsertProduct(p1);
dataStorage.InsertProduct(p2);
dataStorage.InsertProduct(p3);
dataStorage.InsertProduct(p4);
}
public List <SalesStatistics> GetProductSales()
{
List <SalesStatistics> salesStatisticsList = new List <SalesStatistics>();
List <Product> allProducts = GetAllProducts();
using (SqlConnection con = DBConnection.Connect)
{
con.Open();
SqlCommand cmd1 = new SqlCommand("PRODUCT_SALES", con)
{
CommandType = System.Data.CommandType.StoredProcedure
};
SqlDataReader reader = cmd1.ExecuteReader();
while (reader.Read())
{
SalesStatistics stat = new SalesStatistics()
{
QuantitySold = (int)reader["quantity"],
PeriodEnd = (DateTime)reader["periodend"]
};
stat.PeriodStart = new DateTime(stat.PeriodEnd.Year, stat.PeriodEnd.Month, 1);
if (allProducts.Exists(p => p.SKU == (string)reader["sku"]))
{
stat.Product = allProducts.Where((p => p.SKU == (string)reader["sku"])).Single();
}
else
{
stat.Product = new Product()
{
SKU = (string)reader["sku"],
Name = "Ukendt"
};
}
salesStatisticsList.Add(stat);
}
}
return(salesStatisticsList);
}
/// <summary>
/// Table[0]: Result,
/// Table[1]: Statistics
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static SalesStatisticsReport SalesStatisticsReportQuery(SalesStatisticsReportQueryFilter filter)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("FinancialReportSalesStatisticsReportQuery");
DataSet result = null;
int totalCount = 0;
SalesStatisticsReport adjustInfo = new SalesStatisticsReport();
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, filter, string.IsNullOrEmpty(filter.SortFields) ? "B.ProductID DESC, A.PayTypeSysNo" : filter.SortFields))
{
#region Set dynamic codition for where
if (filter.SOStatusList != null && filter.SOStatusList.Count > 0)
{
sqlBuilder.ConditionConstructor.AddInCondition <int>(
QueryConditionRelationType.AND,
"SOStatus",
System.Data.DbType.Int32,
filter.SOStatusList);
}
if (filter.SODateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rpt.OrderDate",
DbType.DateTime,
"@OrderDateFrom_query",
QueryConditionOperatorType.MoreThanOrEqual,
filter.SODateFrom.Value);
}
if (filter.SODateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rpt.OrderDate",
DbType.DateTime,
"@SODateTo_query",
QueryConditionOperatorType.LessThan,
filter.SODateTo.Value);
}
if (filter.C1SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rpt.C1SysNo",
DbType.Int32,
"@C1SysNo_query",
QueryConditionOperatorType.Equal,
filter.C1SysNo);
}
if (filter.C2SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rpt.C2SysNo",
DbType.Int32,
"@C2SysNo_query",
QueryConditionOperatorType.Equal,
filter.C2SysNo);
}
if (filter.C3SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rpt.C3SysNo",
DbType.Int32,
"@C3SysNo_query",
QueryConditionOperatorType.Equal,
filter.C3SysNo);
}
if (!string.IsNullOrWhiteSpace(filter.ProductID))
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rpt.ProductID",
DbType.StringFixedLength,
"@ProductID_query",
QueryConditionOperatorType.Equal,
filter.ProductID);
}
if (!string.IsNullOrWhiteSpace(filter.BrandName))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
string.Format(" (brand.BrandName_Ch = N'{0}' OR brand.BrandName_En = N'{0}') ", filter.BrandName.Replace("'", "''")));
}
sqlBuilder.ConditionConstructor.AddInCondition(
QueryConditionRelationType.AND,
"rpt.VendorSysNo",
DbType.Int32,
filter.VendorSysNoList);
sqlBuilder.ConditionConstructor.AddInCondition(
QueryConditionRelationType.AND,
"rpt.WarehouseNumber",
DbType.StringFixedLength,
filter.WarehouseNumberList);
#endregion
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
result = dataCommand.ExecuteDataSet();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
}
adjustInfo.SalesStatisticsResult.PageInfo = new PageInfo()
{
PageIndex = filter.PageIndex,
PageSize = filter.PageSize,
TotalCount = totalCount,
SortBy = filter.SortFields
};
adjustInfo.SalesStatisticsResult.ResultList = new List <SalesStatistics>();
if (null != result && result.Tables.Count > 0)
{
if (result.Tables.Count >= 1)
{
DataTable mainInfoDt = result.Tables[0];
if (mainInfoDt.Rows.Count > 0)
{
foreach (DataRow dr in mainInfoDt.Rows)
{
SalesStatistics aa = DataMapper.GetEntity <SalesStatistics>(dr);
adjustInfo.SalesStatisticsResult.ResultList.Add(aa);
}
}
}
if (result.Tables.Count >= 2)
{
DataTable itemsInfoDt = result.Tables[1];
if (itemsInfoDt.Rows.Count > 0)
{
foreach (DataRow dr in itemsInfoDt.Rows)
{
adjustInfo.CostReportStatisticList.Add(DataMapper.GetEntity <IncomeCostReportStatistic>(dr));
}
}
}
}
return(adjustInfo);
}
