public string EditEmsDepR(EmsDepRelation query)
{
string json = string.Empty;
try
{
if (query.emsdep == "relation_order_count")//訂單筆數)
{
if (emsDepRe.RelationOrderCount(query) > 0)
{
json = "{success:true}";
}
else
{
json = "{success:false}";
}
}
else if (query.emsdep == "relation_order_cost")//訂單成本
{
if (emsDepRe.RelationOrderCost(query) > 0)
{
json = "{success:true}";
}
else
{
json = "{success:false}";
}
}
return json;
}
catch (Exception ex)
{
throw new Exception("EmsDepRelationMgr-->EditEmsDepR-->" + ex.Message, ex);
}
}
public HttpResponseBase GetDepStore()
{
string json = string.Empty;
List<EmsDepRelation> store = new List<EmsDepRelation>();
EmsDepRelation query = new EmsDepRelation();
query.dep_name = "請選擇...";
try
{
emsDepRelation = new EmsDepRelationMgr(mySqlConnectionString);
store= emsDepRelation.GetDepStore();
store.Insert(0, query);
json = "{success:true,data:" + JsonConvert.SerializeObject(store, Formatting.Indented) + "}";
}
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,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public bool insertPreDate(EmsDepRelation query)
{
StringBuilder sqlInsert = new StringBuilder();
try
{
List<EmsDepRelation> store = emsDepRe.GetDepStore();
ArrayList arrList = new ArrayList();
for (int i = 1; i <= query.predate.Day; i++)
{
for (int j = 0; j < store.Count; j++)
{
query.insert_day = i;
query.dep_code_insert = store[j].dep_code;
query.relation_type_insert = 1;//公關單
if (emsDepRe.IsPRSingleExist(query) == 0)
{
arrList.Add(emsDepRe.insertSql(query));
}
query.relation_type_insert = 2;//報廢單
if (emsDepRe.IsPRSingleExist(query) == 0)
{
arrList.Add(emsDepRe.insertSql(query));
}
}
}
return emsDepRe.execSql(arrList);
}
catch (Exception ex)
{
throw new Exception("EmsDepRelationMgr-->insertPreDate-->" + ex.Message);
}
}
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 List<EmsDepRelation> EmsDepRelationList(EmsDepRelation query, out int totalCount)
{
try
{
insertPreDate(query);
return emsDepRe.EmsDepRelationList(query, out totalCount);
}
catch (Exception ex)
{
throw new Exception("EmsDepRelationMgr-->EmsDepRelationList-->"+ex.Message);
}
}
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 HttpResponseBase EmsDepRelationList()
{
List<EmsDepRelation> store = new List<EmsDepRelation>();
EmsDepRelation query = new EmsDepRelation();
int totalCount = 0;
string json = string.Empty;
emsDepRelation = new EmsDepRelationMgr(mySqlConnectionString);
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
try
{
if (!string.IsNullOrEmpty(Request.Params["dep_code"]))
{
query.relation_dep = Convert.ToInt32(Request.Params["dep_code"]);
}
if (!string.IsNullOrEmpty(Request.Params["relation_type"]))
{
query.re_type = Convert.ToInt32(Request.Params["relation_type"]);
}
if (!string.IsNullOrEmpty(Request.Params["datatype"]))
{
query.datatype = Convert.ToInt32(Request.Params["datatype"]);
}
if (!string.IsNullOrEmpty(Request.Params["date"]))
{
query.date = Convert.ToDateTime(Request.Params["date"]);
}
query.create_user = (Session["caller"] as Caller).user_id;
query.update_user = (Session["caller"] as Caller).user_id;
query.predate = DateTime.Now.AddDays(-1);
store=emsDepRelation.EmsDepRelationList(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 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 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);
}
}
public HttpResponseBase SaveEmsDep()
{
EmsDepRelation query = new EmsDepRelation();
string json = string.Empty;
try
{
if (!string.IsNullOrEmpty(Request.Params["relation_dep"]))
{
query.relation_dep = Convert.ToInt32(Request.Params["relation_dep"]);
}
if (!string.IsNullOrEmpty(Request.Params["relation_type"]))
{
query.relation_type = Convert.ToInt32(Request.Params["relation_type"]);
}
if (!string.IsNullOrEmpty(Request.Params["relation_year"]))
{
query.relation_year = Convert.ToInt32(Request.Params["relation_year"]);
}
if (!string.IsNullOrEmpty(Request.Params["relation_month"]))
{
query.relation_month = Convert.ToInt32(Request.Params["relation_month"]);
}
if (!string.IsNullOrEmpty(Request.Params["relation_day"]))
{
query.relation_day = Convert.ToInt32(Request.Params["relation_day"]);
}
if (!string.IsNullOrEmpty(Request.Params["relation_order_cost"]))
{
query.relation_order_cost = Convert.ToInt32(Request.Params["relation_order_cost"]);
}
if (!string.IsNullOrEmpty(Request.Params["relation_order_count"]))
{
query.relation_order_count = Convert.ToInt32(Request.Params["relation_order_count"]);
}
query.create_user = (Session["caller"] as Caller).user_id;
query.update_user = (Session["caller"] as Caller).user_id;
emsDepRelation = new EmsDepRelationMgr(mySqlConnectionString);
json= emsDepRelation.SaveEmsDepRe(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 HttpResponseBase EditEmsDepR()
{
string json = string.Empty;
EmsDepRelation query = new EmsDepRelation();
try
{
if (!string.IsNullOrEmpty(Request.Params["relation_id"]))
{
query.relation_id = Convert.ToInt32(Request.Params["relation_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["emsDep"]))
{
query.emsdep = (Request.Params["emsDep"]);
}
if (!string.IsNullOrEmpty(Request.Params["value"]))
{
query.value = Convert.ToInt32(Request.Params["value"]);
}
query.update_user = (Session["caller"] as Caller).user_id;
emsDepRelation=new EmsDepRelationMgr (mySqlConnectionString);
json = emsDepRelation.EditEmsDepR(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 string SaveEmsDepRe(EmsDepRelation query)
{
string json = string.Empty;
try
{
string time = query.relation_year + "-" + query.relation_month + "-" + query.relation_day;
DateTime dtime = DateTime.Now;
DateTime sTime = DateTime.Now;
bool isTime = DateTime.TryParse(time, out dtime);//是否是正常時間 是 true 否 false
if (!isTime)//不是正常時間
{
json = "{success:true,msg:3}";//選擇時間有誤
}
else//如果是正常時間判斷是否大於當前時間
{
if (emsDepRe.VerifyData(query) > 0)
{
json = "{success:true,msg:0}";//不可添加數據
}
else
{
if (emsDepRe.SaveEmsDepRe(query) > 0)
{
json = "{success:true,msg:1}";//新增成功
}
else
{
json = "{success:true,msg:2}";//新增失敗
}
}
}
return json;
}
catch (Exception ex)
{
throw new Exception("EmsDepRelationMgr-->SaveEmsDepRe-->" + ex.Message, ex);
}
}
public int VerifyData(EmsDepRelation query)
{
try
{
return emsDepRe.VerifyData(query);
}
catch (Exception ex)
{
throw new Exception("EmsDepRelationMgr-->VerifyData-->" + ex.Message, ex);
}
}
