Exemplo n.º 1
0
        public string RolesRetrieve(RolesRetrieveReq req)
        {
            RolesRetrieveRes res = new RolesRetrieveRes();

            if (Session["ID"] == null)
            {
                res.ReturnStatus = new ReturnStatus(ReturnCode.SESSION_TIMEOUT);
            }
            else
            {
                try
                {
                    Log("Req=" + JsonConvert.SerializeObject(req));
                    res = new Roles("SCC").PaginationRetrieve(req);
                    res.ReturnStatus = new ReturnStatus(ReturnCode.SUCCESS);
                }
                catch (Exception ex)
                {
                    Log("Err=" + ex.Message);
                    Log(ex.StackTrace);
                    res.ReturnStatus = new ReturnStatus(ReturnCode.SERIOUS_ERROR);
                }
            }
            var json = JsonConvert.SerializeObject(res);

            Log("Res=" + json);
            return(json);
        }
Exemplo n.º 2
0
        public RolesRetrieveRes ReportData(RolesRetrieveReq req)
        {
            RolesRetrieveRes res = new RolesRetrieveRes()
            {
                ROLES      = new List <ROLES>(),
                Pagination = new Pagination
                {
                    PageCount  = 0,
                    RowCount   = 0,
                    PageNumber = 0,
                    MinNumber  = 0,
                    MaxNumber  = 0,
                    StartTime  = DateTime.Now
                }
            };

            using (DbCommand cmd = Db.CreateConnection().CreateCommand())
            {
                string sql = @"SELECT TOP(@TOP)
SN,NAME,MODE,CDATE,CUSER,MDATE,MUSER,
    FROM ROLES{0}
    ORDER BY SN DESC;";
                string where = "";
                Db.AddInParameter(cmd, "TOP", DbType.Int32, 1000000);

                //if (!string.IsNullOrEmpty(req.ROLES.SN))
                //{
                //    where += " AND SN=@SN";
                //    Db.AddInParameter(cmd, "SN", DbType.String, req.ROLES.SN);
                //}
                if (!string.IsNullOrEmpty(req.ROLES.NAME))
                {
                    where += " AND NAME=@NAME";
                    Db.AddInParameter(cmd, "NAME", DbType.String, req.ROLES.NAME);
                }
                if (!string.IsNullOrEmpty(req.ROLES.MODE))
                {
                    where += " AND MODE=@MODE";
                    Db.AddInParameter(cmd, "MODE", DbType.String, req.ROLES.MODE);
                }
                if (where.Length > 0)
                {
                    where = " WHERE" + where.Substring(4);
                }

                sql             = String.Format(sql, where);
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                using (IDataReader reader = Db.ExecuteReader(cmd))
                {
                    while (reader.Read())
                    {
                        var row = new ROLES
                        {
                            SN    = reader["SN"] as Int32? ?? null,
                            NAME  = reader["NAME"] as string,
                            MODE  = reader["MODE"] as string,
                            CDATE = reader["CDATE"] as DateTime?,
                            CUSER = reader["CUSER"] as string,
                            MDATE = reader["MDATE"] as DateTime?,
                            MUSER = reader["MUSER"] as string
                        };
                        res.ROLES.Add(row);
                    }
                }
            }
            res.Pagination.EndTime = DateTime.Now;

            return(res);
        }
