static void Main()
{
List<Result> resultElement = new List<Result>();
List<Result> resultElement2 = new List<Result>();
List<Result> resultElement_JobType = new List<Result>();
FileOperation fileOp = new FileOperation();
JsonWriter jsonwriter = new JsonWriter();
using (var db = new MFGTEntities())
{
db.CommandTimeout = 15 * 60;
resultElement = (from jb in db.Jobs
where jb.CreatedDate > new DateTime(2013,12,11)
&& jb.JobType == "Custom"
&& jb.Creator != "polomsky"
&& jb.Creator != "weije"
&& jb.Creator != "zhangjus"
&& jb.Creator != "chenha"
&& jb.Creator != "wangmar"
&& jb.Creator != "palomag"
select new Result { JobID = jb.JobID,
CreateDate = jb.CreatedDate,
UserName = jb.Creator }).ToList();
}
//Get the number of users for different JobType by month
using (var db = new MFGTEntities())
{
db.CommandTimeout = 15 * 60;
resultElement_JobType = (from jb in db.Jobs
where jb.CreatedDate > new DateTime(2013,1,1)
&& jb.Creator != "polomsky"
&& jb.Creator != "weije"
&& jb.Creator != "zhangjus"
&& jb.Creator != "chenha"
&& jb.Creator != "wangmar"
&& jb.Creator != "palomag"
select new Result
{
JobID = jb.JobID,
MFGT_CreateDate = jb.CreatedDate,
UserName = jb.Creator,
JobType = jb.JobType
}).ToList();
}
using (var db2 = new AAT_DTF_ecsEntities2())
{
resultElement2 = (from jb2 in db2.jobs
where jb2.StartTime > new DateTime(2013, 12, 11)
&& jb2.Submitter != "polomsky"
&& jb2.Submitter != "weije"
&& jb2.Submitter != "zhangjus"
&& jb2.Submitter != "chenha"
&& jb2.Submitter != "wangmar"
&& jb2.Submitter != "palomag"
select new Result
{
JobID = jb2.JobID,
CreateDate = jb2.StartTime,
UserName = jb2.Submitter
}).ToList();
}
//DTF4 vs. DTF5 user count
string fileName = @"C:\Users\guj\Dropbox\DTFUserCount.json";
fileOp.DelectExist(fileName);
using (FileStream fs = File.Create(fileName, 1024))
{
jsonwriter.WriteJson_xAxis(fs, "spline", "MFGT&AAT_DTF_ecs", "Date");
QueryContent(fs, resultElement,resultElement2, 1);
jsonwriter.WriteJson_yAxis_head(fs, "UserCount", "EAC100");
jsonwriter.WriteJson_yAxis(fs, "DTF4", "#EAC100", "diamond");
QueryContent(fs, resultElement, resultElement2,2);
fileOp.AddText(fs, ",");
jsonwriter.WriteJson_yAxis(fs, "DTF5", "#0080FF", "circle");
QueryContent(fs, resultElement,resultElement2, 3);
fileOp.AddText(fs, "]}");
}
//Custom vs. official vs. codecoverage user count by month
string fileName_JobType = @"C:\Users\guj\Dropbox\DTFJobTypeUserCount.json";
fileOp.DelectExist(fileName_JobType);
using (FileStream fs3 = File.Create(fileName_JobType, 1024))
{
jsonwriter.WriteJson_xAxis(fs3, "spline", "MFGT", "Month");
QueryContent_JobType(fs3, resultElement_JobType, 1);
jsonwriter.WriteJson_yAxis_head(fs3, "UserCount", "EAC100");
jsonwriter.WriteJson_yAxis(fs3, "Custom", "#EAC100", "diamond");
QueryContent_JobType(fs3, resultElement_JobType, 2);
fileOp.AddText(fs3, ",");
jsonwriter.WriteJson_yAxis(fs3, "Official", "#336600", "circle");
QueryContent_JobType(fs3, resultElement_JobType, 3);
fileOp.AddText(fs3, ",");
jsonwriter.WriteJson_yAxis(fs3, "CodeCoverage", "#ff6600", "square");
QueryContent_JobType(fs3, resultElement_JobType, 4);
fileOp.AddText(fs3, "]}");
}
}
private static void QueryContent_JobType(FileStream fs, List<Result> resultElement, int k)
{
FileOperation fileOp = new FileOperation();
DateOperation dateOp = new DateOperation();
DateTime startTime = new DateTime(2013,1,1);
do
{
int cusotomCount = (from item in resultElement
where item.JobType == "Custom"
&& item.MFGT_CreateDate > startTime
&& item.MFGT_CreateDate < startTime.AddMonths(1)
select item.UserName).Distinct().Count();
int officialCount = (from item in resultElement
where item.JobType == "Official"
&& item.MFGT_CreateDate > startTime
&& item.MFGT_CreateDate < startTime.AddMonths(1)
select item.UserName).Distinct().Count();
int icCount = (from item in resultElement
where item.JobType == "CodeCoverage"
&& item.MFGT_CreateDate > startTime
&& item.MFGT_CreateDate < startTime.AddMonths(1)
select item.UserName).Distinct().Count();
switch (k)
{
case 1:
fileOp.AddText(fs, "\"");
fileOp.AddText(fs, startTime.Month.ToString());
fileOp.AddText(fs, "\",");
break;
case 2: //custom
fileOp.AddText(fs, cusotomCount.ToString());
fileOp.AddText(fs, ",");
break;
case 3: //official
fileOp.AddText(fs, officialCount.ToString());
fileOp.AddText(fs, ",");
break;
case 4: //codecoverage
fileOp.AddText(fs, icCount.ToString());
fileOp.AddText(fs, ",");
break;
}
startTime = startTime.AddMonths(1);
} while (startTime <= DateTime.Now);
fileOp.AddText(fs, "]}");
}
private static void QueryContent(FileStream fs, List<Result> resultElement, List<Result> resultElement2, int k)
{
FileOperation fileOp = new FileOperation();
DateOperation dateOp = new DateOperation();
DateTime StartDate = new DateTime(2013, 12, 11);
DateTime EndDate = DateTime.Now.AddDays(-1);
var db_ECS = new AAT_DTF_ecsEntities2();
foreach (DateTime date in dateOp.GetDateRange(StartDate, EndDate))
{
var jobs_DTF = (from dtf in resultElement
select dtf.JobID).ToList();
var jobs_ECS = (from ecs in resultElement2
select ecs.JobID).ToList();
int userInDTF5 = (from esc in resultElement2
where
jobs_DTF.Contains(esc.JobID) // ecs.JobID belongs to custom job
&& esc.CreateDate < date.AddDays(1)
&& esc.CreateDate > date
select esc.UserName).Distinct().Count();
int userInDTF4 = (from dtf in resultElement
where !jobs_ECS.Contains(dtf.JobID) // dtf.JobID != ecs.JobID
&& dtf.CreateDate < date.AddDays(1)
&& dtf.CreateDate > date
select dtf.UserName).Distinct().Count();
switch (k)
{
case 1:
fileOp.AddText(fs, "\"");
fileOp.AddText(fs, date.ToShortDateString().ToString());
fileOp.AddText(fs, "\",");
break;
case 2:
fileOp.AddText(fs, userInDTF4.ToString());
fileOp.AddText(fs, ",");
break;
case 3:
fileOp.AddText(fs, userInDTF5.ToString());
fileOp.AddText(fs, ",");
break;
}
}
fileOp.AddText(fs, "]}");
}
//query lead time
private static void QueryContent(FileStream fs, List<Result> resultElement, int k)
{
FileOperation fileOp = new FileOperation();
var monthCount = ((from item in resultElement
select new
{
Month = item.CreateDate.Month,
UserCount = item.UserName
})
.GroupBy(x => x.Month)
.Select(g => new { Month = g.Key, UserCount = g.Distinct().Count() }
)).ToList();
foreach (var monthUser in monthCount)
{
switch (k)
{
case 1:
fileOp.AddText(fs, "\"");
fileOp.AddText(fs, monthUser.Month.ToString());
fileOp.AddText(fs, "\",");
break;
case 2:
fileOp.AddText(fs, monthUser.UserCount.ToString());
fileOp.AddText(fs, ",");
break;
case 3:
string csv = string.Format("{0},{1}", monthUser.Month.ToString(), monthUser.UserCount.ToString());
fileOp.AddText(fs, csv);
fileOp.AddText(fs, "\r\n");
break;
}
}
fileOp.AddText(fs, "]}");
}
private static void QueryContent_iTestVersionTrend(FileStream fs4, List<Result> resultElement, int q)
{
DateOperation dateOp = new DateOperation();
FileOperation fileOp = new FileOperation();
DateTime StartDate = new DateTime(2013, 8, 13);
DateTime EndDate = DateTime.Now.AddDays(-1);
foreach (DateTime date in dateOp.GetDateRange(StartDate, EndDate))
{
int userCount_331 = (from rst in resultElement
where rst.ApplicationVersion.Contains("9.5.331")
&& rst.CreateDate < date.AddDays(1)
&& rst.CreateDate > date
select rst.UserName).Distinct().Count();
int userCount_334 = (from rst in resultElement
where rst.ApplicationVersion.Contains("9.5.334")
&& rst.CreateDate < date.AddDays(1)
&& rst.CreateDate > date
select rst.UserName).Distinct().Count();
int userCount_355 = (from rst in resultElement
where rst.ApplicationVersion.Contains("9.6.355")
&& rst.CreateDate < date.AddDays(1)
&& rst.CreateDate > date
select rst.UserName).Distinct().Count();
switch (q)
{
case 1:
fileOp.AddText(fs4, "\"");
fileOp.AddText(fs4, date.ToShortDateString().ToString());
fileOp.AddText(fs4, "\",");
break;
case 2:
fileOp.AddText(fs4, userCount_331.ToString());
fileOp.AddText(fs4, ",");
break;
case 3:
fileOp.AddText(fs4, userCount_334.ToString());
fileOp.AddText(fs4, ",");
break;
case 4:
fileOp.AddText(fs4, userCount_355.ToString());
fileOp.AddText(fs4, ",");
break;
}
}
fileOp.AddText(fs4, "]}");
}
static void Main()
{
FileOperation fileOp = new FileOperation();
JsonWriter jsonwriter = new JsonWriter();
List<Result> resultElement = new List<Result>();
using (var db = new TCIPEntities())
{
db.CommandTimeout = 15 * 60;
resultElement = (from cip in db.ApplicationCIPs.OrderByDescending(x => x.CreateDate).Take(200000)
where
cip.ApplicationName == "iTest"
&& cip.ADSUserName != "polomsky"
&& cip.ADSUserName != "weije"
&& cip.ADSUserName != "zhangjus"
&& cip.ADSUserName != "chenha"
&& cip.ADSUserName != "wangmar"
&& cip.ADSUserName != "palomag"
&& cip.CreateDate > new DateTime(2013, 8, 10)
select new Result
{
UserName = cip.ADSUserName,
CreateDate = cip.CreateDate,
ApplicationVersion = cip.ApplicationVersion
}).ToList();
}
//iTest Json File
string fileName_iTestUser = @"C:\Users\guj\Dropbox\iTest.json";
fileOp.DelectExist(fileName_iTestUser);
using (FileStream fs = File.Create(fileName_iTestUser, 1024))
{
jsonwriter.WriteJson_xAxis(fs, "column", "USSCLPDDBSGP002-AATSPRD1", "Month");
QueryContent(fs, resultElement, 1);
jsonwriter.WriteJson_yAxis_head(fs, "Month", "#D94600");
jsonwriter.WriteJson_yAxis(fs, "UserCount", "#D94600", "square");
QueryContent(fs, resultElement, 2);
fileOp.AddText(fs, "]}");
}
////iTest Excel
//string excelName_iTestUser = @"\\installreport\BO\ToolsCIP\iTest.csv";
//fileOp.DelectExist(excelName_iTestUser);
//using (FileStream fs1 = File.Create(excelName_iTestUser, 1024))
//{
// fileOp.AddText(fs1, "WeekNum,#Users\r\n");
// QueryContent(fs1, resultElement, 3);
//}
//iTestVersionTrend Json
string fileName_iTestVersionTrend = @"C:\Users\guj\Dropbox\iTestVersionTrend.json";
fileOp.DelectExist(fileName_iTestVersionTrend);
using (FileStream fs4 = System.IO.File.Create(fileName_iTestVersionTrend, 1024))
{
jsonwriter.WriteJson_xAxis(fs4, "spline", "USSCLPDDBSGP002-AATSPRD1", "Date");
QueryContent_iTestVersionTrend(fs4, resultElement, 1);
jsonwriter.WriteJson_yAxis_head(fs4, "buildNo.", "UserCount");
jsonwriter.WriteJson_yAxis(fs4, "build9.5.331", "#D94600", "square");
QueryContent_iTestVersionTrend(fs4, resultElement, 2);
fileOp.AddText(fs4, ",");
jsonwriter.WriteJson_yAxis(fs4, "build9.5.334", "#0080FF", "circle");
QueryContent_iTestVersionTrend(fs4, resultElement, 3);
fileOp.AddText(fs4, ",");
jsonwriter.WriteJson_yAxis(fs4, "build9.6.355", "#EAC100", "diamond");
QueryContent_iTestVersionTrend(fs4, resultElement, 4);
fileOp.AddText(fs4, "]}");
}
//iTestVersion Excel
string excelName_iTestVersion = @"\\installreport\BO\ToolsCIP\iTestVersion.csv";
fileOp.DelectExist(excelName_iTestVersion);
using (FileStream fs3 = File.Create(excelName_iTestVersion, 1024))
{
fileOp.AddText(fs3, "Date,iTestVersion,UserName,\r\n");
QueryContent_iTestVersion(fs3, resultElement);
}
}
//Export to excel row data
private static void QueryContent_iTestVersion(FileStream fs3, List<Result> resultElement)
{
FileOperation fileOp = new FileOperation();
var recentData = (from item in resultElement
where item.CreateDate > DateTime.Now.AddDays(-7)
&& item.UserName !="iTools"
select new Result{
CreateDate = item.CreateDate,
ApplicationVersion = item.ApplicationVersion,
UserName = item.UserName
}).OrderBy(x=>x.ApplicationVersion).Distinct().ToList();
foreach (var iDate in recentData)
{
string csv = string.Format("{0},{1},{2}", iDate.CreateDate.ToString(), iDate.ApplicationVersion.ToString(), iDate.UserName.ToString());
fileOp.AddText(fs3, csv);
fileOp.AddText(fs3, "\r\n");
}
}