public async Task <IHttpActionResult> GetDocSalaryTab(int id, HttpRequestMessage request)
{
#region Проверяем Логин и Пароль + Изменяем строку соединения + Права + Разные Функции
//Получаем Куку
System.Web.HttpCookie authCookie = System.Web.HttpContext.Current.Request.Cookies["CookieIPOL"];
// Проверяем Логин и Пароль
Classes.Account.Login.Field field = await Task.Run(() => login.Return(authCookie, true));
if (!field.Access)
{
return(Ok(returnServer.Return(false, Classes.Language.Sklad.Language.msg10)));
}
//Изменяем строку соединения
db = new DbConnectionSklad(connectionString.Return(field.DirCustomersID, null, true));
//Права (1 - Write, 2 - Read, 3 - No Access)
int iRight = await Task.Run(() => accessRight.Access(connectionString.Return(field.DirCustomersID, null, true), field.DirEmployeeID, "RightDocSalaries"));
if (iRight == 3)
{
return(Ok(returnServer.Return(false, Classes.Language.Sklad.Language.msg57(0))));
}
//Разные Функции
function.NumberDecimalSeparator();
//Получам настройки
Models.Sklad.Sys.SysSetting sysSetting = await db.SysSettings.FindAsync(1);
#endregion
#region Параметры
Params _params = new Params();
//paramList -список параметров
var paramList = request.GetQueryNameValuePairs();
//Параметры
_params.DocYear = Convert.ToInt32(paramList.FirstOrDefault(kv => string.Compare(kv.Key, "DocYear", true) == 0).Value);
_params.DocMonth = Convert.ToInt32(paramList.FirstOrDefault(kv => string.Compare(kv.Key, "DocMonth", true) == 0).Value);
DateTime DocDateS = Convert.ToDateTime(_params.DocYear.ToString() + "-" + _params.DocMonth + "-01");
DateTime DocDatePo = DocDateS.AddMonths(1).AddDays(-1);
#endregion
#region Для каждого сутрудника подсчитываем ЗП, Премия1 и Премия2
//Алгортм:
//Получаем каждого сотрудника и параметры ЗП: ЗП + Тип, Премия1 и Премия2
//1. ЗП вычисляем по типу. Пробегаем по каждому дню, если есть хоть одна продажа/ремонт, то засчитываем ему этот день
//1.1. Для продаж смотрим таблицу "RemPartyMinuses"
//Блядь!!!
//1.
//В таблицу "RemPartyMinuses" добавить поле "DirEmployeeID" и перенести всех сотрудников, в зависимости от типа документа:
// - Если СС, то смотрим "DirEmployeeID" в таблице "DocServicePurch2Tabs"
// - Во всех остальных случаях смотрим талицу "Doc"
//А также во всех формах где задействована таблица "RemPartyMinuses" переносить "DirEmployeeID"
//2.
//Аналогично сделать для таблиц "RemParty"
var queryDirEmployees = await
(
from x in db.DirEmployees
select x
).ToListAsync();
Models.Sklad.Doc.DocSalaryTabSQL[] arrDocSalaryTabSQL = new Models.Sklad.Doc.DocSalaryTabSQL[queryDirEmployees.Count()];
if (queryDirEmployees.Count() > 0)
{
for (int i = 0; i < queryDirEmployees.Count(); i++)
{
int CountDay = 0;
double SumSalary = 0, DirBonusIDSalary = 0, DirBonus2IDSalary = 0, Sums = 0;
int?DirEmployeeID = queryDirEmployees[i].DirEmployeeID,
DirBonusID = queryDirEmployees[i].DirBonusID,
DirBonus2ID = queryDirEmployees[i].DirBonus2ID;
#region 1. SumSalary: К-во выходов в месяц
if (queryDirEmployees[i].SalaryDayMonthly == 1)
{
string SQL =
"SELECT date(DocDate) AS DateX, COUNT(date(DocDate)) AS CountX " +
"FROM RemPartyMinuses " +
"WHERE DirEmployeeID=@DirEmployeeID and (DocDate BETWEEN @DocDateS and @DocDatePo) " +
"GROUP BY date(RemPartyMinuses.DocDate);";
SQLiteParameter parDirEmployeeID = new SQLiteParameter("@DirEmployeeID", System.Data.DbType.Int32)
{
Value = DirEmployeeID
};
SQLiteParameter parDocDateS = new SQLiteParameter("@DocDateS", System.Data.DbType.Date)
{
Value = DocDateS.ToString("yyyy-MM-dd")
};
SQLiteParameter parDocDatePo = new SQLiteParameter("@DocDatePo", System.Data.DbType.Date)
{
Value = DocDatePo.ToString("yyyy-MM-dd")
};
//Сам запрос с параметрами
var query = db.Database.SqlQuery <Models.Sklad.Rem.RemPartyMinusSQL>(SQL, parDirEmployeeID, parDocDateS, parDocDatePo);
if (query.Count() > 0)
{
CountDay = query.Count();
SumSalary = query.Count() * Convert.ToDouble(queryDirEmployees[i].Salary);
}
}
else
{
SumSalary = Convert.ToDouble(queryDirEmployees[i].Salary);
}
#endregion
#region 2. DirBonusIDSalary: Премия с продаж
//Алгоритм:
//Получаем [Сумму продаж]
//По градации смотрим в какой диапазон попадает и берём процент
//[Сумма продаж] * [Процент]
if (DirBonusID > 0)
{
//1. Получаем сумму
double querySum =
(
from x in db.RemPartyMinuses
where x.DirEmployeeID == DirEmployeeID && (x.DocDate >= DocDateS && x.DocDate <= DocDatePo)
select x.Quantity * x.PriceCurrency
).DefaultIfEmpty(0).Sum();
//2. Получаем градацию и в цикле пробегаем её
var queryBonus =
(
from x in db.DirBonusTabs
where x.DirBonusID == DirBonusID
select new
{
SumBegin = x.SumBegin,
Bonus = x.Bonus
}
).OrderByDescending(o => o.SumBegin).ToList();
for (int j = 0; j < queryBonus.Count(); j++)
{
if (queryBonus[j].SumBegin <= querySum)
{
DirBonusIDSalary = (querySum / 100) * queryBonus[j].Bonus;
break;
}
}
}
#endregion
#region 3. DirBonus2IDSalary: Премия с продаж
//Алгоритм:
//Получаем [Сумму продаж]
//По градации смотрим в какой диапазон попадает и берём процент
//[Сумма продаж] * [Процент]
if (DirBonus2ID > 0)
{
//1. Получаем сумму
double querySum =
(
from x in db.DocServicePurch1Tabs
where x.DirEmployeeID == DirEmployeeID && (x.TabDate >= DocDateS && x.TabDate <= DocDatePo)
select x.PriceCurrency
).DefaultIfEmpty(0).Sum();
//2. Получаем градацию и в цикле пробегаем её
var queryBonus =
(
from x in db.DirBonusTabs
where x.DirBonusID == DirBonus2ID
select new
{
SumBegin = x.SumBegin,
Bonus = x.Bonus
}
).OrderByDescending(o => o.SumBegin).ToList();
for (int j = 0; j < queryBonus.Count(); j++)
{
if (queryBonus[j].SumBegin <= querySum)
{
DirBonus2IDSalary = (querySum / 100) * queryBonus[j].Bonus;
break;
}
}
}
#endregion
#region 4. Data
string SalaryDayMonthlyName = "За день";
if (queryDirEmployees[i].SalaryDayMonthly == 2)
{
SalaryDayMonthlyName = "За месяц";
}
string DirBonusName = ""; if (queryDirEmployees[i].dirBonus != null)
{
DirBonusName = queryDirEmployees[i].dirBonus.DirBonusName;
}
string DirBonus2Name = ""; if (queryDirEmployees[i].dirBonus2 != null)
{
DirBonus2Name = queryDirEmployees[i].dirBonus2.DirBonusName;
}
Models.Sklad.Doc.DocSalaryTabSQL docSalaryTab = new Models.Sklad.Doc.DocSalaryTabSQL();
docSalaryTab.DirEmployeeID = Convert.ToInt32(DirEmployeeID);
docSalaryTab.DirEmployeeName = queryDirEmployees[i].DirEmployeeName;
if (queryDirEmployees[i].DirCurrencyID != null)
{
docSalaryTab.DirCurrencyID = Convert.ToInt32(queryDirEmployees[i].DirCurrencyID);
docSalaryTab.DirCurrencyName = queryDirEmployees[i].dirCurrency.DirCurrencyName;
docSalaryTab.DirCurrencyRate = queryDirEmployees[i].dirCurrency.DirCurrencyRate;
docSalaryTab.DirCurrencyMultiplicity = queryDirEmployees[i].dirCurrency.DirCurrencyMultiplicity;
}
else
{
docSalaryTab.DirCurrencyID = 1;
docSalaryTab.DirCurrencyName = "???";
docSalaryTab.DirCurrencyRate = 1;
docSalaryTab.DirCurrencyMultiplicity = 1;
}
docSalaryTab.Salary = Convert.ToDouble(queryDirEmployees[i].Salary);
docSalaryTab.CountDay = CountDay;
docSalaryTab.SalaryDayMonthly = Convert.ToInt32(queryDirEmployees[i].SalaryDayMonthly);
docSalaryTab.SalaryDayMonthlyName = SalaryDayMonthlyName;
docSalaryTab.SumSalary = SumSalary;
docSalaryTab.DirBonusID = queryDirEmployees[i].DirBonusID;
docSalaryTab.DirBonusName = DirBonusName;
docSalaryTab.DirBonusIDSalary = DirBonusIDSalary;
docSalaryTab.DirBonus2ID = queryDirEmployees[i].DirBonus2ID;
docSalaryTab.DirBonus2Name = DirBonus2Name;
docSalaryTab.DirBonus2IDSalary = DirBonus2IDSalary;
docSalaryTab.Sums = SumSalary + DirBonusIDSalary + DirBonus2IDSalary;
arrDocSalaryTabSQL[i] = docSalaryTab;
#endregion
}
}
#endregion
dynamic collectionWrapper = new
{
sucess = true,
total = queryDirEmployees.Count(),
DocSalaryTab = arrDocSalaryTabSQL
};
return(await Task.Run(() => Ok(collectionWrapper)));
}
internal async Task <string> Generate(System.Web.HttpRequestBase Request, DbConnectionSklad db)
{
//Получам настройки
Models.Sklad.Sys.SysSetting sysSetting = await db.SysSettings.FindAsync(1);
#region Параметры
pID = Request.Params["pID"];
pLanguage = Convert.ToInt32(Request.Params["pLanguage"]);
DateS = Convert.ToDateTime(Convert.ToDateTime(Request.Params["DateS"]).ToString("yyyy-MM-dd 00:00:00"));
DatePo = Convert.ToDateTime(Convert.ToDateTime(Request.Params["DatePo"]).ToString("yyyy-MM-dd 23:59:59"));
DirContractorIDOrg = 0;
bool bDirContractorIDOrg = Int32.TryParse(Request.Params["DirContractorIDOrg"], out DirContractorIDOrg);
DirContractorNameOrg = Request.Params["DirContractorNameOrg"];
DirEmployeeID = 0;
bool bDirEmployeeID = Int32.TryParse(Request.Params["DirEmployeeID"], out DirEmployeeID);
DirEmployeeName = Request.Params["DirEmployeeName"];
#endregion
//!!! Не менял код !!! Всё как в контролере "ReportSalariesController"
#region Для каждого сутрудника подсчитываем ЗП, Премия1 и Премия2
//Алгортм:
//Получаем каждого сотрудника и параметры ЗП: ЗП + Тип, Премия1 и Премия2
//1. ЗП вычисляем по типу. Пробегаем по каждому дню, если есть хоть одна продажа/ремонт, то засчитываем ему этот день
//1.1. Для продаж смотрим таблицу "RemPartyMinuses"
//Блядь!!!
//1.
//В таблицу "RemPartyMinuses" добавить поле "DirEmployeeID" и перенести всех сотрудников, в зависимости от типа документа:
// - Если СС, то смотрим "DirEmployeeID" в таблице "DocServicePurch2Tabs"
// - Во всех остальных случаях смотрим талицу "Doc"
//А также во всех формах где задействована таблица "RemPartyMinuses" переносить "DirEmployeeID"
//2.
//Аналогично сделать для таблиц "RemParty"
#region Получаем сотрудников
var queryDirEmployees1 =
(
from x in db.DirEmployees
//where x.DirEmployeeID == DirEmployeeID
select x
);
//Если выбран Сотрудник
if (DirEmployeeID > 0)
{
queryDirEmployees1 = queryDirEmployees1.Where(x => x.DirEmployeeID == DirEmployeeID);
}
var queryDirEmployees = await queryDirEmployees1.ToListAsync();
Models.Sklad.Doc.DocSalaryTabSQL[] arrDocSalaryTabSQL = new Models.Sklad.Doc.DocSalaryTabSQL[queryDirEmployees.Count()];
#endregion
if (queryDirEmployees.Count() > 0)
{
for (int i = 0; i < queryDirEmployees.Count(); i++)
{
int CountDay = 0;
double
SumSalary = 0,
DirBonusIDSalary = 0,
DirBonus2IDSalary = 0,
SalaryFixedSalesMount = 0,
SumSalaryFixedServiceOne = 0,
//Б/У
DirBonus4IDSalary = 0,
DirBonus3IDSalary = 0,
SumSalaryFixedSecondHandRetailOne = 0,
SumSalaryFixedSecondHandWorkshopOne = 0;
int?
DirEmployeeID = queryDirEmployees[i].DirEmployeeID,
DirBonusID = queryDirEmployees[i].DirBonusID,
DirBonus2ID = queryDirEmployees[i].DirBonus2ID,
//Б/У
DirBonus4ID = queryDirEmployees[i].DirBonus4ID,
DirBonus3ID = queryDirEmployees[i].DirBonus3ID;
#region 1. SumSalary: К-во выходов в месяц
#region Зарплата за 1 месяц: если установили даты больше или меньше месяца (Salary, SalaryFixedSalesMount)
//Проблема в том что:
//В каждом месяце ЗП за 1 день будет разная:
//1. 28 дней в месяце
//2. 30 дней в месяце
//3. 31 дней в месяце
double
iDayDiv_SumSalary = 0, //Convert.ToDouble(queryDirEmployees[i].Salary),
iDayDiv_SalaryFixedSalesMount = 0; //Convert.ToDouble(queryDirEmployees[i].SalaryFixedSalesMount);
//За 1 месяц 1000 рублей
//Период: [2016-11-20] - [2017-03-10]
//1. [2016-11-20] - [2016-11-30] //11 дней // 367 руб
//2. [2016-12-01] - [2016-12-31] //31 дней // 1000 руб
//3. [2017-01-01] - [2016-01-31] //31 дней // 1000 руб
//4. [2017-02-01] - [2016-02-28] //28 дней // 1000 руб
//5. [2017-03-01] - [2016-03-10] //10 дней // 323 руб
//Будем менять (ниже)
DateTime DateS2 = DateS;
//Сколько дней в периоде
TimeSpan ts = DatePo - DateS;
//for (int d = 0; d < ts.Days + 1; d++)
int d = 0;
while (true)
{
//Что бы выйти при d = 1000;
d++;
//Получили к-во дней в месяце
int iDay1 = System.DateTime.DaysInMonth(DateS2.Year, DateS2.Month);
//Если начинается День НЕ с "1" (30 - 20 + 1 = 11)
int iDay2 = 0;
if (DateS2.Year != DatePo.Year || DateS2.Month != DatePo.Month)
{
iDay2 = iDay1 - DateS2.Day + 1;
}
else
{
iDay2 = DatePo.Day - DateS2.Day + 1;
}
//Переопределяем DateS2
DateS2 = DateS2.AddDays(iDay2);
//Высчитываем сумму ЗП по каждому месяцу
iDayDiv_SumSalary += (Convert.ToDouble(queryDirEmployees[i].Salary) / iDay1) * iDay2;
iDayDiv_SalaryFixedSalesMount += (Convert.ToDouble(queryDirEmployees[i].SalaryFixedSalesMount) / iDay1) * iDay2;
//Выход
if (DateS2 >= DatePo || d == 1000)
{
break;
}
}
#endregion
if (queryDirEmployees[i].SalaryDayMonthly == 1)
{
#region Продажи + Ремонты
//Продажи: RemPartyMinuses
//Продажи: DocServicePurch1Tabs
string SQL =
"SELECT DateX, SUM(CountX) AS CountX " +
"FROM " +
" ( " +
" SELECT date(DocDate) AS DateX, COUNT(date(DocDate)) AS CountX " +
" FROM RemPartyMinuses " +
" WHERE DirEmployeeID=@DirEmployeeID and (DocDate BETWEEN @DateS and @DatePo) " +
" GROUP BY date(RemPartyMinuses.DocDate) " +
" UNION " +
" SELECT date(TabDate) AS DateX, COUNT(date(TabDate)) AS CountX " +
" FROM DocServicePurch1Tabs " +
" WHERE DirEmployeeID=@DirEmployeeID and (TabDate BETWEEN @DateS and @DatePo) " +
" GROUP BY date(DocServicePurch1Tabs.TabDate) " +
" ) AS X1 " +
"GROUP BY date(X1.DateX) ";
SQLiteParameter parDirEmployeeID = new SQLiteParameter("@DirEmployeeID", System.Data.DbType.Int32)
{
Value = DirEmployeeID
};
SQLiteParameter parDateS = new SQLiteParameter("@DateS", System.Data.DbType.Date)
{
Value = DateS.ToString("yyyy-MM-dd 00:00:00")
};
SQLiteParameter parDatePo = new SQLiteParameter("@DatePo", System.Data.DbType.Date)
{
Value = DatePo.ToString("yyyy-MM-dd 23:59:59")
};
//Сам запрос с параметрами
var query = db.Database.SqlQuery <Models.Sklad.Rem.RemPartyMinusSQL>(SQL, parDirEmployeeID, parDateS, parDatePo);
if (query.Count() > 0)
{
CountDay = query.Count();
SumSalary = query.Count() * Convert.ToDouble(queryDirEmployees[i].Salary);
}
#endregion
}
else
{
SumSalary = iDayDiv_SumSalary; // Convert.ToDouble(queryDirEmployees[i].Salary);
}
//ЗП фиксированная за месяц
if (queryDirEmployees[i].SalaryFixedSalesMount > 0)
{
SalaryFixedSalesMount = iDayDiv_SalaryFixedSalesMount; // Convert.ToDouble(queryDirEmployees[i].SalaryFixedSalesMount);
}
#endregion
// === Магазин === === ===
#region 1. DirBonusIDSalary: Премия с продаж
//Алгоритм:
//Получаем [Сумму продаж]
//По градации смотрим в какой диапазон попадает и берём процент
//[Сумма продаж] * [Процент]
if (DirBonusID > 0)
{
//1. Получаем сумму
double querySum =
(
from x in db.RemPartyMinuses
where x.DirEmployeeID == DirEmployeeID && (x.DocDate >= DateS && x.DocDate <= DatePo)
select x.Quantity * x.PriceCurrency
).DefaultIfEmpty(0).Sum();
//2. Получаем градацию и в цикле пробегаем её
var queryBonus =
(
from x in db.DirBonusTabs
where x.DirBonusID == DirBonusID
select new
{
SumBegin = x.SumBegin,
Bonus = x.Bonus
}
).OrderByDescending(o => o.SumBegin).ToList();
for (int j = 0; j < queryBonus.Count(); j++)
{
if (queryBonus[j].SumBegin <= querySum)
{
DirBonusIDSalary = (querySum / 100) * queryBonus[j].Bonus;
break;
}
}
}
#endregion
#region 2. DirBonus2IDSalary: Премия с ремонта
//Алгоритм:
//Получаем [Сумму продаж]
//По градации смотрим в какой диапазон попадает и берём процент
//[Сумма продаж] * [Процент]
if (DirBonus2ID > 0)
{
//1. Получаем сумму
double querySum =
(
from x in db.DocServicePurch1Tabs
where x.DirEmployeeID == DirEmployeeID && (x.TabDate >= DateS && x.TabDate <= DatePo)
select x.PriceCurrency
).DefaultIfEmpty(0).Sum();
//2. Получаем градацию и в цикле пробегаем её
var queryBonus =
(
from x in db.DirBonusTabs
where x.DirBonusID == DirBonus2ID
select new
{
SumBegin = x.SumBegin,
Bonus = x.Bonus
}
).OrderByDescending(o => o.SumBegin).ToList();
for (int j = 0; j < queryBonus.Count(); j++)
{
if (queryBonus[j].SumBegin <= querySum)
{
DirBonus2IDSalary = (querySum / 100) * queryBonus[j].Bonus;
break;
}
}
}
//Ремонт: фиксированной суммы с каждого ремонта, скажем 300 руб
//Алгоритм:
//Получаем к-во ремонтов с сумой > "SalaryFixedServiceOne"
double?SalaryFixedServiceOne = queryDirEmployees[i].SalaryFixedServiceOne;
if (SalaryFixedServiceOne > 0)
{
string SQL =
/*
* "SELECT COUNT(*) AS CountX " +
* "FROM " +
* "( " +
* " SELECT COUNT(*) AS CountX " +
* " FROM Docs, DocServicePurches, DocServicePurch1Tabs " +
* " WHERE " +
* " Docs.DocID = DocServicePurches.DocID and" +
* " DocServicePurches.DocServicePurchID = DocServicePurch1Tabs.DocServicePurchID and " +
* " DocServicePurch1Tabs.DirEmployeeID = @DirEmployeeID and " +
* " DocServicePurch1Tabs.TabDate BETWEEN @DateS and @DatePo " +
* " GROUP BY DocServicePurches.DocServicePurchID " +
* ")";
*/
" SELECT COUNT(*) AS CountX " +
" FROM Docs, DocServicePurches, DocServicePurch1Tabs " +
" WHERE " +
" Docs.DocID = DocServicePurches.DocID and" +
" DocServicePurches.DocServicePurchID = DocServicePurch1Tabs.DocServicePurchID and " +
" DocServicePurch1Tabs.DirEmployeeID = @DirEmployeeID and " +
" DocServicePurch1Tabs.TabDate BETWEEN @DateS and @DatePo " +
" GROUP BY DocServicePurches.DocServicePurchID ";
SQLiteParameter parDirEmployeeID = new SQLiteParameter("@DirEmployeeID", System.Data.DbType.Int32)
{
Value = DirEmployeeID
};
SQLiteParameter parDateS = new SQLiteParameter("@DateS", System.Data.DbType.Date)
{
Value = DateS.ToString("yyyy-MM-dd 00:00:00")
};
SQLiteParameter parDatePo = new SQLiteParameter("@DatePo", System.Data.DbType.Date)
{
Value = DatePo.ToString("yyyy-MM-dd 23:59:59")
};
//Сам запрос с параметрами
var query = await db.Database.SqlQuery <Models.Sklad.Doc.DocServicePurchSQL>(SQL, parDirEmployeeID, parDateS, parDatePo).ToListAsync();
if (query.Count() > 0)
{
//CountDay = query.Count();
SumSalaryFixedServiceOne = Convert.ToDouble(query[0].CountX) * Convert.ToDouble(queryDirEmployees[i].SalaryFixedServiceOne);
}
}
#endregion
// === Б/У === === ===
#region 3. DirBonus3IDSalary: Премия с ремонта
//Алгоритм:
//Получаем [Сумму продаж]
//По градации смотрим в какой диапазон попадает и берём процент
//[Сумма продаж] * [Процент]
if (DirBonus3ID > 0)
{
double querySum = 0;
//1. Получаем сумму
if (!queryDirEmployees[i].SalarySecondHandWorkshopCheck)
{
querySum =
(
from x in db.DocSecondHandPurch1Tabs
where x.DirEmployeeID == DirEmployeeID && (x.TabDate >= DateS && x.TabDate <= DatePo)
select x.PriceCurrency
).DefaultIfEmpty(0).Sum();
}
else
{
querySum =
(
from x in db.DocSecondHandPurch1Tabs
from r in db.Rem2Parties
where
x.DirEmployeeID == DirEmployeeID && (x.TabDate >= DateS && x.TabDate <= DatePo) &&
x.docSecondHandPurch.doc.DocID == r.DocID && r.Remnant == 0
select x.PriceCurrency
).DefaultIfEmpty(0).Sum();
}
//2. Получаем градацию и в цикле пробегаем её
var queryBonus =
(
from x in db.DirBonusTabs
where x.DirBonusID == DirBonus3ID
select new
{
SumBegin = x.SumBegin,
Bonus = x.Bonus
}
).OrderByDescending(o => o.SumBegin).ToList();
for (int j = 0; j < queryBonus.Count(); j++)
{
if (queryBonus[j].SumBegin <= querySum)
{
DirBonus3IDSalary = (querySum / 100) * queryBonus[j].Bonus;
break;
}
}
}
//Ремонт: фиксированной суммы с каждого ремонта, скажем 300 руб
//Алгоритм:
//Получаем к-во ремонтов с сумой > "SalaryFixedSecondHandWorkshopOne"
double?SalaryFixedSecondHandWorkshopOne = queryDirEmployees[i].SalaryFixedSecondHandWorkshopOne;
if (SalaryFixedSecondHandWorkshopOne > 0)
{
string SQL = "";
if (!queryDirEmployees[i].SalarySecondHandWorkshopCheck)
{
SQL =
"SELECT COUNT(*) AS CountX " +
"FROM Docs, DocSecondHandPurches, DocSecondHandPurch1Tabs " +
"WHERE " +
" Docs.DocID = DocSecondHandPurches.DocID and" +
" DocSecondHandPurches.DocSecondHandPurchID = DocSecondHandPurch1Tabs.DocSecondHandPurchID and " +
" DocSecondHandPurch1Tabs.DirEmployeeID = @DirEmployeeID and " +
" DocSecondHandPurch1Tabs.TabDate BETWEEN @DateS and @DatePo " +
"GROUP BY DocSecondHandPurches.DocSecondHandPurchID ";
}
else
{
SQL =
"SELECT COUNT(*) AS CountX " +
"FROM Docs, DocSecondHandPurches, DocSecondHandPurch1Tabs, Rem2Parties " +
"WHERE " +
" Docs.DocID = DocSecondHandPurches.DocID and" +
" DocSecondHandPurches.DocSecondHandPurchID = DocSecondHandPurch1Tabs.DocSecondHandPurchID and " +
" DocSecondHandPurch1Tabs.DirEmployeeID = @DirEmployeeID and " +
" DocSecondHandPurch1Tabs.TabDate BETWEEN @DateS and @DatePo and " +
" Docs.DocID = Rem2Parties.DocID and Rem2Parties.Remnant = 0 " + // !!! //
"GROUP BY DocSecondHandPurches.DocSecondHandPurchID ";
}
SQLiteParameter parDirEmployeeID = new SQLiteParameter("@DirEmployeeID", System.Data.DbType.Int32)
{
Value = DirEmployeeID
};
SQLiteParameter parDateS = new SQLiteParameter("@DateS", System.Data.DbType.Date)
{
Value = DateS.ToString("yyyy-MM-dd 00:00:00")
};
SQLiteParameter parDatePo = new SQLiteParameter("@DatePo", System.Data.DbType.Date)
{
Value = DatePo.ToString("yyyy-MM-dd 23:59:59")
};
//Сам запрос с параметрами
var query = await db.Database.SqlQuery <Models.Sklad.Doc.DocSecondHandPurchSQL>(SQL, parDirEmployeeID, parDateS, parDatePo).ToListAsync();
if (query.Count() > 0)
{
//CountDay = query.Count();
SumSalaryFixedSecondHandWorkshopOne = Convert.ToDouble(query[0].CountX) * Convert.ToDouble(queryDirEmployees[i].SalaryFixedSecondHandWorkshopOne);
}
}
#endregion
#region 4. DirBonus4IDSalary: Премия с продаж
//Алгоритм:
//Получаем [Сумму продаж]
//По градации смотрим в какой диапазон попадает и берём процент
//[Сумма продаж] * [Процент]
if (DirBonus4ID > 0)
{
//1. Получаем сумму
double querySum =
(
from x in db.Rem2PartyMinuses
where x.DirEmployeeID == DirEmployeeID && (x.DocDate >= DateS && x.DocDate <= DatePo)
select x.Quantity * x.PriceCurrency
).DefaultIfEmpty(0).Sum();
//2. Получаем градацию и в цикле пробегаем её
var queryBonus =
(
from x in db.DirBonusTabs
where x.DirBonusID == DirBonus4ID
select new
{
SumBegin = x.SumBegin,
Bonus = x.Bonus
}
).OrderByDescending(o => o.SumBegin).ToList();
for (int j = 0; j < queryBonus.Count(); j++)
{
if (queryBonus[j].SumBegin <= querySum)
{
DirBonus4IDSalary = (querySum / 100) * queryBonus[j].Bonus;
break;
}
}
}
//Продажа: фиксированной суммы с каждой продажи, скажем 300 руб
//Алгоритм:
//Получаем к-во продаж с сумой > "SalaryFixedSecondHandRetailOne"
double?SalaryFixedSecondHandRetailOne = queryDirEmployees[i].SalaryFixedSecondHandRetailOne;
if (SalaryFixedSecondHandRetailOne > 0)
{
string SQL =
/*
* " SELECT COUNT(*) AS CountX " +
* " FROM Docs, DocSecondHandPurches, DocSecondHandPurch1Tabs " +
* " WHERE " +
* " Docs.DocID = DocSecondHandPurches.DocID and" +
* " DocSecondHandPurches.DocSecondHandPurchID = DocSecondHandPurch1Tabs.DocSecondHandPurchID and " +
* " DocSecondHandPurch1Tabs.DirEmployeeID = @DirEmployeeID and " +
* " DocSecondHandPurch1Tabs.TabDate BETWEEN @DateS and @DatePo " +
* " GROUP BY DocSecondHandPurches.DocSecondHandPurchID ";
*/
" SELECT COUNT(*) AS CountX " +
" FROM Docs, Rem2PartyMinuses " +
" WHERE " +
" Docs.DocID = Rem2PartyMinuses.DocID and" +
" Rem2PartyMinuses.DirEmployeeID = @DirEmployeeID and " +
" Docs.DocDate BETWEEN @DateS and @DatePo ";
SQLiteParameter parDirEmployeeID = new SQLiteParameter("@DirEmployeeID", System.Data.DbType.Int32)
{
Value = DirEmployeeID
};
SQLiteParameter parDateS = new SQLiteParameter("@DateS", System.Data.DbType.Date)
{
Value = DateS.ToString("yyyy-MM-dd 00:00:00")
};
SQLiteParameter parDatePo = new SQLiteParameter("@DatePo", System.Data.DbType.Date)
{
Value = DatePo.ToString("yyyy-MM-dd 23:59:59")
};
//Сам запрос с параметрами
var query = await db.Database.SqlQuery <Models.Sklad.Doc.DocSecondHandPurchSQL>(SQL, parDirEmployeeID, parDateS, parDatePo).ToListAsync();
if (query.Count() > 0)
{
//CountDay = query.Count();
SumSalaryFixedSecondHandRetailOne = Convert.ToDouble(query[0].CountX) * Convert.ToDouble(queryDirEmployees[i].SalaryFixedSecondHandRetailOne);
}
}
#endregion
#region 4. Data
string SalaryDayMonthlyName = "За день";
if (queryDirEmployees[i].SalaryDayMonthly == 2)
{
SalaryDayMonthlyName = "За месяц";
}
string DirBonusName = ""; if (queryDirEmployees[i].dirBonus != null)
{
DirBonusName = queryDirEmployees[i].dirBonus.DirBonusName;
}
string DirBonus2Name = ""; if (queryDirEmployees[i].dirBonus2 != null)
{
DirBonus2Name = queryDirEmployees[i].dirBonus2.DirBonusName;
}
string DirBonus3Name = ""; if (queryDirEmployees[i].dirBonus3 != null)
{
DirBonus3Name = queryDirEmployees[i].dirBonus3.DirBonusName;
}
string DirBonus4Name = ""; if (queryDirEmployees[i].dirBonus4 != null)
{
DirBonus4Name = queryDirEmployees[i].dirBonus4.DirBonusName;
}
Models.Sklad.Doc.DocSalaryTabSQL docSalaryTab = new Models.Sklad.Doc.DocSalaryTabSQL();
docSalaryTab.DirEmployeeID = Convert.ToInt32(DirEmployeeID);
docSalaryTab.DirEmployeeName = queryDirEmployees[i].DirEmployeeName;
if (queryDirEmployees[i].DirCurrencyID != null)
{
docSalaryTab.DirCurrencyID = Convert.ToInt32(queryDirEmployees[i].DirCurrencyID);
docSalaryTab.DirCurrencyName = queryDirEmployees[i].dirCurrency.DirCurrencyName;
docSalaryTab.DirCurrencyRate = queryDirEmployees[i].dirCurrency.DirCurrencyRate;
docSalaryTab.DirCurrencyMultiplicity = queryDirEmployees[i].dirCurrency.DirCurrencyMultiplicity;
}
else
{
docSalaryTab.DirCurrencyID = 1;
docSalaryTab.DirCurrencyName = "???";
docSalaryTab.DirCurrencyRate = 1;
docSalaryTab.DirCurrencyMultiplicity = 1;
}
docSalaryTab.Salary = Math.Round(Convert.ToDouble(queryDirEmployees[i].Salary), 2);
docSalaryTab.CountDay = CountDay;
docSalaryTab.SalaryDayMonthly = Convert.ToInt32(queryDirEmployees[i].SalaryDayMonthly);
docSalaryTab.SalaryDayMonthlyName = SalaryDayMonthlyName;
docSalaryTab.SumSalary = Math.Round(SumSalary, 2);
docSalaryTab.SalaryFixedSalesMount = Math.Round(SalaryFixedSalesMount, 2);
docSalaryTab.DirBonusID = queryDirEmployees[i].DirBonusID;
docSalaryTab.DirBonusName = DirBonusName;
docSalaryTab.DirBonusIDSalary = Math.Round(DirBonusIDSalary, 2);
docSalaryTab.DirBonus2ID = queryDirEmployees[i].DirBonus2ID;
docSalaryTab.DirBonus2Name = DirBonus2Name;
docSalaryTab.DirBonus2IDSalary = Math.Round(DirBonus2IDSalary, 2);
docSalaryTab.SumSalaryFixedServiceOne = Math.Round(SumSalaryFixedServiceOne, 2);
//Б/У
docSalaryTab.DirBonus4ID = queryDirEmployees[i].DirBonus4ID;
docSalaryTab.DirBonus4Name = DirBonus4Name;
docSalaryTab.DirBonus4IDSalary = Math.Round(DirBonus4IDSalary, 2);
docSalaryTab.SumSalaryFixedSecondHandWorkshopOne = Math.Round(SumSalaryFixedSecondHandWorkshopOne, 2);
docSalaryTab.DirBonus3ID = queryDirEmployees[i].DirBonus3ID;
docSalaryTab.DirBonus3Name = DirBonus3Name;
docSalaryTab.DirBonus3IDSalary = Math.Round(DirBonus3IDSalary, 2);
docSalaryTab.SumSalaryFixedSecondHandRetailOne = Math.Round(SumSalaryFixedSecondHandRetailOne, 2);
docSalaryTab.Sums = Math.Round(SumSalary + DirBonusIDSalary + DirBonus2IDSalary + DirBonus4IDSalary + SumSalaryFixedSecondHandWorkshopOne + DirBonus3IDSalary + SumSalaryFixedSecondHandRetailOne + SalaryFixedSalesMount + SumSalaryFixedServiceOne, 2);
arrDocSalaryTabSQL[i] = docSalaryTab;
#endregion
}
}
#endregion
#region arrDocSalaryTabSQL[i] => Models.Sklad.Doc.DocSalaryTabSQL docSalaryTab
string
ret =
"<center>" +
"<h2>" + "Зарплата сотрудников " + DirEmployeeName + " (" + DateS.ToString("yyyy-MM-dd") + " по " + DatePo.ToString("yyyy-MM-dd") + ")</h2>";
ret += ReportHeaderTab(pLanguage) + "</center>";
double
SalaryS = 0, SumSalaryS = 0, SalaryFixedSalesMountS = 0, DirBonusIDSalaryS = 0, DirBonus2IDSalaryS = 0, SumSalaryFixedServiceOneS = 0,
DirBonus4IDSalaryS = 0, SumSalaryFixedSecondHandWorkshopOneS = 0,
DirBonus3IDSalaryS = 0, SumSalaryFixedSecondHandRetailOneS = 0,
SumsS = 0;
int CountDayS = 0;
for (int i = 0; i < arrDocSalaryTabSQL.Length; i++)
{
Models.Sklad.Doc.DocSalaryTabSQL docSalaryTab = (Models.Sklad.Doc.DocSalaryTabSQL)arrDocSalaryTabSQL[i];
SalaryS += docSalaryTab.Salary;
CountDayS += docSalaryTab.CountDay;
SumSalaryS += docSalaryTab.SumSalary;
SalaryFixedSalesMountS += docSalaryTab.SalaryFixedSalesMount;
DirBonusIDSalaryS += docSalaryTab.DirBonusIDSalary;
DirBonus2IDSalaryS += docSalaryTab.DirBonus2IDSalary;
SumSalaryFixedServiceOneS += docSalaryTab.SumSalaryFixedServiceOne;
//Б/У
DirBonus4IDSalaryS += docSalaryTab.DirBonus4IDSalary;
SumSalaryFixedSecondHandWorkshopOneS += Convert.ToDouble(docSalaryTab.SumSalaryFixedSecondHandWorkshopOne);
DirBonus3IDSalaryS += docSalaryTab.DirBonus3IDSalary;
SumSalaryFixedSecondHandRetailOneS += Convert.ToDouble(docSalaryTab.SumSalaryFixedSecondHandRetailOne);
SumsS += docSalaryTab.Sums;
ret +=
"<TR>" +
//Сотрудник
"<TD>" + docSalaryTab.DirEmployeeName + "</TD> " +
"<TD> </TD> " +
//Зарплата
"<TD>" + docSalaryTab.Salary + "</TD> " +
//Тип
"<TD>" + docSalaryTab.SalaryDayMonthlyName + "</TD> " +
//Дней
"<TD>" + docSalaryTab.CountDay + "</TD> " +
//Сумма
"<TD>" + docSalaryTab.SumSalary + "</TD> " +
//Сумма фикс.
"<TD>" + docSalaryTab.SalaryFixedSalesMount + "</TD> " +
"<TD> </TD> " +
//Премия (продавца)
"<TD>" + docSalaryTab.DirBonusName + "</TD> " +
//Сумма
"<TD>" + docSalaryTab.DirBonusIDSalary + "</TD> " +
"<TD> </TD> " +
//Премия (мастера)
"<TD>" + docSalaryTab.DirBonus2Name + "</TD> " +
//Сумма
"<TD>" + docSalaryTab.DirBonus2IDSalary + "</TD> " +
//За ремонт
"<TD>" + docSalaryTab.SumSalaryFixedServiceOne + "</TD> " +
"<TD> </TD> " +
//Б/У
//Премия (мастера)
"<TD>" + docSalaryTab.DirBonus4Name + "</TD> " +
//Сумма
"<TD>" + docSalaryTab.DirBonus4IDSalary + "</TD> " +
//За ремонт
"<TD>" + docSalaryTab.SumSalaryFixedSecondHandWorkshopOne + "</TD> " +
"<TD> </TD> " +
//Премия (мастера)
"<TD>" + docSalaryTab.DirBonus3Name + "</TD> " +
//Сумма
"<TD>" + docSalaryTab.DirBonus3IDSalary + "</TD> " +
//За ремонт
"<TD>" + docSalaryTab.SumSalaryFixedSecondHandRetailOne + "</TD> " +
"<TD> </TD> " +
//Общая
"<TD>" + docSalaryTab.Sums + "</TD> " +
"</TR>";
}
ret +=
"<TR>" +
//Сотрудник
"<TD><b> Итого </b></TD> " +
"<TD> </TD> " +
//Зарплата
"<TD><b>" + SalaryS + "</b></TD> " +
//Тип
"<TD></TD> " +
//Дней
"<TD><b>" + CountDayS + "</b></TD> " +
//Сумма
"<TD><b>" + SumSalaryS + "</b></TD> " +
//Сумма фикс.
"<TD><b>" + SalaryFixedSalesMountS + "</b></TD> " +
"<TD> </TD> " +
//Премия (продавца)
"<TD></TD> " +
//Сумма
"<TD><b>" + DirBonusIDSalaryS + "</b></TD> " +
"<TD> </TD> " +
//Премия (мастера)
"<TD></TD> " +
//Сумма
"<TD><b>" + DirBonus2IDSalaryS + "</b></TD> " +
//За ремонт
"<TD><b>" + SumSalaryFixedServiceOneS + "</b></TD> " +
"<TD> </TD> " +
//Б/У
//Премия (мастера)
"<TD></TD> " +
//Сумма
"<TD><b>" + DirBonus4IDSalaryS + "</b></TD> " +
//За ремонт
"<TD><b>" + SumSalaryFixedSecondHandWorkshopOneS + "</b></TD> " +
"<TD> </TD> " +
//Премия (мастера)
"<TD></TD> " +
//Сумма
"<TD><b>" + DirBonus3IDSalaryS + "</b></TD> " +
//За ремонт
"<TD><b>" + SumSalaryFixedSecondHandRetailOneS + "</b></TD> " +
"<TD> </TD> " +
//Общая
"<TD><b>" + SumsS + "</b></TD> " +
"</TR>";
ret += "</TABLE>"; //"К-во Суотрудников: " + arrDocSalaryTabSQL.Length;
#endregion
return(ret);
}
