public bool UpdateVaccinationDeatils(IEnumerable <VaccinationUpdateRequest> vaccList)
        {
            var success = false;

            foreach (var vaccData in vaccList)
            {
                var query         = string.Format("SELECT SchId FROM VaccinationDetails WHERE InfantId={0} AND SchId={1}", vaccData.InfantId, vaccData.ScheduleId);
                var existingSchId = Convert.ToInt32(dbService.ExecuteScalar(query));
                if (existingSchId != 0)
                {
                    query = string.Format("UPDATE VaccinationDetails SET VaccinatedDate='{0}',Comments ={3} WHERE InfantId={1} AND SchId={2}",
                                          vaccData.VaccinatedDate, vaccData.InfantId, vaccData.ScheduleId, vaccData.Comments);
                    dbService.ExecuteScalar(query);
                    success = true;
                }
                else
                {
                    query = string.Format(
                        @"SELECT TOP(1) DATEADD(WEEK, imm.StartOn, inf.DOB) AS NextVaccinationDate FROM InfantDetails inf CROSS APPLY ImmunizationSchedule imm
                        WHERE DATEADD(WEEK, imm.StartOn, inf.DOB) >= GETDATE() AND InfantID = {0}
                    ORDER BY DATEADD(WEEK, imm.StartOn, inf.DOB)", vaccData.InfantId);
                    var nextVaccinationDate = Convert.ToDateTime(dbService.ExecuteScalar(query));

                    query = string.Format(@"INSERT INTO VaccinationDetails(SchId,InfantID,VaccinatedDate,NextVaccinationDate,Comments) output inserted.VaccinationId  
                                          VALUES ({0}, {1}, '{2}', '{3}', '{4}')",
                                          vaccData.ScheduleId, vaccData.InfantId, vaccData.VaccinatedDate, nextVaccinationDate, vaccData.Comments);
                    var vaccinationId = Convert.ToInt32(dbService.ExecuteScalar(query));
                    success = true;
                }
            }

            return(success);
        }
        public FilterResponse GetFilteredGraphData(FilterCriteriaRequest filterCriteria)
        {
            var vaccinationToQuery = string.Empty;
            var districtToQuery    = string.Empty;

            if (filterCriteria.VaccineIdList.Count > 0)
            {
                vaccinationToQuery = "SchId in (" + string.Join(",", filterCriteria.VaccineIdList) + ")";
            }
            if (filterCriteria.DistrictIdList.Count > 0)
            {
                districtToQuery = "DistrictId in (" + string.Join(",", filterCriteria.DistrictIdList) + ")";
            }

            FilterResponse filterres = new FilterResponse();

            if (filterCriteria.StatusList.Count > 0)
            {
                foreach (int status in filterCriteria.StatusList)
                {
                    if (status == 0)//Done
                    {
                        var query = string.Format(@"SELECT Count(*) FROM ListOfSuccessfullVacc WHERE VaccinatedDate>='{0}' AND VaccinatedDate<='{1}' AND {2} AND {3}", filterCriteria.FromYear, filterCriteria.ToYear, districtToQuery, vaccinationToQuery);
                        filterres.DoneCount = Convert.ToInt32(dbService.ExecuteScalar(query));
                    }
                    else if (status == 1)//Due
                    {
                        var query = string.Format(@"SELECT Count(*) FROM ListOfVaccinationDue WHERE VAccStartOn>='{0}' AND VAccStartOn<='{1}'AND {2} AND {3} ", filterCriteria.FromYear, filterCriteria.ToYear, districtToQuery, vaccinationToQuery);
                        filterres.DueCount = Convert.ToInt32(dbService.ExecuteScalar(query));
                    }
                    else if (status == 2)//OverDue/Missed
                    {
                        var query = string.Format(@"SELECT Count(*) FROM ListOfVaccinationOverDue WHERE VAccStartOn>='{0}' AND VAccStartOn<='{1}' AND {2} AND {3}", filterCriteria.FromYear, filterCriteria.ToYear, districtToQuery, vaccinationToQuery);
                        filterres.MissedCount = Convert.ToInt32(dbService.ExecuteScalar(query));
                    }
                }
            }

            //int Totalstatuscount = filterres.DueCount + filterres.DoneCount + filterres.MissedCount;
            //filterres.DoneCount = (filterres.DoneCount / Totalstatuscount) * 100;
            //filterres.DueCount = (filterres.DueCount / Totalstatuscount) * 100;
            //filterres.MissedCount = (filterres.MissedCount / Totalstatuscount) * 100;

            return(filterres);
        }
        public int RegisterUser(RegistrationRequest registerData)
        {
            var userId         = 0;
            var query          = string.Format("select userId from UserDetails where Mobile = '{1}' or UserName = '******'", registerData.Email, registerData.Mobile, registerData.UserName);
            var existingUserId = Convert.ToInt32(dbService.ExecuteScalar(query));

            if (existingUserId > 0)
            {
                userId = -1;
            }
            else
            {
                query = string.Format(@"insert into UserDetails(FullName,Address,PINCode,Mobile,Email,UserName,Password,UserType,
                        RegistrationID,RegisteredPHC,EmailNotification,SMSNotification) output inserted.UserID  values 
                        ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', {7}, '{8}', '{9}', {10}, {11})",
                                      registerData.FullName, registerData.Address, registerData.PinCode, registerData.Mobile, registerData.Email,
                                      registerData.UserName, registerData.Password, registerData.UserType, registerData.RegistrationId, registerData.RegisteredPHC, Convert.ToInt32(registerData.EmailNotification), Convert.ToInt32(registerData.SMSNotification));
                userId = Convert.ToInt32(dbService.ExecuteScalar(query));
            }
            return(userId);
        }