public List<EmsActualQuery> GetEmsActualList(EmsActualQuery query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
StringBuilder sqlFrom = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
totalCount = 0;
try
{
query.Replace4MySQL();
sqlCount.Append("select count(ea.row_id) as totalCount ");
sql.Append("select ea.row_id,ea.department_code,paral.parameterName as department_name ,ea.year,ea.month,ea.`day`,ea.type,ea.cost_sum,ea.order_count,ea.amount_sum,ea.status ,ea.create_time,mu.user_username ");
sqlFrom.Append(" from ems_actual ea ");
sqlFrom.Append(" LEFT JOIN (select parameterType,parameterCode,parameterName,remark from t_parametersrc where parameterType='emsdepartment' ) paral on ea.department_code=paral.parameterCode ");
sqlFrom.Append(" LEFT JOIN manage_user mu on ea.create_user=mu.user_id ");
sqlWhere.Append(" where 1=1 and ea.status=1 ");
if (query.department_code != "")
{
sqlWhere.AppendFormat(" and ea.department_code='{0}' ", query.department_code);
}
sqlWhere.AppendFormat(" and ea.year={0} and ea.month={1} and ea.`day`={2} ", query.date.Year, query.date.Month, query.date.Day);
if (query.type != 0)
{
sqlWhere.AppendFormat(" and ea.type={0} ",query.type);
}
if (query.IsPage)
{
DataTable _dt = _access.getDataTable(sqlCount.ToString() + sqlFrom.ToString()+sqlWhere.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
sqlWhere.AppendFormat(" ORDER BY ea.`year` DESC,ea.`month` desc,ea.`day` desc limit {0},{1};", query.Start, query.Limit);
}
return _access.getDataTableForObj<EmsActualQuery>(sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString());
}
catch (Exception ex)
{
throw new Exception("EmsDao-->GetEmsActualList-->" + ex.Message + sql.ToString() + sqlWhere.ToString(), ex);
}
}
public string insertSql(EmsActualQuery query)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
sql.Append(" insert into ems_actual (`department_code`,`year`,`month`,`day`,`type`,`cost_sum`,`order_count`,`amount_sum`,`status`,`create_time`,`create_user`)");
sql.AppendFormat(" VALUES('{0}',{1} ,{2} ,{3} ,{4},{5},{6},{7},{8},'{9}',{10}); ", query.department_code_insert, query.predate.Year, query.predate.Month, query.day, query.insertType, query.cost_sum, query.order_count, query.amount_sum, query.status, CommonFunction.DateTimeToString(DateTime.Now), query.user_userid);
return sql.ToString();
}
public int IsExist(EmsActualQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
query.Replace4MySQL();
sql.AppendFormat(" select ea.row_id from ems_actual ea where ea.year='{0}' and ea.month='{1}' and ea.`day`='{2}' and department_code='{3}' and type={4} ;", query.predate.Year, query.predate.Month, query.day, query.department_code_insert, query.insertType);
return _access.getDataTable(sql.ToString()).Rows.Count;
}
catch(Exception ex)
{
throw new Exception(" EmsDao-->IsExist--> " + sql.ToString()+ ex.Message, ex);
}
}
public int VerifyActualData(EmsActualQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
query.Replace4MySQL();
sql.AppendFormat("select count(ea.row_id) as totalCount from ems_actual ea where ea.`year`={0} and ea.`month`={1} and ea.`day`={2} and ea.department_code='{3}' and ea.type={4}; ", query.year, query.month, query.day, query.department_code, query.insertType);
DataTable _dt = _access.getDataTable(sql.ToString());
return Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
catch (Exception ex)
{
throw new Exception("EmsDao-->VerifyActualData-->" + sql.ToString() + ex.Message, ex);
}
}
public int SaveEmsActual(EmsActualQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
query.Replace4MySQL();
sql.Append(" insert into ems_actual (`department_code`,`year`,`month`,`day`,`type`,`cost_sum`,`order_count`,`amount_sum`,`status`,`create_time`,`create_user`)");
sql.AppendFormat(" VALUES('{0}',{1} ,{2} ,{3} ,{4},{5},{6},{7},{8},'{9}',{10}); ", query.department_code, query.year, query.month, query.day, query.type, query.cost_sum, query.order_count, query.amount_sum, query.status,CommonFunction.DateTimeToString(query.create_time),query.user_userid);
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("EmsDao-->SaveEmsActual-->" + sql.ToString() + ex.Message, ex);
}
}
public int AmountSumEmsActual(EmsActualQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
query.Replace4MySQL();
sql.AppendFormat("update ems_actual set amount_sum={0} where row_id={1};", query.EmsValue, query.row_id);
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("EmsDao-->CostSumEmsActual-->" + sql.ToString() + ex.Message, ex);
}
}