public async Task <object> GetPageByProcList([FromBody] CTCApproval Model)
        {
            Tuple <List <POS_Master>, int> tp = await _ctcapprovalRepository.GetPageByProcList(Model);

            var meta = new
            {
                pageTotal = tp.Item2
            };

            Response.Headers.Add("Access-Control-Expose-Headers", "X-Pagination");
            Response.Headers.Add("X-Pagination", JsonConvert.SerializeObject(meta));
            return(Ok(tp.Item1.Select(posmaster => new
            {
                POS_CODE = posmaster.POS_CODE,
                POS_NAME = posmaster.POS_NAME,
                CUSTOMER_GROUP = posmaster.CUSTOMER_GROUP,
                BRANCH_EN = posmaster.BRANCH_EN,
                POS_CHANNEL = posmaster.POS_CHANNEL,
                SEGMENTATION = posmaster.Seg_Marketplace_recommended,
                OCS_GRADING = posmaster.OCS_Grading_recommended,
                POS_TYPE = posmaster.POS_Type_recommended
            }
                                      )));
        }
        public async Task <object> GetExportList([FromBody] CTCApproval Model)
        {
            string            roleid     = HttpContext.User.Claims.Where(item => item.Type == "RoleID").FirstOrDefault().Value;
            List <ColumnAuth> listColumn = await _IColumnAuthRepository.GetColumnAuths(roleid, "CTCApproval");

            var NoEdit    = new List <int>();
            var columnMap = new Dictionary <string, string>();

            for (int i = 0; i < listColumn.Count(); i++)
            {
                columnMap.Add(listColumn[i].Database_Column.ToString(), listColumn[i].Excel_Column.ToString());
                if (listColumn[i].CanEdit.ToUpper() == "N")
                {
                    NoEdit.Add(i + 1);
                }
            }
            List <POS_Master> list = await _ctcapprovalRepository.GetExportList(Model);

            var fs = ExcelHelper.GetByteToExportExcel <POS_Master>(list, columnMap, new List <string>(), NoEdit, "Sheet1");

            Response.Headers.Add("Access-Control-Expose-Headers", "Filename");
            Response.Headers.Add("Filename", "PosType.xlsx");
            return(File(fs, "application/octet-stream", $"excel导出测试{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"));
        }
Example #3
0
        public async Task <List <POS_Master> > GetExportList(CTCApproval Model)
        {
            string            sqlText = "select * from SIDE_PLM_V_POS_Master where 1=1";
            DynamicParameters parm    = new DynamicParameters();

            if (!string.IsNullOrEmpty(Model.Year))
            {
                sqlText += " and TM_YEAR = @Year";
                parm.Add("Year", Model.Year);
            }
            if (!string.IsNullOrEmpty(Model.Season))
            {
                sqlText += " and TM_SEASON = @Season";
                parm.Add("Season", Model.Season);
            }
            if (!string.IsNullOrEmpty(Model.Group))
            {
                sqlText += " and CUSTOMER_GROUP = @Group";
                parm.Add("Group", Model.Group);
            }
            if (!string.IsNullOrEmpty(Model.Branch))
            {
                sqlText += " and BRANCH_EN = @Branch";
                parm.Add("Branch", Model.Branch);
            }
            if (!string.IsNullOrEmpty(Model.Channel))
            {
                sqlText += " and POS_CHANNEL = @Channel";
                parm.Add("Channel", Model.Channel);
            }
            if (!string.IsNullOrEmpty(Model.City))
            {
                sqlText += " and City_CN LIKE @City";
                parm.Add("City", "%" + Model.City + "%");
            }
            if (!string.IsNullOrEmpty(Model.Province))
            {
                sqlText += " and PROVINCE_CN LIKE @Province";
                parm.Add("Province", "%" + Model.Province + "%");
            }
            if (!string.IsNullOrEmpty(Model.Sellspacebegin) && string.IsNullOrEmpty(Model.Sellspaceend))
            {
                sqlText += " and SELLING_SPACE >= @Sellspacebegin";
                parm.Add("Sellspacebegin", Model.Sellspacebegin);
            }
            if (!string.IsNullOrEmpty(Model.Sellspacebegin) && string.IsNullOrEmpty(Model.Sellspaceend))
            {
                sqlText += " and SELLING_SPACE <= @Sellspaceend";
                parm.Add("Sellspaceend", Model.Sellspaceend);
            }
            if (!string.IsNullOrEmpty(Model.Province) && !string.IsNullOrEmpty(Model.Sellspaceend))
            {
                sqlText += " and SELLING_SPACE between @Sellspacebegin and @Sellspaceend";
                parm.Add("Sellspacebegin", Model.Sellspacebegin);
                parm.Add("Sellspaceend", Model.Sellspaceend);
            }
            if (!string.IsNullOrEmpty(Model.Range))
            {
                sqlText += " and (POS_Type is NULL OR POS_Type in (select POS_TYPE from SIDE_MD_POS_Type WHERE Type_Status=0) )";
            }

            using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
            {
                var list = await conn.QueryAsync <POS_Master>(sqlText, parm);

                return(list.ToList());
            }
        }
