/// <summary>
        /// 添加一个报销条目
        /// </summary>
        /// <param name="reimItem"></param>
        public void AddReimItem(ReimItem reimItem)
        {
            try
            {
                string commandString = "INSERT INTO usta_ReimItem (projectId, reimId, value, memo, createdTime) VALUES(@projectId, @reimId, @value, @memo, @createdTime)";

                SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@projectId", reimItem.project.id),
                new SqlParameter("@reimId", reimItem.reim.id),
                new SqlParameter("@value", reimItem.value),
                new SqlParameter("@memo", reimItem.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>
        /// 更新一个报销条目
        /// </summary>
        /// <param name="reimItem"></param>
        public void UpdateReimItem(ReimItem reimItem)
        {
            try
            {
                string commandString = "UPDATE usta_ReimItem SET projectId = @projectId, reimId = @reimId, value = @value, memo = @memo WHERE id = @id";
                SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@projectId", reimItem.project.id),
                new SqlParameter("@reimId", reimItem.reim.id),
                new SqlParameter("@value", reimItem.value),
                new SqlParameter("@memo", reimItem.memo),
                new SqlParameter("@id", reimItem.id)
            };

                SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandString, parameters);
            }
            catch (Exception ex)
            {
                MongoDBLog.LogRecord(ex);
                CommonUtility.RedirectUrl();
            }
            finally
            {
                conn.Close();
            }
        }
        /// <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>
        /// 查询某个项目指定报销项的报销记录
        /// </summary>
        /// <param name="projectId"></param>
        /// <param name="reimId"></param>
        /// <returns></returns>
        public List<ReimItem> GetReimItemsForProjectAndReim(int projectId, int reimId)
        {
            List<ReimItem> reimItems = new List<ReimItem>();
            try
            {
                string commandString = "SELECT * FROM usta_ReimItem WHERE usta_ReimItem.projectId = @projectId AND usta_ReimItem.reimId = @reimId";

                SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@projectId", projectId),
                new SqlParameter("@reimId", reimId)
            };

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

                ReimItem reimItem;
                while (reader.Read())
                {
                    reimItem = new ReimItem();
                    reimItem.id = int.Parse(reader["id"].ToString().Trim());
                    reimItem.value = CommonUtility.ConvertFormatedFloat("{0:0.00}", reader["value"].ToString().Trim());
                    reimItem.createdTime = DateTime.Parse(reader["createdTime"].ToString().Trim());
                    reimItem.memo = reader["memo"].ToString().Trim();
                    reimItems.Add(reimItem);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MongoDBLog.LogRecord(ex);
                CommonUtility.RedirectUrl();
            }
            finally
            {
                conn.Close();
            }
            return reimItems;
        }
        /// <summary>
        /// 根据项目名称或id查询该项目的报销总金额
        /// </summary>
        /// <param name="projectName"></param>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public List<ReimItem> GetProjectReimItems(string projectName, int projectId)
        {
            List<ReimItem> reimItems = new List<ReimItem>();
            try
            {
                string commandString;
                if (projectId != 0)
                {

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

                }

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

                ReimItem reimItem;
                while (reader.Read())
                {
                    reimItem = new ReimItem();
                    reimItem.project = new Project()
                    {
                        id = int.Parse(reader["projectId"].ToString().Trim()),
                        name = reader["projectName"].ToString().Trim()
                    };
                    reimItem.value = CommonUtility.ConvertFormatedFloat("{0:0.00}", 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;
        }
Esempio n. 8
0
        protected void ReimEntry_Click(object sender, EventArgs e)
        {
            string reimIdString = this.ddl_ReimList.SelectedValue;
            if (reimIdString == null || reimIdString == "0")
            {
                Javascript.Alert("您未指定报销项,请核对后再完成此操作!", Page);
            }
            else
            {
                int projectId = int.Parse(this.hf_ProjectId.Value);
                int reimId = int.Parse(reimIdString);
                float reimingValue = CommonUtility.ConvertFormatedFloat("{0:F2}", this.tb_ReimValue.Text.Trim());
                DalOperationAboutReimItem dalReimItem = new DalOperationAboutReimItem();
                DalOperationAboutProjectReimRule dalRule = new DalOperationAboutProjectReimRule();

                float reimedValue = dalReimItem.GetReimItemValue(projectId, reimId);

                ProjectReimRule projectRule = dalRule.GetProjectReimRule(projectId, reimId);

                bool isError = false;
                if (projectRule != null)
                {
                    if (reimingValue > projectRule.reimValue)
                    {
                        isError = true;
                        Javascript.Alert("该项目选择的报销项单次最大金额不超过" + projectRule.reimValue+ "元", Page);
                    }
                    else if (reimingValue + reimedValue > projectRule.maxReimValue)
                    {
                        isError = true;
                        Javascript.Alert("该项目选择的报销项最大报销总金额不超过" + projectRule.maxReimValue + "元,当前您已报销了" + reimedValue + "元", Page);
                    }
                }

                if (!isError) {
                    ReimItem reimItem = new ReimItem()
                    {
                        project = new Project() { id = projectId },
                        reim = new Reim() { id = reimId },
                        value = reimingValue,
                        memo = this.ReimEntryMemo.Text.Trim()
                    };

                    dalReimItem.AddReimItem(reimItem);

                    Javascript.Alert("操作成功!", Page);
                    Javascript.RefreshParentWindowReload(Page);
                }
            }
        }