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