Exemplo n.º 3
0
        public RolesRetrieveRes PaginationRetrieve(RolesRetrieveReq req)
        {
            RolesRetrieveRes res = new RolesRetrieveRes()
            {
                ROLES      = new List <ROLES>(),
                Pagination = new Pagination
                {
                    PageCount  = 0,
                    RowCount   = 0,
                    PageNumber = 0,
                    MinNumber  = 0,
                    MaxNumber  = 0,
                    StartTime  = DateTime.Now
                }
            };

            using (DbCommand cmd = Db.CreateConnection().CreateCommand())
            {
                string sql = @"SELECT COUNT(1) FROM (SELECT TOP(@TOP) NULL AS N FROM ROLES{0}) A;
SELECT TOP(@TOP) SN,NAME,MODE,CDATE,CUSER,MDATE,MUSER
    FROM ROLES{0}
    ORDER BY SN DESC;";
                string where = "";
                Db.AddInParameter(cmd, "TOP", DbType.Int32, 1000);

                if (req.ROLES.SN != null)
                {
                    where += " AND SN=@SN";
                    Db.AddInParameter(cmd, "SN", DbType.Int16, req.ROLES.SN);
                }
                if (!string.IsNullOrEmpty(req.ROLES.NAME))
                {
                    where += " AND NAME LIKE @NAME";
                    Db.AddInParameter(cmd, "NAME", DbType.String, "%" + req.ROLES.NAME + "%");
                }
                if (!string.IsNullOrEmpty(req.ROLES.MODE))
                {
                    where += " AND MODE=@MODE";
                    Db.AddInParameter(cmd, "MODE", DbType.String, req.ROLES.MODE);
                }
                if (where.Length > 0)
                {
                    where = " WHERE" + where.Substring(4);
                }

                sql             = String.Format(sql, where);
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                using (IDataReader reader = Db.ExecuteReader(cmd))
                {
                    reader.Read();
                    int.TryParse(reader[0].ToString(), out res.Pagination.RowCount);
                    if (res.Pagination.RowCount > 0)
                    {
                        reader.NextResult();

                        res.Pagination.PageCount  = Convert.ToInt32(Math.Ceiling(1.0 * res.Pagination.RowCount / req.PageSize));
                        res.Pagination.PageNumber = req.PageNumber < 1 ? 1 : req.PageNumber;
                        res.Pagination.PageNumber = req.PageNumber > res.Pagination.PageCount ? res.Pagination.PageCount : res.Pagination.PageNumber;
                        res.Pagination.MinNumber  = (res.Pagination.PageNumber - 1) * req.PageSize + 1;
                        res.Pagination.MaxNumber  = res.Pagination.PageNumber * req.PageSize;
                        res.Pagination.MaxNumber  = res.Pagination.MaxNumber > res.Pagination.RowCount ? res.Pagination.RowCount : res.Pagination.MaxNumber;

                        int i = 0;
                        while (reader.Read())
                        {
                            i++;
                            if (i >= res.Pagination.MinNumber && i <= res.Pagination.MaxNumber)
                            {
                                var row = new ROLES
                                {
                                    SN    = reader["SN"] as Int32? ?? null,
                                    NAME  = reader["NAME"] as string,
                                    MODE  = reader["MODE"] as string,
                                    CDATE = reader["CDATE"] as DateTime?,
                                    CUSER = reader["CUSER"] as string,
                                    MDATE = reader["MDATE"] as DateTime?,
                                    MUSER = reader["MUSER"] as string
                                };
                                res.ROLES.Add(row);
                            }
                            else if (i > res.Pagination.MaxNumber)
                            {
                                reader.Close();
                                break;
                            }
                        }
                    }
                }
            }
            res.Pagination.EndTime = DateTime.Now;

            return(res);
        }
Exemplo n.º 4
0
        public ActionResult RolesExcel()
        {
            RolesRetrieveReq req  = null;
            RequestParameter para = new RequestParameter();

            para.Load(Request);
            req = new RolesRetrieveReq();
            JsonConvert.PopulateObject(para.Item("json"), req);

            var memoryStream = new MemoryStream();

            using (var document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookpart = document.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();
                WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();

                #region Sheet1
                SheetData sheetData = new SheetData();
                Row       row;

                #region header
                row = new Row();    //header
                row.Append(
                    new Cell()
                {
                    CellValue = new CellValue("序號")
                },
                    new Cell()
                {
                    CellValue = new CellValue("群組名稱")
                },
                    new Cell()
                {
                    CellValue = new CellValue("狀態")
                },
                    new Cell()
                {
                    CellValue = new CellValue("建檔時間")
                },
                    new Cell()
                {
                    CellValue = new CellValue("建檔人員")
                },
                    new Cell()
                {
                    CellValue = new CellValue("異動時間")
                },
                    new Cell()
                {
                    CellValue = new CellValue("異動人員")
                }
                    );
                sheetData.AppendChild(row);
                #endregion

                #region data
                RolesRetrieveRes res = new Roles("KYL").ReportData(req);
                foreach (ROLES data in res.ROLES) //data
                {
                    row = new Row();
                    row.Append(
                        new Cell()
                    {
                        CellValue = new CellValue(data.SN.ToString())
                    },
                        new Cell()
                    {
                        CellValue = new CellValue(data.NAME.ToString())
                    },
                        new Cell()
                    {
                        CellValue = new CellValue(data.MODE.ToString())
                    },
                        new Cell()
                    {
                        CellValue = new CellValue(data.CDATE.ToString())
                    },
                        new Cell()
                    {
                        CellValue = new CellValue(data.CUSER.ToString())
                    },
                        new Cell()
                    {
                        CellValue = new CellValue(data.MDATE.ToString())
                    },
                        new Cell()
                    {
                        CellValue = new CellValue(data.MUSER.ToString())
                    }
                        );
                    sheetData.AppendChild(row);
                }
                #endregion


                Worksheet worksheet = new Worksheet();
                worksheet.Append(sheetData);
                worksheetPart.Worksheet = worksheet;    //add a Worksheet to the WorksheetPart

                Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());
                sheets.AppendChild(new Sheet()
                {
                    Id      = document.WorkbookPart.GetIdOfPart(document.WorkbookPart.WorksheetParts.First()),
                    SheetId = 1,
                    Name    = "工作表1"
                });
                #endregion
            }
            memoryStream.Seek(0, SeekOrigin.Begin);

            return(File(memoryStream.ToArray(), "application/vnd.ms-excel", "TEST.xlsx"));
        }