protected void NewReim_Click(object sender, EventArgs e)
{
string reimName = this.ReimName.Text.Trim();
string reimDesc = this.ReimDesc.Text.Trim();
DalOperationAboutReim dalReim = new DalOperationAboutReim();
if (this.btn_NewReim.Text == "修改")
{
string reimId = this.hf_ReimId.Value;
Reim reim = dalReim.GetReim(int.Parse(reimId));
if (reim != null)
{
reim.name = reimName;
reim.comment = reimDesc;
dalReim.UpdateReim(reim);
}
else {
Javascript.Alert("您所操作的报销项不存在或已被删除!", Page);
}
}
else {
Reim reim = new Reim()
{
name = reimName,
comment = reimDesc
};
dalReim.AddReim(reim);
}
Javascript.Alert("操作成功!", Page);
Javascript.RefreshParentWindowReload(Page);
}
public void AddReim(Reim reim)
{
try
{
string commandString = "INSERT INTO usta_Reim (name, comment, createdTime) VALUES(@name, @comment, @createdTime)";
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@name", reim.name),
new SqlParameter("@comment", reim.comment),
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 BuildReims(IDataReader reader, List<Reim> reims)
{
Reim reim;
while (reader.Read())
{
reim = new Reim();
reim.id = int.Parse(reader["id"].ToString().Trim());
reim.name = reader["name"].ToString().Trim();
reim.comment = reader["comment"].ToString().Trim();
reim.createdTime = DateTime.Parse(reader["createdTime"].ToString().Trim());
reims.Add(reim);
}
}
public void UpdateReim(Reim reim)
{
try
{
string commandString = "UPDATE usta_Reim SET name = @name, @comment = @comment WHERE id = @reimId";
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@name", reim.name),
new SqlParameter("@comment", reim.comment),
new SqlParameter("@reimId", reim.id)
};
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;
}
