public int IsPRSingleExist(EmsDepRelation query) { StringBuilder sql = new StringBuilder(); try { query.Replace4MySQL(); sql.AppendFormat("select edr.relation_id from ems_dep_relation edr where edr.relation_year='{0}' and edr.relation_month='{1}' and edr.relation_day='{2}' and edr.relation_create_type='{3}' and edr.relation_dep='{4}' and edr.relation_type='{5}';", query.predate.Year, query.predate.Month, query.insert_day, query.relation_create_type, query.dep_code_insert, query.relation_type_insert); return _access.getDataTable(sql.ToString()).Rows.Count; } catch (Exception ex) { throw new Exception("EmsDepRelationDao-->IsPRSingleExist-->" + sql.ToString() + ex.Message, ex); } }
public List<EmsDepRelation> EmsDepRelationList(EmsDepRelation query,out int totalCount) { StringBuilder sql = new StringBuilder(); string sqlCount = string.Empty; StringBuilder sqlFrom = new StringBuilder(); StringBuilder sqlWhere = new StringBuilder(); totalCount = 0; try { query.Replace4MySQL(); sql.Append(" select edr.relation_id,edr.relation_type,edr.relation_order_count,edr.relation_order_cost,edr.relation_dep,edr.update_time,"); sql.Append(" edr.create_time,edr.relation_create_type, edr.create_user,edr.update_user,edr.relation_year,edr.relation_month,edr.relation_day,para.parameterName as 'dep_name',mu.user_username "); sqlFrom.Append(" from ems_dep_relation edr "); sqlFrom.Append(" LEFT JOIN manage_user mu on mu.user_id= edr.create_user LEFT JOIN (select parameterType,parameterCode,parameterName,remark from t_parametersrc where parameterType='dep' ) para on edr.relation_dep=para.parameterCode "); sqlWhere.Append(" where 1=1 "); if (query.relation_dep != 0) { sqlWhere.AppendFormat(" and edr.relation_dep='{0}' ", query.relation_dep); } if (query.re_type != 0) { sqlWhere.AppendFormat(" and edr.relation_type='{0}' ", query.re_type); } if (query.datatype != 0) { sqlWhere.AppendFormat(" and edr.relation_create_type='{0}' ", query.datatype); } sqlWhere.AppendFormat(" and edr.relation_year='{0}' and edr.relation_month='{1}' and edr.relation_day='{2}' ", query.date.Year,query.date.Month,query.date.Day); if (query.IsPage) { sqlCount = "select count(edr.relation_id) as totalCount "+sqlFrom.ToString()+sqlWhere.ToString(); DataTable _dt = _access.getDataTable(sqlCount.ToString()); if (_dt.Rows.Count > 0) { totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]); } } sqlWhere.AppendFormat(" order by edr.relation_year desc,edr.relation_month desc,edr.relation_day desc limit {0},{1}; ", query.Start, query.Limit); return _access.getDataTableForObj<EmsDepRelation>(sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString()); } catch (Exception ex) { throw new Exception("EmsDao-->EmsDepRelationList-->" + ex.Message + sql.ToString() + sqlWhere.ToString(), ex); } }
public string insertSql(EmsDepRelation query) { StringBuilder sqlInsert = new StringBuilder(); query.Replace4MySQL(); sqlInsert.Append("insert into ems_dep_relation (`relation_type`,`relation_order_count`,`relation_order_cost`,`relation_dep`,`update_time`,`create_time`,`relation_create_type`,`create_user`,`update_user`,`relation_year`,`relation_month`,`relation_day`)"); sqlInsert.AppendFormat("values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}');", query.relation_type_insert, query.relation_order_count, query.relation_order_cost, query.dep_code_insert, CommonFunction.DateTimeToString(DateTime.Now), CommonFunction.DateTimeToString(DateTime.Now), query.relation_create_type, query.create_user, query.update_user, query.predate.Year, query.predate.Month, query.insert_day); return sqlInsert.ToString(); }
public int SaveEmsDepRe(EmsDepRelation query) { StringBuilder sql = new StringBuilder(); try { query.Replace4MySQL(); sql.Append("insert into ems_dep_relation (`relation_type`,`relation_order_count`,`relation_order_cost`,`relation_dep`,`update_time`,`create_time`,`relation_create_type`,`create_user`,`update_user`,`relation_year`,`relation_month`,`relation_day`)"); sql.AppendFormat("values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}');", query.relation_type, query.relation_order_count, query.relation_order_cost, query.relation_dep, CommonFunction.DateTimeToString(DateTime.Now), CommonFunction.DateTimeToString(DateTime.Now), query.relation_create_type, query.create_user, query.update_user, query.relation_year, query.relation_month, query.relation_day); return _access.execCommand(sql.ToString()); } catch (Exception ex) { throw new Exception("EmsDepRelationDao-->SaveEmsDepRe-->"+sql.ToString()+ex.Message,ex); } }
public int VerifyData(EmsDepRelation query) { StringBuilder sql = new StringBuilder(); try { query.Replace4MySQL(); sql.AppendFormat("select count(edr.relation_id) as totalCount from ems_dep_relation edr where edr.relation_year='{0}' and edr.relation_month='{1}' and edr.relation_day='{2}' and edr.relation_create_type='{3}' and edr.relation_dep='{4}' and edr.relation_type='{5}';", query.relation_year, query.relation_month, query.relation_day, query.relation_create_type, query.relation_dep, query.relation_type); DataTable _dt = _access.getDataTable(sql.ToString()); return Convert.ToInt32(_dt.Rows[0]["totalCount"]); } catch (Exception ex) { throw new Exception("EmsDepRelationDao-->VerifyData-->" + sql.ToString() + ex.Message, ex); } }
public int RelationOrderCost(EmsDepRelation query) { StringBuilder sql = new StringBuilder(); try { query.Replace4MySQL(); sql.AppendFormat(" update ems_dep_relation set relation_order_cost='{0}',update_user='******',update_time='{2}' where relation_id='{3}';", query.value, query.update_user, CommonFunction.DateTimeToString(DateTime.Now), query.relation_id); return _access.execCommand(sql.ToString()); } catch (Exception ex) { throw new Exception("EmsDepRelationDao-->RelationOrderCount-->" + sql.ToString() + ex.Message, ex); } }