Example #4
0
        //public async Task<Tuple<List<POS_Master>, int>> GetPageByProcList(string viewName, string fieldName = "*", string wherestring = " 1=1", string orderString = "ID", int page = 1, int pageSize = 10)
        //{
        //    int recordTotal = 0;

        //    using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
        //    {
        //        //conn.Open();
        //        DynamicParameters parm = new DynamicParameters();
        //        parm.Add("viewName", viewName);
        //        parm.Add("fieldName", fieldName);
        //        parm.Add("whereString", wherestring);
        //        parm.Add("pageSize", pageSize);
        //        parm.Add("pageNo", page);
        //        parm.Add("orderString", orderString);
        //        parm.Add("recordTotal", 0, DbType.Int32, ParameterDirection.Output);

        //        var list = await Task.Run(() => conn.Query<POS_Master>("SIDE_PLM_ProcViewPager", parm, commandType: CommandType.StoredProcedure).ToList());
        //        recordTotal = parm.Get<int>("@recordTotal");//返回总页数

        //        //  conn.Close();
        //        return new Tuple<List<POS_Master>, int>(list, recordTotal);

        //    }
        //}

        public async Task <Tuple <List <POS_Master>, int> > GetPageByProcList(CTCApproval Model)
        {
            string            sqlText = "select * from SIDE_PLM_V_POS_Master where 1=1";
            DynamicParameters parm    = new DynamicParameters();

            if (!string.IsNullOrEmpty(Model.Year))
            {
                sqlText += " and TM_YEAR = @Year";
                parm.Add("Year", Model.Year);
            }
            if (!string.IsNullOrEmpty(Model.Season))
            {
                sqlText += " and TM_SEASON = @Season";
                parm.Add("Season", Model.Season);
            }
            if (!string.IsNullOrEmpty(Model.Group))
            {
                sqlText += " and CUSTOMER_GROUP = @Group";
                parm.Add("Group", Model.Group);
            }
            if (!string.IsNullOrEmpty(Model.Branch))
            {
                sqlText += " and BRANCH_EN = @Branch";
                parm.Add("Branch", Model.Branch);
            }
            if (!string.IsNullOrEmpty(Model.Channel))
            {
                sqlText += " and POS_CHANNEL = @Channel";
                parm.Add("Channel", Model.Channel);
            }
            if (!string.IsNullOrEmpty(Model.City))
            {
                sqlText += " and City_CN LIKE @City";
                parm.Add("City", "%" + Model.City + "%");
            }
            if (!string.IsNullOrEmpty(Model.Province))
            {
                sqlText += " and PROVINCE_CN LIKE @Province";
                parm.Add("Province", "%" + Model.Province + "%");
            }
            if (!string.IsNullOrEmpty(Model.Sellspacebegin) && string.IsNullOrEmpty(Model.Sellspaceend))
            {
                sqlText += " and SELLING_SPACE >= @Sellspacebegin";
                parm.Add("Sellspacebegin", Model.Sellspacebegin);
            }
            if (string.IsNullOrEmpty(Model.Sellspacebegin) && !string.IsNullOrEmpty(Model.Sellspaceend))
            {
                sqlText += " and SELLING_SPACE <= @Sellspaceend";
                parm.Add("Sellspaceend", Model.Sellspaceend);
            }
            if (!string.IsNullOrEmpty(Model.Province) && !string.IsNullOrEmpty(Model.Sellspaceend))
            {
                sqlText += " and SELLING_SPACE between @Sellspacebegin and @Sellspaceend";
                parm.Add("Sellspacebegin", Model.Sellspacebegin);
                parm.Add("Sellspaceend", Model.Sellspaceend);
            }
            if (!string.IsNullOrEmpty(Model.Range))
            {
                sqlText += " and (POS_Type is NULL OR POS_Type in (select POS_TYPE from SIDE_MD_POS_Type WHERE Type_Status=0) )";
            }

            parm.Add("PageIndex", Model.PageIndex);
            parm.Add("PageSize", Model.PageSize);

            string selectQuery = @" ;WITH _data AS (" + sqlText + @"                    
                    ),
                      _count AS (
                        SELECT COUNT(1) AS TotalCount FROM _data
                    )
                    SELECT * FROM _data CROSS APPLY _count Order by ID OFFSET @PageIndex * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;
                    ";
            string sqlText2    = sqlText.Replace("*", "COUNT(*)");

            selectQuery += sqlText2;
            using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
            {
                var list = await conn.QueryMultipleAsync(selectQuery, parm);

                var i = list.Read <POS_Master>();
                var s = list.Read <int>().Single();
                Tuple <List <POS_Master>, int> tuple = new Tuple <List <POS_Master>, int>(i.ToList(), s);
                return(tuple);
            }
        }