/// <summary>
/// Получает данные по занятости авто между указанными месяцами (включительно)
/// </summary>
/// <param name="first">Месяц начала</param>
/// <param name="last">Месяц окончания</param>
/// <returns></returns>
public static MonthBusiness[] GetDataset(Month first, Month last)
{
MonthBusiness[] monthBuisnesses = null;
if (App.SearchSettings.TestData)
{
var rMonth = Randomizer.GetRandomMonth(2018);
List <MonthBusiness> businesses = new List <MonthBusiness>();
foreach (var month in Month.GetMonthes(first, last))
{
businesses.Add(Randomizer.GetRandomBusiness(month));
}
//return new MonthBusiness[] { Randomizer.GetRandomBusiness(first, last) };
return(businesses.ToArray());
}
else
{
monthBuisnesses = new MonthBusiness[] { DB_Manager.Default.GetBusinessByMonthes(first, last) };
}
return(monthBuisnesses);
}
public static MonthBusiness GetRandomBusiness(Month month)
{
var mb = new MonthBusiness(Generate(month));
mb.Month = month;
return(mb);
}
MonthBusiness GetRandomBusiness()
{
var mb = new MonthBusiness(Generate());
mb.Month = m_month;
// System.Windows.Media.Brushes.LightGray
return(mb);
}
public void Add(MonthBusiness mb)
{
if (Results == null)
{
Results = new ObservableCollection <MonthBusiness>();
}
Results.Add(mb);
}
public static MonthBusiness GetRandomBusiness(Month start, Month end)
{
var monthes = Month.GetMonthes(start, end);
var mb = new MonthBusiness(Generate(start, end));
mb.Monthes = monthes;
return(mb);
}
public MonthBusiness GetBusinessByMonthes(Month start, Month end, SearchSettings settings = null, Region region = null)
{
if (settings == null)
{
settings = App.SearchSettings;
}
var carBusinesses = new List <CarBusiness>();
var sql = string.Empty;
if (settings.SelectedDBSearchType == DBSearchType.All)
{
var sb = new StringBuilder(1000);
sb.Append("SELECT DISTINCT ").Append("(");
//настройки с поиском старых заказов
var oldSettings = new SearchSettings(settings);
oldSettings.SelectedDBSearchType = DBSearchType.Old;
sb.Append(GetBusinessByMonthesQuery(start, end, oldSettings, region));
//sb.Append(")");
sb.Append("\r\n UNION \r\n");//.Append("("); //объединяем два запроса
//настройки с поиском актуальных заказов
var curentSettings = new SearchSettings(settings);
curentSettings.SelectedDBSearchType = DBSearchType.Curent;
sb.Append(GetBusinessByMonthesQuery(start, end, curentSettings, region));
sb.Append(")");//.Append(" ORDER BY [Document No_]");
sql = sb.ToString();
}
else
{
sql = GetBusinessByMonthesQuery(start, end, settings, region);
}
try
{
using (var con = new SqlConnection(m_connectionString))
{
var com = new SqlCommand(sql);
com.Connection = con;
con.Open();
using (var reader = com.ExecuteReader())
{
var curentCar = string.Empty;
var previosCar = string.Empty;
var buyer = string.Empty;
CarBusiness cb = null;
while (reader.Read())
{
curentCar = (string)reader["CarName"];
var no_ = (string)reader["No_"];
cb = carBusinesses.FirstOrDefault(_cb => _cb.ItemNo.Equals(no_));
if (cb == null)
{
previosCar = curentCar;
cb = new CarBusiness();
cb.Monthes = Month.GetMonthes(new DateTime(start.Year, start.Index, 1), new DateTime(end.Year, end.Index, 1));
cb.ItemNo = (string)reader["No_"];
carBusinesses.Add(cb);
var number = ((string)reader["CarNumber"]).Trim();
var lastCharIndex = number.Last(c => char.IsLetter(c)) + 1;
if ((lastCharIndex) < number.Length)
{
number = number.Substring(lastCharIndex, number.Length - lastCharIndex);
}
cb.Name = $"{curentCar} ({number})";
}
buyer = (string)reader["Buyer"];
if (cb.Count > 0 && cb.Business.Last().Title.Equals(buyer))
{
cb.Business.Last().DateEnd = ((DateTime)reader["DateEnd"]);
}
else
{
var b = new Leasing();
b.DateStart = ((DateTime)reader["DateStart"]);
b.DateEnd = ((DateTime)reader["DateEnd"]);
b.Title = buyer;
b.Type = BusinessType.Leasing;
b.Comment = (string)reader["Comment"];
b.Monthes = Month.GetMonthes(b.DateStart, b.DateEnd);
b.Saler = (string)reader["Saler"];
b.Blocked = (bool)reader["Blocked"];
cb.Add(b);
}
}
}
}
}
catch (SqlException sqlEx)
{
m_loger.Log("Возникло исключение при запросе выборки из БД", sqlEx,
new LogParameter("Запрос", sql));
}
catch (Exception ex)
{
m_loger.Log("Возникло исключение при запросе выборки из БД", ex);
}
var monthBusiness = new MonthBusiness(carBusinesses);
monthBusiness.Monthes = Month.GetMonthes(new DateTime(start.Year, start.Index, 1), new DateTime(end.Year, end.Index, 1));
return(monthBusiness);
}
public MonthBusiness GetBusinessByMonth(Month month, SearchSettings settings = null, Region region = null)
{
if (settings == null)
{
settings = App.SearchSettings;
}
var carBusinesses = new List <CarBusiness>();
#region Old
/*
* var sql = $@"SELECT DISTINCT
* l.[Document No_]
* ,h.[Salesperson Code]
* ,h.[Bal_ Account No_]
* ,h.[Sell-to Customer Name] as Buyer
* ,h.[Bill-to Name]
* ,h.[Ship-to Name]
* ,l.[Description] as CarName
* ,l.[Vehicle Reg_ No_] as CarNumber
* ,h.[Venicle Operation Area]
* ,h.[Date Begin] as DateStart
* ,h.[Time Begin]
* ,h.[Date End] as DateEnd
* ,h.[Time End]
* ,h.[Comment Text] as Comment
* FROM [CARLSON_Test_10052018].[dbo].[Carlson$Sales{(invoice ? " Invoice" : "")} Line] l
* INNER JOIN [CARLSON_Test_10052018].[dbo].[Carlson$Sales{(invoice ? " Invoice" : "")} Header] h ON h.[Sell-to Customer No_] = l.[Sell-to Customer No_]
* WHERE l.[Vehicle Reg_ No_] != ''
* AND h.[Date End] > h.[Date Begin]
* ORDER BY l.[Document No_], h.[Date Begin]";
*/
#endregion
var sql = string.Empty;
if (settings.SelectedDBSearchType == DBSearchType.All)
{
var sb = new StringBuilder(1000);
sb.Append("SELECT DISTINCT (");
//настройки с поиском старых заказов
var oldSettings = new SearchSettings(settings);
oldSettings.SelectedDBSearchType = DBSearchType.Old;
sb.Append(GetBusinessByMonthQuery(month, oldSettings, region));
sb.Append("\r\n UNION \r\n"); //объединяем два запроса
//настройки с поиском актуальных заказов
var curentSettings = new SearchSettings(settings);
curentSettings.SelectedDBSearchType = DBSearchType.Curent;
sb.Append(GetBusinessByMonthQuery(month, curentSettings, region));
sb.Append(")");
sql = sb.ToString();
}
else
{
sql = GetBusinessByMonthQuery(month, settings, region);
}
//sql = $@"SELECT
// i.[No_]
// , i.[Description] as CarName
// , i.[Vehicle Reg_ No_] as CarNumber
// , i.[Blocked]
// , l.[Document No_]
// ,h.[Salesperson Code]
// ,h.[Bal_ Account No_]
// ,h.[Sell-to Customer Name] as Buyer
// ,h.[Bill-to Name]
// ,h.[Ship-to Name]
// ,h.[Venicle Operation Area]
// ,h.[Date Begin] as DateStart
// ,h.[Time Begin]
// ,h.[Date End] as DateEnd
// ,h.[Time End]
// ,h.[Comment Text] as Comment
// FROM Carlson$Item i
// LEFT JOIN [Carlson$Sales {(settings.SelectedDBSearchType == DBSearchType.Curent ? string.Empty : invoice)}Line] l ON l.No_ = i.No_
// LEFT JOIN [Carlson$Sales {(settings.SelectedDBSearchType == DBSearchType.Curent ? string.Empty : invoice)}Header] h ON h.No_ = l.[Document No_]
// WHERE 1 = 1
// {(settings.IncludeBlocked ? string.Empty : "AND i.Blocked = 0")}
// AND i.IsService = 0
// AND i.IsFranchise = 0
// AND h.[Date Begin] IS NOT NULL
// {(region == null || string.IsNullOrWhiteSpace(region.DBKey) ? "" : "AND i.[Responsibility Center] = '" + region.DBKey + "'")}
// AND ((h.[Date Begin] BETWEEN '{month.GetSqlDate(1)}' AND '{month.Next().GetSqlDate(1)}') OR (h.[Date End] BETWEEN '{month.GetSqlDate(1)}' AND '{month.Next().GetSqlDate(1)}'))
// ORDER BY l.[Document No_]";
try
{
using (var con = new SqlConnection(m_connectionString))
{
var com = new SqlCommand(sql);
com.Connection = con;
con.Open();
using (var reader = com.ExecuteReader())
{
var curentCar = string.Empty;
var previosCar = string.Empty;
var buyer = string.Empty;
CarBusiness cb = null;
while (reader.Read())
{
curentCar = (string)reader["CarName"];
var no_ = (string)reader["No_"];
cb = carBusinesses.FirstOrDefault(_cb => _cb.ItemNo.Equals(no_));
if (cb == null)
{
previosCar = curentCar;
cb = new CarBusiness();
cb.Month = month;
cb.ItemNo = (string)reader["No_"];
carBusinesses.Add(cb);
var number = ((string)reader["CarNumber"]).Trim();
var lastCharIndex = number.Last(c => char.IsLetter(c)) + 1;
if ((lastCharIndex) < number.Length)
{
number = number.Substring(lastCharIndex, number.Length - lastCharIndex);
}
cb.Name = $"{curentCar} ({number})";
}
buyer = (string)reader["Buyer"];
if (cb.Count > 0 && cb.Business.Last().Title.Equals(buyer))
{
cb.Business.Last().DateEnd = ((DateTime)reader["DateEnd"]);
}
else
{
var b = new Leasing();
b.DateStart = ((DateTime)reader["DateStart"]);
b.DateEnd = ((DateTime)reader["DateEnd"]);
b.Title = buyer;
b.Type = BusinessType.Leasing;
b.Comment = (string)reader["Comment"];
b.CurrentMonth = month;
cb.Add(b);
}
}
}
}
}
catch (Exception ex)
{
m_loger.Log("Возникло исключение при запросе выборки из БД", ex);
}
var monthBusiness = new MonthBusiness(carBusinesses);
monthBusiness.Month = month;
return(monthBusiness);
}
