/// <summary>
/// 获取记录总数
/// </summary>
public int GetRecordCount(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) FROM withdrawdetails ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
object obj = DbHelperMySQL.GetSingle(strSql.ToString());
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/*
* /// <summary>
* /// 分页获取数据列表
* /// </summary>
* public DataSet GetList(int PageSize,int PageIndex,string strWhere)
* {
* MySqlParameter[] parameters = {
* new MySqlParameter("@tblName", MySqlDbType.VarChar, 255),
* new MySqlParameter("@fldName", MySqlDbType.VarChar, 255),
* new MySqlParameter("@PageSize", MySqlDbType.Int32),
* new MySqlParameter("@PageIndex", MySqlDbType.Int32),
* new MySqlParameter("@IsReCount", MySqlDbType.Bit),
* new MySqlParameter("@OrderType", MySqlDbType.Bit),
* new MySqlParameter("@strWhere", MySqlDbType.VarChar,1000),
* };
* parameters[0].Value = "transactiondetails";
* parameters[1].Value = "ID";
* parameters[2].Value = PageSize;
* parameters[3].Value = PageIndex;
* parameters[4].Value = 0;
* parameters[5].Value = 0;
* parameters[6].Value = strWhere;
* return DbHelperMySQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
* }*/
#endregion BasicMethod
#region ExtensionMethod
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetListJoinEmpAndPrj(Dictionary <string, string> dic, Dictionary <string, bool> dicSelectFlag, string transactionType, int pageIndex, int pageSize, out int totalRecordCount, out int sumAmount, out DataTable exportDataTable)
{
totalRecordCount = 0;
sumAmount = 0;
exportDataTable = new DataTable();
StringBuilder sbSelectColumn = new StringBuilder();
StringBuilder sbSelectCount = new StringBuilder();
StringBuilder sbSumAmount = new StringBuilder();
StringBuilder sbExport = new StringBuilder();
sbSelectColumn.Append(@"SELECT td.ID,TRANSACTIONAMOUNT,TRANSACTIONDESCRIPTION,TRANSACTIONPROPORTION,TRANSACTIONDATE,td.PLANDATE,
cf.configValue TRANSACTIONTYPE,td.EMPLOYEEID,td.PROJECTID,td.CREATEDATE,td.ISDELETED,
e.EMPLOYEENO, e.`NAME`, p.TASKNO ");
sbSelectCount.Append("SELECT COUNT(*) ");
sbSumAmount.Append("SELECT SUM(TRANSACTIONAMOUNT) ");
sbExport.Append(@"SELECT e.EMPLOYEENO 员工编号, e.`NAME` 员工姓名, p.TASKNO 任务编号, TRANSACTIONAMOUNT 交易金额,TRANSACTIONDESCRIPTION 描述信息,TRANSACTIONDATE 交易时间,td.PLANDATE 计划时间,
cf.configValue 交易类型, td.CREATEDATE 创建时间 ");
StringBuilder sbFromAndWhere = new StringBuilder();
sbFromAndWhere.Append(@"FROM transactiondetails td
LEFT JOIN employee e
ON td.EMPLOYEEID = e.ID
LEFT JOIN project p
ON td.PROJECTID = p.ID
LEFT JOIN
(
select configkey, configvalue from configvalue cv
left join configtype ct
on cv.configtypeid = ct.configtypeid
WHERE ct.CONFIGTYPENAME = '奖励与处罚类型'
) cf
ON td.TRANSACTIONTYPE = cf.configkey
WHERE TD.ISDELETED = 0 ");
if (dic.ContainsKey("employeeId"))
{
sbFromAndWhere.AppendFormat(" AND employeeId = '{0}'", dic["employeeId"]);
}
if (string.IsNullOrEmpty(transactionType))
{
if (dic.ContainsKey("transacType"))
{
sbFromAndWhere.AppendFormat(" AND transactionType = '{0}'", dic["transacType"]);
}
}
else
{
sbFromAndWhere.Append(" AND transactionType IN (" + transactionType + ")");
}
if (dic.ContainsKey("amountFrom"))
{
sbFromAndWhere.AppendFormat(" AND TRANSACTIONAMOUNT >= {0}", dic["amountFrom"]);
}
if (dic.ContainsKey("amountTo"))
{
sbFromAndWhere.AppendFormat(" AND TRANSACTIONAMOUNT <= {0}", dic["amountTo"]);
}
if (dic.ContainsKey("dateFrom"))
{
sbFromAndWhere.AppendFormat(" AND transactiondate >= '{0}'", dic["dateFrom"]);
}
if (dic.ContainsKey("dateTo"))
{
sbFromAndWhere.AppendFormat(" AND transactiondate <= '{0}'", dic["dateTo"]);
}
if (dic.ContainsKey("planDate"))
{
sbFromAndWhere.AppendFormat(" AND DATE_FORMAT(planDate,'%Y-%m')='{0}'", dic["planDate"]);
}
if (dic.ContainsKey("taskNo"))
{
sbFromAndWhere.AppendFormat(" AND taskNo like '%" + dic["taskNo"] + "%'");
}
// 查询结果集
var sqlDataSet = sbSelectColumn.Append(sbFromAndWhere).AppendFormat(" ORDER BY TRANSACTIONDATE DESC LIMIT {0}, {1} ", (pageIndex - 1) * pageSize, pageSize);
// 查询记录总数
var sqlCount = sbSelectCount.Append(sbFromAndWhere);
totalRecordCount = Convert.ToInt32(DbHelperMySQL.GetSingle(sqlCount.ToString()));
if (dicSelectFlag.ContainsKey("selectSumAmount") && dicSelectFlag["selectSumAmount"])
{
var sqlSumAmount = sbSumAmount.Append(sbFromAndWhere);
sumAmount = Convert.ToInt32(DbHelperMySQL.GetSingle(sqlSumAmount.ToString()));
}
if (dicSelectFlag.ContainsKey("needExport") && dicSelectFlag["needExport"])
{
var sqlExport = sbExport.Append(sbFromAndWhere).AppendFormat(" ORDER BY TRANSACTIONDATE DESC ");
exportDataTable = DbHelperMySQL.Query(sqlExport.ToString()).Tables[0];
}
DataSet ds = DbHelperMySQL.Query(sqlDataSet.ToString());
return(ds);
}