static void CreatePoorRestoransResultByEmp(int dep)
{
Application app = new Microsoft.Office.Interop.Excel.Application();
Workbook Wb = app.Workbooks.Add(true);
Worksheet Ws = (Microsoft.Office.Interop.Excel.Worksheet)Wb.ActiveSheet;
app.Visible = true;
QDataDataContext QDB = new QDataDataContext();
IQueryable <QuestAnswers> AllAnwers = QDB.QuestAnswers.Where(a => a.AnketaId == AnketaId);
var PoorAnswEmps = AllAnwers.Where(a => (a.Result == 1 || a.Result == 2) && (a.AnketaId == AnketaId) && a.EmpDepNum == dep).Select(c => new { empId = c.EmpId.GetValueOrDefault(), posit = c.EmpDepPos.GetValueOrDefault() }).Distinct();
int row = 1;
StaffDataDataContext SDB = new StaffDataDataContext();
foreach (var emp in PoorAnswEmps)
{
//Ws.Cells[row, 4] = SDB.StaffDepartments.Where(a => a.DepID == DepNum).First().DepName;
Ws.Cells[row, 3] = SDB.Staffposition.Where(a => a.POSITION_ID == emp.posit).First().NAME;
Ws.Cells[row, 2] = SDB.StaffEmployee.Where(a => a.EMPLOYEE_ID == emp.empId).First().LAST_NAME + " " + SDB.StaffEmployee.Where(a => a.EMPLOYEE_ID == emp.empId).FirstOrDefault().FIRST_NAME;
row++;
foreach (QuestQuestions QQ in QDB.QuestQuestions.Where(a => a.AnketaId == AnketaId))
{
//Ws.Cells[row, 1] = (row - 1).ToString();
Ws.Cells[row, 2] = QQ.Text;
Ws.Cells[row, 3] = AllAnwers.Where(a => (a.AnketaId == AnketaId) && a.EmpId == emp.empId && a.QuestionId == QQ.Id).FirstOrDefault().Result.ToString();
row++;
}
row++;
row++;
}
}
static void CreateGreetResult()
{
try
{
Application app = new Microsoft.Office.Interop.Excel.Application();
Workbook Wb = app.Workbooks.Add(true);
Worksheet Ws = (Microsoft.Office.Interop.Excel.Worksheet)Wb.ActiveSheet;
app.Visible = true;
StaffDataDataContext SDB = new StaffDataDataContext();
QDataDataContext QDB = new QDataDataContext();
IQueryable <int> Deps = QDB.QuestAnswers.Select(a => a.EmpDepNum.Value).Distinct();
IQueryable <QuestGreetings> QGr = QDB.QuestGreetings.Where(a => ((a.Greet1.Length > 2 || a.Greet2.Length > 2 || a.Greet3.Length > 2) && (a.AnketaId == AnketaId)));
var res = QGr.Select(g => new
{
g1 = g.Greet1,
g2 = g.Greet2,
g3 = g.Greet3,
Dn = SDB.StaffDepartments.Where(a => a.DepID == QDB.QuestAnswers.Where(b => b.EmpId == g.EmpId && b.AnketaId == AnketaId).First().EmpDepNum).First().DepName,
Pn = SDB.Staffposition.Where(a => a.POSITION_ID == QDB.QuestAnswers.Where(b => b.EmpId == g.EmpId && b.AnketaId == AnketaId).First().EmpDepPos).First().NAME,
}
);
int row = 1;
int c = QDB.QuestGreetings.Where(a => ((a.Greet1.Length > 2 || a.Greet2.Length > 2 || a.Greet3.Length > 2) && (a.AnketaId == AnketaId))).Count();
foreach (var G in QDB.QuestGreetings.Where(a => ((a.Greet1.Length > 2 || a.Greet2.Length > 2 || a.Greet3.Length > 2) && (a.AnketaId == AnketaId))))
{
try
{
int EmplId = G.EmpId;
int PosId = QDB.QuestAnswers.Where(b => b.EmpId == G.EmpId && b.AnketaId == AnketaId).First().EmpDepPos.Value;
int DepNum = QDB.QuestAnswers.Where(b => b.EmpId == G.EmpId && b.AnketaId == AnketaId).First().EmpDepNum.Value;
if (OneDep)
{
if (DepNum != OneDepNum)
{
continue;
}
}
Ws.Cells[row, 1] = G.Greet1;
Ws.Cells[row, 2] = G.Greet2;
Ws.Cells[row, 3] = G.Greet3;
Ws.Cells[row, 4] = SDB.StaffDepartments.Where(a => a.DepID == DepNum).First().DepName;
Ws.Cells[row, 5] = SDB.Staffposition.Where(a => a.POSITION_ID == PosId).First().NAME;
Ws.Cells[row, 6] = SDB.StaffEmployee.Where(a => a.EMPLOYEE_ID == EmplId).First().LAST_NAME + " " + SDB.StaffEmployee.Where(a => a.EMPLOYEE_ID == EmplId).FirstOrDefault().FIRST_NAME;
Ws.Cells[row, 7] = EmplId;
row++;
}
catch (Exception e)
{
string s = e.Message;
}
}
}
catch (Exception ee)
{
string ss = ee.Message;
}
}
static void CreateAllPoorRestoransResult()
{
List <int> KitchenPos = new List <int>()
{
2, 8, 4, 49, 121
};
List <int> StoykaPos = new List <int>()
{
5, 6, 12
};
List <int> WPos = new List <int>()
{
27, 3
};
Application app = new Microsoft.Office.Interop.Excel.Application();
Workbook Wb = app.Workbooks.Add(true);
Worksheet Ws = (Microsoft.Office.Interop.Excel.Worksheet)Wb.ActiveSheet;
app.Visible = true;
QDataDataContext QDB = new QDataDataContext();
IQueryable <int> Deps = QDB.QuestAnswers.Where(a => a.AnketaId == AnketaId).Select(a => a.EmpDepNum.Value).Distinct();
StaffDataDataContext SDB = new StaffDataDataContext();
Ws.Name = "Все рестораны";
Dictionary <int, Tuple <int, int> > Res = new Dictionary <int, Tuple <int, int> >();
IQueryable <QuestAnswers> AllAnwers = QDB.QuestAnswers.Where(a => a.AnketaId == AnketaId);
IQueryable <int> GoodAnsw = AllAnwers.Where(a => a.Result == 1 && (a.AnketaId == AnketaId)).Select(c => c.QuestionId.Value);
int QCount = QDB.QuestQuestions.Where(a => a.AnketaId == AnketaId).Count();
int VCount = AllAnwers.Where(a => a.AnketaId == AnketaId).Count() / QCount;
int row = 1;
Ws.Cells[row, 1] = "Id";
Ws.Cells[row, 2] = "Вопрос";
Ws.Cells[row, 3] = "Всего";
Ws.Cells[row, 4] = "Оценок 1";
Ws.Cells[row, 5] = "____%____";
row = 2;
foreach (QuestQuestions QQ in QDB.QuestQuestions.Where(a => a.AnketaId == AnketaId))
{
Ws.Cells[row, 1] = (row - 1).ToString();
Ws.Cells[row, 2] = QQ.Text;
int FiveCount = GoodAnsw.Where(a => a == QQ.Id).Count();
Ws.Cells[row, 3] = VCount.ToString();
Ws.Cells[row, 4] = FiveCount.ToString();
Ws.Cells[row, 5] = (FiveCount * 100) / VCount;
row++;
}
}
static void CreateAllPoorResult()
{
List <int> KitchenPos = new List <int>()
{
2, 8, 4, 49, 121
};
List <int> StoykaPos = new List <int>()
{
5, 6, 12
};
List <int> WPos = new List <int>()
{
27, 3
};
Application app = new Microsoft.Office.Interop.Excel.Application();
Workbook Wb = app.Workbooks.Add(true);
Worksheet Ws = (Microsoft.Office.Interop.Excel.Worksheet)Wb.ActiveSheet;
app.Visible = true;
QDataDataContext QDB = new QDataDataContext();
IQueryable <int> Deps = QDB.QuestAnswers.Where(a => a.AnketaId == AnketaId).Select(a => a.EmpDepNum.Value).Distinct();
foreach (int Dep in Deps)
{
StaffDataDataContext SDB = new StaffDataDataContext();
string DName = SDB.StaffDepartments.Where(a => a.DepID == Dep).First().DepName;
Ws.Name = DName.Substring(0, Math.Min(DName.Count(), 20));
Dictionary <int, Tuple <int, int> > Res = new Dictionary <int, Tuple <int, int> >();
IQueryable <QuestAnswers> AllAnwers = QDB.QuestAnswers.Where(a => a.EmpDepNum == Dep && a.AnketaId == AnketaId);
IQueryable <int> GoodAnsw = AllAnwers.Where(a => a.Result == 1 && (a.AnketaId == AnketaId)).Select(c => c.QuestionId.Value);
IQueryable <int> GoodAnswK = AllAnwers.Where(a => a.Result == 1 && (a.AnketaId == AnketaId) && KitchenPos.Contains(a.EmpDepPos.Value)).Select(c => c.QuestionId.Value);
IQueryable <int> GoodAnswS = AllAnwers.Where(a => a.Result == 1 && (a.AnketaId == AnketaId) && StoykaPos.Contains(a.EmpDepPos.Value)).Select(c => c.QuestionId.Value);
IQueryable <int> GoodAnswW = AllAnwers.Where(a => a.Result == 1 && (a.AnketaId == AnketaId) && WPos.Contains(a.EmpDepPos.Value)).Select(c => c.QuestionId.Value);
int QCount = QDB.QuestQuestions.Where(a => a.AnketaId == AnketaId).Count();
int VCount = AllAnwers.Where(a => a.AnketaId == AnketaId).Count() / QCount;
int row = 1;
Ws.Cells[row, 1] = "Id";
Ws.Cells[row, 2] = "Вопрос";
Ws.Cells[row, 3] = "Всего";
Ws.Cells[row, 4] = "Оценок 1";
Ws.Cells[row, 5] = "____%____";
row = 2;
foreach (QuestQuestions QQ in QDB.QuestQuestions.Where(a => a.AnketaId == AnketaId))
{
Ws.Cells[row, 1] = (row - 1).ToString();
Ws.Cells[row, 2] = QQ.Text;
int FiveCount = GoodAnsw.Where(a => a == QQ.Id).Count();
Ws.Cells[row, 3] = VCount.ToString();
Ws.Cells[row, 4] = FiveCount.ToString();
Ws.Cells[row, 5] = (FiveCount * 100) / VCount;
row++;
}
row++;
if (!AllPosOnly)
{
VCount = AllAnwers.Where(a => KitchenPos.Contains(a.EmpDepPos.Value) && a.AnketaId == AnketaId).Count() / QCount;
Ws.Cells[row, 2] = "Кухня";
row++;
if (VCount > 0)
{
foreach (QuestQuestions QQ in QDB.QuestQuestions.Where(a => a.AnketaId == AnketaId))
{
Ws.Cells[row, 1] = (row - 1).ToString();
Ws.Cells[row, 2] = QQ.Text;
int FiveCount = GoodAnswK.Where(a => a == QQ.Id).Count();
Ws.Cells[row, 3] = VCount.ToString();
Ws.Cells[row, 4] = FiveCount.ToString();
Ws.Cells[row, 5] = (FiveCount * 100) / VCount;
row++;
}
}
row++;
VCount = AllAnwers.Where(a => StoykaPos.Contains(a.EmpDepPos.Value) && a.AnketaId == AnketaId).Count() / QCount;
if (VCount > 0)
{
Ws.Cells[row, 2] = "Стойка";
row++;
foreach (QuestQuestions QQ in QDB.QuestQuestions.Where(a => a.AnketaId == AnketaId))
{
Ws.Cells[row, 1] = (row - 1).ToString();
Ws.Cells[row, 2] = QQ.Text;
int FiveCount = GoodAnswS.Where(a => a == QQ.Id).Count();
Ws.Cells[row, 3] = VCount.ToString();
Ws.Cells[row, 4] = FiveCount.ToString();
Ws.Cells[row, 5] = (FiveCount * 100) / VCount;
row++;
}
}
row++;
VCount = AllAnwers.Where(a => WPos.Contains(a.EmpDepPos.Value) && a.AnketaId == AnketaId).Count() / QCount;
if (VCount > 0)
{
Ws.Cells[row, 2] = "Официанты";
row++;
foreach (QuestQuestions QQ in QDB.QuestQuestions.Where(a => a.AnketaId == AnketaId))
{
Ws.Cells[row, 1] = (row - 1).ToString();
Ws.Cells[row, 2] = QQ.Text;
int FiveCount = GoodAnswW.Where(a => a == QQ.Id).Count();
Ws.Cells[row, 3] = VCount.ToString();
Ws.Cells[row, 4] = FiveCount.ToString();
Ws.Cells[row, 5] = (FiveCount * 100) / VCount;
row++;
}
}
}
Ws.get_Range("A1:Z1").EntireColumn.AutoFit();
Ws = Wb.Sheets.Add();
}
}
private static void CreateWsPage(Worksheet Ws, IQueryable <QuestAnswers> AllAnwers, QDataDataContext QDB)
{
IQueryable <int> GoodAnsw = AllAnwers.Where(a => a.Result == 5).Select(c => c.QuestionId.Value);
int QCount = QDB.QuestQuestions.Where(a => a.AnketaId == AnketaId).Count();
int VCount = AllAnwers.Where(a => a.AnketaId == AnketaId).Count() / QCount;
int row = 1;
Ws.Cells[row, 1] = "Id";
Ws.Cells[row, 2] = "Вопрос";
Ws.Cells[row, 3] = "Всего";
Ws.Cells[row, 4] = "Ответов Да";
Ws.Cells[row, 5] = "____%____";
Ws.Cells[row, 6] = "Ответов Нет";
Ws.Cells[row, 7] = "____%____";
row = 2;
foreach (QuestQuestions QQ in QDB.QuestQuestions.Where(a => a.AnketaId == AnketaId))
{
Ws.Cells[row, 1] = (row - 1).ToString();
Ws.Cells[row, 2] = QQ.Text;
int FiveCount = GoodAnsw.Where(a => a == QQ.Id).Count();
Ws.Cells[row, 3] = VCount.ToString();
Ws.Cells[row, 4] = FiveCount.ToString();
Ws.Cells[row, 5] = (FiveCount * 100) / VCount;
Ws.Cells[row, 6] = (VCount - FiveCount).ToString();
Ws.Cells[row, 7] = 100 - (FiveCount * 100) / VCount;
row++;
}
row++;
Ws.get_Range("A1:Z1").EntireColumn.AutoFit();
}
static void CreateAllYesNoResult(bool AllPos, List <int> WPos)
{
Application app = new Microsoft.Office.Interop.Excel.Application();
Workbook Wb = app.Workbooks.Add(true);
Worksheet Ws = (Microsoft.Office.Interop.Excel.Worksheet)Wb.ActiveSheet;
app.Visible = true;
QDataDataContext QDB = new QDataDataContext();
IQueryable <int> Deps = QDB.QuestAnswers.Where(a => a.AnketaId == AnketaId).Select(a => a.EmpDepNum.Value).Distinct();
foreach (int Dep in Deps)
{
StaffDataDataContext SDB = new StaffDataDataContext();
string DName = SDB.StaffDepartments.Where(a => a.DepID == Dep).First().DepName;
Ws.Name = DName.Substring(0, Math.Min(DName.Count(), 20));
Dictionary <int, Tuple <int, int> > Res = new Dictionary <int, Tuple <int, int> >();
IQueryable <QuestAnswers> AllAnwers = QDB.QuestAnswers.Where(a => a.EmpDepNum == Dep && a.AnketaId == AnketaId && WPos.Contains(a.EmpDepPos.Value));
if (AllPos)
{
AllAnwers = QDB.QuestAnswers.Where(a => a.EmpDepNum == Dep && a.AnketaId == AnketaId);
}
CreateWsPage(Ws, AllAnwers, QDB);
/*
* IQueryable<int> GoodAnsw = AllAnwers.Where(a => a.Result == 5).Select(c => c.QuestionId.Value);
* //IQueryable<int> GoodAnswW = AllAnwers.Where(a => a.Result == 1 && (a.AnketaId == AnketaId) && WPos.Contains(a.EmpDepPos.Value)).Select(c => c.QuestionId.Value);
* int QCount = QDB.QuestQuestions.Where(a => a.AnketaId == AnketaId).Count();
* int VCount = AllAnwers.Where(a => a.AnketaId == AnketaId).Count() / QCount;
*
* int row = 1;
* Ws.Cells[row, 1] = "Id";
* Ws.Cells[row, 2] = "Вопрос";
* Ws.Cells[row, 3] = "Всего";
* Ws.Cells[row, 4] = "Ответов Да";
* Ws.Cells[row, 5] = "____%____";
* Ws.Cells[row, 6] = "Ответов Нет";
* Ws.Cells[row, 7] = "____%____";
* row = 2;
* foreach (QuestQuestions QQ in QDB.QuestQuestions.Where(a => a.AnketaId == AnketaId))
* {
* Ws.Cells[row, 1] = (row - 1).ToString();
* Ws.Cells[row, 2] = QQ.Text;
* int FiveCount = GoodAnsw.Where(a => a == QQ.Id).Count();
* Ws.Cells[row, 3] = VCount.ToString();
* Ws.Cells[row, 4] = FiveCount.ToString();
* Ws.Cells[row, 5] = (FiveCount * 100) / VCount;
* Ws.Cells[row, 6] = (VCount-FiveCount).ToString();
* Ws.Cells[row, 7] = 100- (FiveCount * 100) / VCount;
* row++;
* }
*
* row++;
*
* Ws.get_Range("A1:Z1").EntireColumn.AutoFit();
*/
Ws = Wb.Sheets.Add();
}
Ws.Name = "Все рестораны";
IQueryable <QuestAnswers> AllAnwers2 = QDB.QuestAnswers.Where(a => a.AnketaId == AnketaId && WPos.Contains(a.EmpDepPos.Value));
if (AllPos)
{
AllAnwers2 = QDB.QuestAnswers.Where(a => a.AnketaId == AnketaId);
}
CreateWsPage(Ws, AllAnwers2, QDB);
}