Пример #1
0
        internal ResultWithTotalRows <IEnumerable <ReportMitkanim> > ExcuteReportMitkanim(SearchMitkanimRequest request)
        {
            var orderBy = GenerateSortingFieldAndOrder(request.SortingName, request.SortingOrder);
            var result  = new ResultWithTotalRows <IEnumerable <ReportMitkanim> >();
            var data    = new List <ReportMitkanim>();

            result.IsError = false;
            var connection = GetSqlConnection();

            try
            {
                var command = new SqlCommand(@"gs_searchMitkan", connection);

                command.Parameters.Add("@pOrderBy", orderBy);
                command.Parameters.Add("@pCurrentPage", request.CurrentPage);
                command.Parameters.Add("@pPageSize", request.MaxRowsPerPage);

                command.Parameters.Add("@MahozNum", request.MahozNum);
                command.Parameters.Add("@MitkanNum", request.MitkanNum);
                command.Parameters.Add("@RashutID", request.RashutID);

                command.CommandType = System.Data.CommandType.StoredProcedure;
                connection.Open();
                var drOutput = command.ExecuteReader();

                using (drOutput)
                {
                    while (drOutput.Read())
                    {
                        var item = new ReportMitkanim();
                        item.MahozNum  = (drOutput["MahozNum"] != Convert.DBNull) ? (int?)int.Parse(drOutput["MahozNum"].ToString()) : null;
                        item.MahozName = (drOutput["MahozName"] != Convert.DBNull) ? drOutput["MahozName"].ToString() : null;

                        item.MitkanNum  = (drOutput["MitkanNum"] != Convert.DBNull) ? (int?)int.Parse(drOutput["MitkanNum"].ToString()) : null;
                        item.MitkanName = (drOutput["MitkanName"] != Convert.DBNull) ? drOutput["MitkanName"].ToString() : null;

                        item.RashutID      = (drOutput["RashutID"] != Convert.DBNull) ? (int?)int.Parse(drOutput["RashutID"].ToString()) : null;
                        item.RashutName    = (drOutput["RashutName"] != Convert.DBNull) ? drOutput["RashutName"].ToString() : null;
                        item.MitkanAddress = (drOutput["MitkanAddress"] != Convert.DBNull) ? drOutput["MitkanAddress"].ToString() : null;

                        item.Sum19To65 = (drOutput["Sum19To65"] != Convert.DBNull) ? (int?)int.Parse(drOutput["Sum19To65"].ToString()) : null;
                        item.Sum3      = (drOutput["Sum3"] != Convert.DBNull) ? (int?)int.Parse(drOutput["Sum3"].ToString()) : null;
                        item.Sum4To6   = (drOutput["Sum4To6"] != Convert.DBNull) ? (int?)int.Parse(drOutput["Sum4To6"].ToString()) : null;
                        item.Sum66     = (drOutput["Sum66"] != Convert.DBNull) ? (int?)int.Parse(drOutput["Sum66"].ToString()) : null;
                        item.Sum7To18  = (drOutput["Sum7To18"] != Convert.DBNull) ? (int?)int.Parse(drOutput["Sum7To18"].ToString()) : null;
                        item.SumAll    = (drOutput["SumAll"] != Convert.DBNull) ? (int?)int.Parse(drOutput["SumAll"].ToString()) : null;
                        data.Add(item);
                    }
                    result.Return = data;
                    bool hasNextResult = drOutput.NextResult();
                    if (hasNextResult)
                    {
                        while (drOutput.Read())
                        {
                            result.TotalRows = Convert.ToInt32(drOutput.GetValue(0));
                            break;
                        }
                    }
                }
            }
            catch (Exception ee)
            {
                WriteError(ee.ToString());
                result.IsError   = true;
                result.ErrorDesc = ee.ToString();
            }
            finally
            {
                if (connection != null)
                {
                    connection.Dispose();
                }
            }
            return(result);
        }
