/// <summary>
/// 分页获取数据列表
/// </summary>
public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM ( ");
strSql.Append(" SELECT ROW_NUMBER() OVER (");
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append("order by T." + orderby);
}
else
{
strSql.Append("order by T.ID desc");
}
strSql.Append(")AS Row, T.* from employeedomination T ");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.Append(" WHERE " + strWhere);
}
strSql.Append(" ) TT");
strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
return(DbHelperMySQL.Query(strSql.ToString()));
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public RightDown GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,FROMVALUE,TOVALUE,RIGHTDOWNPERCENT from rightdown ");
strSql.Append(" where ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 40)
};
parameters[0].Value = ID;
RightDown model = new RightDown();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public SystemLog GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,OPERATETYPE,OPERATECONTENT,CREATEDATE,EMPLOYEEID,IPADDRESS,PHYSICALADDRESS from systemlog ");
strSql.Append(" where ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 40)
};
parameters[0].Value = ID;
SystemLog model = new SystemLog();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public WithdrawDetails GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,EMPLOYEEID,WITHDRAWAMOUNT,CREATEDATE,ISCONFIRMED,OPERATEPERSON,ISDELETED from withdrawdetails ");
strSql.Append(" where ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 36)
};
parameters[0].Value = ID;
WithdrawDetails model = new WithdrawDetails();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public EmployeeAccount GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,AMOUNT,PAIDAMOUNT,SURPLUSAMOUNT,OTHERSAMOUNT,REWARDANDAMERCEMENTAMOUNT,CREATEDATE,EMPLOYEEID,LASTUPDATEDATE,ISDELETED from employeeaccount ");
strSql.Append(" where ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 36)
};
parameters[0].Value = ID;
EmployeeAccount model = new EmployeeAccount();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public WeightsConfig GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,ITEMKEY,ITEMNAME,ITEMVALUE from weightsconfig ");
strSql.Append(" where ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 40)
};
parameters[0].Value = ID;
WeightsConfig model = new WeightsConfig();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public ProjectProportion GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,PROJECTID,PROPORTION from projectproportion ");
strSql.Append(" where ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 36)
};
parameters[0].Value = ID;
ProjectProportion model = new ProjectProportion();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public TransactionDetails GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,TRANSACTIONAMOUNT,TRANSACTIONDESCRIPTION,TRANSACTIONPROPORTION,TRANSACTIONDATE,PLANDATE,TRANSACTIONTYPE,EMPLOYEEID,PROJECTID,CREATEDATE,ISDELETED from transactiondetails ");
strSql.Append(" where ISDELETED = 0 AND ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 36)
};
parameters[0].Value = ID;
TransactionDetails model = new TransactionDetails();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public ProjectSharing GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,PROJECTID,FINISHEDPERSON,PROPORTION,CREATEDATE from projectsharing ");
strSql.Append(" where ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 40)
};
parameters[0].Value = ID;
ProjectSharing model = new ProjectSharing();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public ProjectModify GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,PROJECTID,FOLDERNAME,FOLDERFULLNAME,ISUPLOADATTACH,ISFINISHED,REVIEWSTATUS,createdate from projectmodify ");
strSql.Append(" where ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 40)
};
parameters[0].Value = ID;
ProjectModify model = new ProjectModify();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Attachment GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,TASKID,TASKTYPE,FILENAME,EXTENSION,FILEFULLNAME,CREATEDATE from attachment ");
strSql.Append(" where ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 40)
};
parameters[0].Value = ID;
Attachment model = new Attachment();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 获取回复tab列表
/// </summary>
/// <param name="categoryId">配置表中的categoryId</param>
/// <param name="parentId">针对哪个记录进行的回复</param>
/// <returns></returns>
public DataSet GetReplayToList(string projectId, string category)
{
// 需要对 parentCategory 进行转换，
string parentCategory = GlobalConfig.dicMapForSubTab[category].ToString();
string strSql = @"
SELECT f.Id, f.CATEGORY CATEGORYId, f.TITLE, f.expireDate FROM filecategory f
WHERE PROJECTID = @projectId AND CATEGORY = @parentCategory
AND f.ID NOT IN (
SELECT PARENTID FROM filecategory
WHERE PROJECTID = @projectId AND CATEGORY = @category and PARENTID <> '0' ) ORDER BY f.orderSort ";
MySqlParameter[] parameters =
{
new MySqlParameter("@projectId", MySqlDbType.VarChar, 36),
new MySqlParameter("@category", MySqlDbType.String, 3),
new MySqlParameter("@parentCategory", MySqlDbType.String, 3)
};
parameters[0].Value = projectId;
parameters[1].Value = category;
parameters[2].Value = parentCategory;
DataSet ds = DbHelperMySQL.Query(strSql, parameters);
return(ds);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public FileCategory GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,PROJECTID,CATEGORY,TITLE,DESCRIPTION,FOLDERNAME,CREATEDATE,CREATEUSER,exPIREDATE,PARENTID,CLASSSORT,DIVISIONSORT,ORDERSORT from filecategory ");
strSql.Append(" where ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 36)
};
parameters[0].Value = ID;
FileCategory model = new FileCategory();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public TaskAssignConfigDetails GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,EMPLOYEEID,SPECIALTYCATEGORY,QUALITYSCORE,AVAILABLE,TIMEMULTIPLE,SPECIALTYTYPE from taskassignconfigdetails ");
strSql.Append(" where ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 40)
};
parameters[0].Value = ID;
TaskAssignConfigDetails model = new TaskAssignConfigDetails();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public SalaryConfig GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,EMPLOYEEID,BASESALARY,AGEWAGE,ACCOMMODATION_ALLOWANCE,MEAL_ALLOWANCE,OTHERWAGE,SOCIALSECURITY_INDIVIDUAL,SOCIALSECURITY_COMPANY,HOUSINGPROVIDENTFUND_INDIVIDUAL,HOUSINGPROVIDENTFUND_COMPANY,PAYOFF_TYPE,COMMISSION from salaryconfig ");
strSql.Append(" where ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 40)
};
parameters[0].Value = ID;
SalaryConfig model = new SalaryConfig();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public EmployeeProportion GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,EMPLOYEEID,PROPORTION,PARENTEMPLOYEEID,ISBRANCHLEADER from employeeproportion ");
strSql.Append(" where ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 36)
};
parameters[0].Value = ID;
EmployeeProportion model = new EmployeeProportion();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public FileHistory GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,PARENTID,FILENAME,FILEEXTENSION,FILEFULLNAME,DESCRIPTION,OPERATEDATE,OPERATEUSER,ISDELETED from filehistory ");
strSql.Append(" where ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 36)
};
parameters[0].Value = ID;
FileHistory model = new FileHistory();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public FileZillaServerModel.Salary GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,EMPLOYEEID,MONTHDATE,BASESALARY,CONTRACTSALARY,PIECEWAGE,PIECEPENALTY,FULLATTEND,OVERTIMEWAGE,AGEWAGE,ACCOMMODATION_ALLOWANCE,MEAL_ALLOWANCE,ATTENDANCEPENALTY,OTHERWAGE,SOCIALSECURITY_INDIVIDUAL,SOCIALSECURITY_COMPANY,HOUSINGPROVIDENTFUND_INDIVIDUAL,HOUSINGPROVIDENTFUND_COMPANY,TOTALINCOME,REALSALARY,REALSALARY_COMPANY from salary ");
strSql.Append(" where ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 40)
};
parameters[0].Value = ID;
FileZillaServerModel.Salary model = new FileZillaServerModel.Salary();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public FileOperationLog GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,PROJECTID,EMPLOYEEID,FILENAME,OPERATETYPE,OPERATEDATE,OPERATEUSER,OPERATECONTENT from fileoperationlog ");
strSql.Append(" where ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 36)
};
parameters[0].Value = ID;
FileOperationLog model = new FileOperationLog();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Employee GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,EMPLOYEENO,PASSWORD,NAME,SEX,BIRTHDATE,NATIVEPLACE,MOBILEPHONE,ADDRESS,EMAIL,TOREGULARDATE,BANKCARD,DEPARTMENTID,POLITICALSTATUS,TYPE,ISBRANCHLEADER,ISEXTERNAL,AVAILABLE,DINGTALKUSERID from employee ");
strSql.Append(" where AVAILABLE = 1 AND ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 40)
};
parameters[0].Value = ID;
Employee model = new Employee();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public TaskTrend GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,PROJECTID,EMPLOYEEID,DESCRIPTION,CREATEDATE,READSTATUS,TYPE from tasktrend ");
strSql.Append(" where ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 36)
};
parameters[0].Value = ID;
TaskTrend model = new TaskTrend();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Cerficate GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,EMPLOYEEID,CERTIFICATENAME,FILEPATH,DESCRIPTION,ISMAIN from cerficate ");
strSql.Append(" where ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 36)
};
parameters[0].Value = ID;
Cerficate model = new Cerficate();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public EmployeeRole GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,EMPLOYEEID,ROLEID from employeerole ");
strSql.Append(" where ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 40)
};
parameters[0].Value = ID;
EmployeeRole model = new EmployeeRole();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public TaskAssignConfig GetModel(string ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,EMPLOYEEID,AVAILABLE,TARGETAMOUNT,TIMEMULTIPLE from taskassignconfig ");
strSql.Append(" where ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 40)
};
parameters[0].Value = ID;
TaskAssignConfig model = new TaskAssignConfig();
DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/*
* /// <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);
}