/// <summary>
        /// 添加一个项目 
        /// </summary>
        /// <param name="project"></param>
        public void AddProject(Project project)
        {
            try
            {
                string commandString = "INSERT INTO usta_Project(name, categoryId, userName, userNo, memo, createdTime) VALUES(@name, @categoryId, @userName, @userNo, @memo, @createdTime)";

                SqlParameter[] parameters = new SqlParameter[]{
                new SqlParameter("@name", project.name),
                new SqlParameter("@categoryId", project.category.id),
                new SqlParameter("@userName", project.userName),
                new SqlParameter("@userNo", project.userNo),
                new SqlParameter("@memo", project.memo),
                new SqlParameter("@createdTime", DateTime.Now)
            };

                SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandString, parameters);
            }
            catch (Exception ex)
            {
                MongoDBLog.LogRecord(ex);
                CommonUtility.RedirectUrl();
            }
            finally
            {
                conn.Close();
            }
        }
        private void BuildReimItems(IDataReader reader, List<ReimItem> reimItems)
        {
            ReimItem reimItem;
            while (reader.Read())
            {
                reimItem = new ReimItem();
                reimItem.id = int.Parse(reader["id"].ToString().Trim());
                Project project = new Project()
                {
                    id = int.Parse(reader["projectId"].ToString().Trim()),
                    name = reader["projectName"].ToString().Trim()
                };

                reimItem.project = project;
                Reim reim = new Reim()
                {
                    id = int.Parse(reader["reimId"].ToString().Trim()),
                    name = reader["reimName"].ToString().Trim()
                };

                reimItem.reim = reim;
                reimItem.value = CommonUtility.ConvertFormatedFloat("{0:0.00}", reader["value"].ToString().Trim());
                reimItem.memo = reader["memo"].ToString().Trim();
                reimItem.createdTime = DateTime.Parse(reader["createdTime"].ToString().Trim());

                reimItems.Add(reimItem);
            }
        }
        /// <summary>
        /// 根据项目名称或id和报销项查询报销记录
        /// </summary>
        /// <param name="projectName"></param>
        /// <param name="projectId"></param>
        /// <param name="reimId"></param>
        /// <returns></returns>
        public List<ReimItem> GetReimItemSummaryValues(string projectName, int projectId, int reimId, string userNo)
        {
            List<ReimItem> reimItems = new List<ReimItem>();

            try
            {
                string projectCase = null;
                string reimCase = null;
                string userCase = null;

                List<SqlParameter> parameterList = new List<SqlParameter>();

                if (reimId != 0)
                {
                    reimCase = " reimId = @reimId ";
                    parameterList.Add(new SqlParameter("@reimId", reimId));
                }

                if (!string.IsNullOrWhiteSpace(userNo))
                {
                    userCase = " usta_Project.userNo = @userNo";
                    parameterList.Add(new SqlParameter("@userNo", userNo));
                }

                string commandString;
                if (projectId != 0)
                {

                    projectCase = " usta_ReimItem.projectId = @projectId ";
                    commandString = "SELECT usta_Project.id AS projectId,"
                                    + "usta_Project.name AS projectName,"
                                    + "usta_Reim.id AS reimId,"
                                    + "usta_Reim.name AS reimName,"
                                    + "temp_ReimItem.value AS value "
                                + " FROM ( SELECT usta_ReimItem.projectId AS projectId,"
                                            + " usta_ReimItem.reimId AS reimId, SUM(usta_ReimItem.value) AS value "
                                            + " FROM usta_ReimItem "
                                            + " WHERE " + projectCase
                                            + (reimCase == null ? "" : (" AND " + reimCase))
                                            + " GROUP BY usta_ReimItem.projectId, usta_ReimItem.reimId) "
                                + " AS temp_ReimItem, usta_Project, usta_Reim"
                                + " WHERE temp_ReimItem.projectId = usta_Project.id "
                                + " AND temp_ReimItem.reimId = usta_Reim.id";

                    parameterList.Add(new SqlParameter("@projectId", projectId));

                }
                else if (!string.IsNullOrWhiteSpace(projectName))
                {
                    commandString = "SELECT usta_Project.id AS projectId,"
                                        + "usta_Project.name AS projectName,"
                                        + "usta_Reim.id AS reimId,"
                                        + "usta_Reim.name AS reimName,"
                                        + "temp_ReimItem.value AS value "
                                    + "FROM (SELECT usta_ReimItem.projectId AS projectId,"
                                                + " usta_ReimItem.reimId AS reimId, SUM(usta_ReimItem.value) AS value "
                                                + " FROM usta_ReimItem "
                                                + (reimCase == null ? "" : (" WHERE " + reimCase))
                                                + " GROUP BY usta_ReimItem.projectId, usta_ReimItem.reimId) "
                                    + " AS temp_ReimItem, usta_Project, usta_Reim"
                                    + " WHERE temp_ReimItem.projectId = usta_Project.id "
                                    + " AND temp_ReimItem.reimId = usta_Reim.id AND usta_Project.name LIKE '%" + projectName.Trim() + "%'";
                }
                else
                {

                    commandString = "SELECT usta_Project.id AS projectId,"
                                        + "usta_Project.name AS projectName,"
                                        + "usta_Reim.id AS reimId,"
                                        + "usta_Reim.name AS reimName,"
                                        + "temp_ReimItem.value AS value "
                                    + "FROM ( SELECT usta_ReimItem.projectId AS projectId,"
                                                + " usta_ReimItem.reimId AS reimId, SUM(usta_ReimItem.value) AS value "
                                                + " FROM usta_ReimItem "
                                                + (reimCase == null ? "" : (" WHERE " + reimCase))
                                                + " GROUP BY usta_ReimItem.projectId, usta_ReimItem.reimId) "
                                    + " AS temp_ReimItem, usta_Project, usta_Reim"
                                    + " WHERE temp_ReimItem.projectId = usta_Project.id "
                                    + " AND temp_ReimItem.reimId = usta_Reim.id";

                }

                if (userCase != null)
                {
                    commandString += (" AND " + userCase);
                }

                IDataReader reader = SqlHelper.ExecuteReader(conn, CommandType.Text, commandString, parameterList.ToArray());

                while (reader.Read())
                {
                    ReimItem reimItem = new ReimItem();
                    Project project = new Project()
                    {
                        id = int.Parse(reader["projectId"].ToString().Trim()),
                        name = reader["projectName"].ToString().Trim()
                    };
                    reimItem.project = project;

                    Reim reim = new Reim()
                    {
                        id = int.Parse(reader["reimId"].ToString().Trim()),
                        name = reader["reimName"].ToString().Trim()
                    };

                    reimItem.reim = reim;

                    reimItem.value = CommonUtility.ConvertFormatedFloat("{0:F2}", reader["value"].ToString().Trim());

                    reimItems.Add(reimItem);

                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MongoDBLog.LogRecord(ex);
                CommonUtility.RedirectUrl();
            }
            finally
            {
                conn.Close();
            }
            return reimItems;
        }
        /// <summary>
        /// 查询所有的报销条目:同一项目的相同报销项目金额进行合并
        /// 如果需要查询同一项目的相同报销项目的不同报销时间的记录,
        /// 请使用GetAllDistinctReimItems()
        /// </summary>
        /// <returns></returns>
        public List<ReimItem> GetAllReimItems()
        {
            List<ReimItem> reimItems = new List<ReimItem>();
            try
            {
                string commandString = "SELECT usta_Project.id AS projectId,"
                                            + "usta_Project.name AS projectName,"
                                            + "usta_Reim.id AS reimId,"
                                            + "usta_Reim.name AS reimName,"
                                            + "temp_ReimItem.value AS value "
                                        + "FROM ("
                                            + "SELECT usta_ReimItem.projectId AS projectId, "
                                                + "usta_ReimItem.reimId AS reimId, "
                                                + "SUM(usta_ReimItem.value) AS value "
                                            + "FROM usta_ReimItem "
                                            + "GROUP BY usta_ReimItem.projectId, usta_ReimItem.reimId) "
                                        + "AS temp_ReimItem, usta_Project, usta_Reim "
                                        + "WHERE temp_ReimItem.projectId = usta_Project.id "
                                        + "AND temp_ReimItem.reimId = usta_Reim.id";
                IDataReader reader = SqlHelper.ExecuteReader(conn, CommandType.Text, commandString);

                while (reader.Read())
                {
                    ReimItem reimItem = new ReimItem();
                    Project project = new Project()
                    {
                        id = int.Parse(reader["projectId"].ToString().Trim()),
                        name = reader["projectName"].ToString().Trim()
                    };
                    reimItem.project = project;

                    Reim reim = new Reim()
                    {
                        id = int.Parse(reader["reimId"].ToString().Trim()),
                        name = reader["reimName"].ToString().Trim()
                    };

                    reimItem.reim = reim;

                    reimItem.value = CommonUtility.ConvertFormatedFloat("{0:F2}", reader["value"].ToString().Trim());
                    reimItems.Add(reimItem);

                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MongoDBLog.LogRecord(ex);
                CommonUtility.RedirectUrl();
            }
            finally
            {
                conn.Close();
            }
            return reimItems;
        }
 private void BuildProjects(IDataReader reader, List<Project> projects)
 {
     Project project;
     while (reader.Read())
     {
         project = new Project();
         project.id = int.Parse(reader["id"].ToString().Trim());
         project.name = reader["name"].ToString().Trim();
         ProjectCategory category = new ProjectCategory();
         category.id = int.Parse(reader["categoryId"].ToString().Trim());
         category.name = reader["categoryName"].ToString().Trim();
         project.category = category;
         project.userName = reader["userName"].ToString().Trim();
         project.userNo = reader["userNo"].ToString().Trim();
         project.memo = reader["memo"].ToString().Trim();
         project.createdTime = DateTime.Parse(reader["createdTime"].ToString().Trim());
         projects.Add(project);
     }
 }
        /// <summary>
        /// 更新一个项目
        /// </summary>
        /// <param name="project"></param>
        public void UpdateProject(Project project)
        {
            try
            {
                string commandString = "UPDATE usta_Project SET name = @name, categoryId = @categoryId, userName = @userName, userNo = @userNo, memo = @memo WHERE id = @projectId";

                SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@name", project.name),
                new SqlParameter("@categoryId", project.category.id),
                new SqlParameter("@userName", project.userName),
                new SqlParameter("@userNo", project.userNo),
                new SqlParameter("@memo", project.memo),
                new SqlParameter("@projectId", project.id)

            };

                SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandString, parameters);
            }
            catch (Exception ex)
            {
                MongoDBLog.LogRecord(ex);
                CommonUtility.RedirectUrl();
            }
            finally
            {
                conn.Close();
            }
        }