Пример #2
0
        internal ResultWithTotalRows <IEnumerable <ShiltonMekomi> > GetShiltonMekomiByModelAndPages(SearchRequest request)
        {
            var orderBy = GenerateSortingFieldAndOrder(request.SortingName, request.SortingOrder);
            var result  = new ResultWithTotalRows <IEnumerable <ShiltonMekomi> >();
            var data    = new List <ShiltonMekomi>();

            result.IsError = false;
            var connection = GetSqlConnection();

            try
            {
                var command = new SqlCommand(@"gs_searchShiltonMekomi", connection);

                command.Parameters.Add("@pOrderBy", orderBy);
                command.Parameters.Add("@pCurrentPage", request.CurrentPage);
                command.Parameters.Add("@pPageSize", request.MaxRowsPerPage);
                command.Parameters.Add("@MfDateIncomeBegin", request.MfDateIncomeBegin);
                command.Parameters.Add("@MfDateIncomeEnd", request.MfDateIncomeEnd);
                command.Parameters.Add("@MehozId", request.MehozId);
                command.Parameters.Add("@MitkanNum", request.MitkanNum);
                command.Parameters.Add("@RashutID", request.RashutID);
                command.Parameters.Add("@MfFirstName", request.MfFirstName);
                command.Parameters.Add("@MfLastName", request.MfLastName);
                command.Parameters.Add("@MfGender", request.MfGender);
                command.Parameters.Add("@MfFather", request.MfFather);
                command.Parameters.Add("@MfAge", request.MfAge);
                command.Parameters.Add("@YeshuvNum", request.YeshuvNum);
                command.Parameters.Add("@StNum", request.StNum);
                command.Parameters.Add("@MfAddHouseNum", request.MfAddHouseNum);
                command.Parameters.Add("@IsMfDateOutcome", request.IsMfDateOutcome);

                command.CommandType = System.Data.CommandType.StoredProcedure;
                connection.Open();
                var drOutput = command.ExecuteReader();

                using (drOutput)
                {
                    while (drOutput.Read())
                    {
                        var item = new ShiltonMekomi();
                        item.MefuneSysNum = (drOutput["MefuneSysNum"] != Convert.DBNull) ? long.Parse(drOutput["MefuneSysNum"].ToString()) : 0;
                        item.MefuneID     = (drOutput["MefuneID"] != Convert.DBNull) ? long.Parse(drOutput["MefuneID"].ToString()) : 0;
                        item.MfLastName   = (drOutput["MfLastName"] != Convert.DBNull) ? drOutput["MfLastName"].ToString() : null;
                        item.MfFirstName  = (drOutput["MfFirstName"] != Convert.DBNull) ? drOutput["MfFirstName"].ToString() : null;

                        // item.ShiltonMekomiAddress = (drOutput["ShiltonMekomiAddress"] != Convert.DBNull) ? drOutput["ShiltonMekomiAddress"].ToString() : null;
                        item.MfAge = (drOutput["MfAge"] != Convert.DBNull) ? (int?)int.Parse(drOutput["MfAge"].ToString()) : null;

                        item.Mitkan               = new Mitkanim();
                        item.Mitkan.MitkanName    = (drOutput["MitkanName"] != Convert.DBNull) ? drOutput["MitkanName"].ToString() : null;
                        item.Mitkan.MitkanPhone   = (drOutput["MitkanPhone"] != Convert.DBNull) ? drOutput["MitkanPhone"].ToString() : null;
                        item.Mitkan.MitkanNum     = (drOutput["MitkanNum"] != Convert.DBNull) ? int.Parse(drOutput["MitkanNum"].ToString()) : 0;
                        item.Mitkan.MitkanAddress = (drOutput["MitkanAddress"] != Convert.DBNull) ? drOutput["MitkanAddress"].ToString() : null;

                        item.Mitkan.Rashut            = new Rashuyot();
                        item.Mitkan.Rashut.RashutID   = (drOutput["RashutID"] != Convert.DBNull) ? int.Parse(drOutput["RashutID"].ToString()) : 0;
                        item.Mitkan.Rashut.RashutName = (drOutput["RashutName"] != Convert.DBNull) ? drOutput["RashutName"].ToString() : null;

                        item.Rehovot        = new Rehovot();
                        item.Rehovot.StName = (drOutput["StName"] != Convert.DBNull) ? drOutput["StName"].ToString() : null;

                        item.Yeshuv            = new Yeshuvim();
                        item.Yeshuv.YeshuvName = (drOutput["YeshuvName"] != Convert.DBNull) ? drOutput["YeshuvName"].ToString() : null;

                        item.MfAddHouseNum = (drOutput["MfAddHouseNum"] != Convert.DBNull) ? (int?)int.Parse(drOutput["MfAddHouseNum"].ToString()) : null;

                        data.Add(item);
                    }
                    result.Return = data;
                    bool hasNextResult = drOutput.NextResult();
                    if (hasNextResult)
                    {
                        while (drOutput.Read())
                        {
                            result.TotalRows = Convert.ToInt32(drOutput.GetValue(0));
                            break;
                        }
                    }
                }
            }
            catch (Exception ee)
            {
                WriteError(ee.ToString());
                result.IsError   = true;
                result.ErrorDesc = ee.ToString();
            }
            finally
            {
                if (connection != null)
                {
                    connection.Dispose();
                }
            }
            return(result);
        }