/// <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();
}
}