/// <summary>
/// 增加一条数据
/// </summary>
public bool Add(Employee model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into employee(");
strSql.Append("ID,EMPLOYEENO,PASSWORD,NAME,SEX,BIRTHDATE,NATIVEPLACE,MOBILEPHONE,ADDRESS,EMAIL,TOREGULARDATE,BANKCARD,DEPARTMENTID,POLITICALSTATUS,TYPE,ISBRANCHLEADER,ISEXTERNAL,AVAILABLE,DINGTALKUSERID)");
strSql.Append(" values (");
strSql.Append("@ID,@EMPLOYEENO,@PASSWORD,@NAME,@SEX,@BIRTHDATE,@NATIVEPLACE,@MOBILEPHONE,@ADDRESS,@EMAIL,@TOREGULARDATE,@BANKCARD,@DEPARTMENTID,@POLITICALSTATUS,@TYPE,@ISBRANCHLEADER,@ISEXTERNAL,@AVAILABLE,@DINGTALKUSERID)");
MySqlParameter[] parameters =
{
new MySqlParameter("@ID", MySqlDbType.VarChar, 40),
new MySqlParameter("@EMPLOYEENO", MySqlDbType.VarChar, 50),
new MySqlParameter("@PASSWORD", MySqlDbType.VarChar, 200),
new MySqlParameter("@NAME", MySqlDbType.VarChar, 100),
new MySqlParameter("@SEX", MySqlDbType.Bit),
new MySqlParameter("@BIRTHDATE", MySqlDbType.Date),
new MySqlParameter("@NATIVEPLACE", MySqlDbType.VarChar, 40),
new MySqlParameter("@MOBILEPHONE", MySqlDbType.VarChar, 20),
new MySqlParameter("@ADDRESS", MySqlDbType.VarChar, 255),
new MySqlParameter("@EMAIL", MySqlDbType.VarChar, 50),
new MySqlParameter("@TOREGULARDATE", MySqlDbType.Date),
new MySqlParameter("@BANKCARD", MySqlDbType.VarChar, 20),
new MySqlParameter("@DEPARTMENTID", MySqlDbType.VarChar, 40),
new MySqlParameter("@POLITICALSTATUS", MySqlDbType.VarChar, 20),
new MySqlParameter("@TYPE", MySqlDbType.Decimal, 1),
new MySqlParameter("@ISBRANCHLEADER", MySqlDbType.Bit),
new MySqlParameter("@ISEXTERNAL", MySqlDbType.Bit),
new MySqlParameter("@AVAILABLE", MySqlDbType.Decimal, 1),
new MySqlParameter("@DINGTALKUSERID", MySqlDbType.VarChar, 40)
};
parameters[0].Value = model.ID;
parameters[1].Value = model.EMPLOYEENO;
parameters[2].Value = model.PASSWORD;
parameters[3].Value = model.NAME;
parameters[4].Value = model.SEX;
parameters[5].Value = model.BIRTHDATE;
parameters[6].Value = model.NATIVEPLACE;
parameters[7].Value = model.MOBILEPHONE;
parameters[8].Value = model.ADDRESS;
parameters[9].Value = model.EMAIL;
parameters[10].Value = model.TOREGULARDATE;
parameters[11].Value = model.BANKCARD;
parameters[12].Value = model.DEPARTMENTID;
parameters[13].Value = model.POLITICALSTATUS;
parameters[14].Value = model.TYPE;
parameters[15].Value = model.ISBRANCHLEADER;
parameters[16].Value = model.ISEXTERNAL;
parameters[17].Value = model.AVAILABLE;
parameters[18].Value = model.DINGTALKUSERID;
int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Employee model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update employee set ");
strSql.Append("EMPLOYEENO=@EMPLOYEENO,");
strSql.Append("PASSWORD=@PASSWORD,");
strSql.Append("NAME=@NAME,");
strSql.Append("SEX=@SEX,");
strSql.Append("BIRTHDATE=@BIRTHDATE,");
strSql.Append("NATIVEPLACE=@NATIVEPLACE,");
strSql.Append("MOBILEPHONE=@MOBILEPHONE,");
strSql.Append("ADDRESS=@ADDRESS,");
strSql.Append("EMAIL=@EMAIL,");
strSql.Append("TOREGULARDATE=@TOREGULARDATE,");
strSql.Append("BANKCARD=@BANKCARD,");
strSql.Append("DEPARTMENTID=@DEPARTMENTID,");
strSql.Append("POLITICALSTATUS=@POLITICALSTATUS,");
strSql.Append("TYPE=@TYPE,");
strSql.Append("ISBRANCHLEADER=@ISBRANCHLEADER,");
strSql.Append("ISEXTERNAL=@ISEXTERNAL,");
strSql.Append("AVAILABLE=@AVAILABLE,");
strSql.Append("DINGTALKUSERID=@DINGTALKUSERID");
strSql.Append(" where ID=@ID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@EMPLOYEENO", MySqlDbType.VarChar, 50),
new MySqlParameter("@PASSWORD", MySqlDbType.VarChar, 200),
new MySqlParameter("@NAME", MySqlDbType.VarChar, 100),
new MySqlParameter("@SEX", MySqlDbType.Bit),
new MySqlParameter("@BIRTHDATE", MySqlDbType.DateTime),
new MySqlParameter("@NATIVEPLACE", MySqlDbType.VarChar, 40),
new MySqlParameter("@MOBILEPHONE", MySqlDbType.VarChar, 20),
new MySqlParameter("@ADDRESS", MySqlDbType.VarChar, 255),
new MySqlParameter("@EMAIL", MySqlDbType.VarChar, 50),
new MySqlParameter("@TOREGULARDATE", MySqlDbType.DateTime),
new MySqlParameter("@BANKCARD", MySqlDbType.VarChar, 20),
new MySqlParameter("@DEPARTMENTID", MySqlDbType.VarChar, 40),
new MySqlParameter("@POLITICALSTATUS", MySqlDbType.VarChar, 20),
new MySqlParameter("@TYPE", MySqlDbType.Decimal, 1),
new MySqlParameter("@ISBRANCHLEADER", MySqlDbType.Bit),
new MySqlParameter("@ISEXTERNAL", MySqlDbType.Bit),
new MySqlParameter("@AVAILABLE", MySqlDbType.Decimal, 1),
new MySqlParameter("@DINGTALKUSERID", MySqlDbType.VarChar, 40),
new MySqlParameter("@ID", MySqlDbType.VarChar, 40)
};
parameters[0].Value = model.EMPLOYEENO;
parameters[1].Value = model.PASSWORD;
parameters[2].Value = model.NAME;
parameters[3].Value = model.SEX;
parameters[4].Value = model.BIRTHDATE;
parameters[5].Value = model.NATIVEPLACE;
parameters[6].Value = model.MOBILEPHONE;
parameters[7].Value = model.ADDRESS;
parameters[8].Value = model.EMAIL;
parameters[9].Value = model.TOREGULARDATE;
parameters[10].Value = model.BANKCARD;
parameters[11].Value = model.DEPARTMENTID;
parameters[12].Value = model.POLITICALSTATUS;
parameters[13].Value = model.TYPE;
parameters[14].Value = model.ISBRANCHLEADER;
parameters[15].Value = model.ISEXTERNAL;
parameters[16].Value = model.AVAILABLE;
parameters[17].Value = model.DINGTALKUSERID;
parameters[18].Value = model.ID;
int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/*
* /// <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);
}
