public List<EmsActualQuery> GetEmsActualList(EmsActualQuery query, out int totalCount)
{
try
{
//列表頁數據查詢出來之前判斷前一天數據是否存在，不存在則插入默認值
insertPreDate(query);
return _IEmsDao.GetEmsActualList(query,out totalCount);
}
catch (Exception ex)
{
throw new Exception("EmsMgr-->GetEmsActualList-->" + ex.Message, ex);
}
}
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);
}
}
//實際業績列表頁
public HttpResponseBase GetEmsActualList()
{
List<EmsActualQuery> store = new List<EmsActualQuery>();
EmsActualQuery query = new EmsActualQuery();
int totalCount = 0;
string json = string.Empty;
_IEmsMgr = new EmsMgr(mySqlConnectionString);
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
try
{
if (!string.IsNullOrEmpty(Request.Params["departactual"]))
{
query.department_code = Request.Params["departactual"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["dateactual"]))
{
query.date = Convert.ToDateTime(Request.Params["dateactual"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["datatype"]))
{
query.type = Convert.ToInt32(Request.Params["datatype"].ToString());
}
else
{
query.type = 0;
}
//列表頁數據查詢出來之前判斷前一天數據是否存在，不存在則插入默認值
query.predate = DateTime.Now.AddDays(-1);
query.user_userid = (Session["caller"] as Caller).user_id;
store = _IEmsMgr.GetEmsActualList(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
//編輯實際業績
public HttpResponseBase EditEmsActual()
{
string json = string.Empty;
EmsActualQuery query = new EmsActualQuery();
try
{
if (!string.IsNullOrEmpty(Request.Params["id"]))
{
query.row_id = Convert.ToInt32(Request.Params["id"]);
}
if (!string.IsNullOrEmpty(Request.Params["EmsActual"]))
{
query.EmsActual = (Request.Params["EmsActual"]);
}
if (!string.IsNullOrEmpty(Request.Params["EmsValue"]))
{
query.EmsValue = Convert.ToInt32(Request.Params["EmsValue"]);
}
_IEmsMgr = new EmsMgr(mySqlConnectionString);
json = _IEmsMgr.EditEmsActual(query);
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
//保存實際業績
public HttpResponseBase SaveEmsActual()
{
string json = string.Empty;
EmsActualQuery query = new EmsActualQuery();
try
{
if (!string.IsNullOrEmpty(Request.Params["department_code"]))
{
query.department_code = Request.Params["department_code"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["year"]))
{
query.year = Convert.ToInt32(Request.Params["year"]);
}
if (!string.IsNullOrEmpty(Request.Params["month"]))
{
query.month = Convert.ToInt32(Request.Params["month"]);
}
if (!string.IsNullOrEmpty(Request.Params["day"]))
{
query.day = Convert.ToInt32(Request.Params["day"]);
}
if (!string.IsNullOrEmpty(Request.Params["cost_sum"]))
{
query.cost_sum = Convert.ToInt32(Request.Params["cost_sum"]);
}
if (!string.IsNullOrEmpty(Request.Params["order_count"]))
{
query.order_count = Convert.ToInt32(Request.Params["order_count"]);
}
if (!string.IsNullOrEmpty(Request.Params["amount_sum"]))
{
query.amount_sum = Convert.ToInt32(Request.Params["amount_sum"]);
}
query.create_time = DateTime.Now;
query.user_userid = (Session["caller"] as Caller).user_id;
_IEmsMgr = new EmsMgr(mySqlConnectionString);
json = _IEmsMgr.SaveEmsActual(query);
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:true,msg:2}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public bool insertPreDate(EmsActualQuery query)
{
StringBuilder sqlInsert = new StringBuilder();
try
{
List<EmsGoalQuery> store = new List<EmsGoalQuery>();
store = _IEmsDao.GetDepartmentStore();
ArrayList arrList = new ArrayList();
for (int i = 1; i <= query.predate.Day; i++)
{
for (int j = 0; j < store.Count; j++)
{
query.day = i;
query.department_code_insert = store[j].department_code;
if (_IEmsDao.IsExist(query) == 0)
{
arrList.Add(_IEmsDao.insertSql(query));
}
}
}
return _emsDao.execInsertSql(arrList);
}
catch (Exception ex)
{
throw new Exception("EmsMgr-->insertPreDate-->" + ex.Message, ex);
}
}