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