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");

            }
        }