public JsonResult DeleteOutgoingManage(string OutMan, string OutgoingJson)
{
OutgoingEventModel Outgoing = null;
try
{
Outgoing = JsonConvert.DeserializeObject <OutgoingEventModel>(OutgoingJson);
Outgoing.OutMan = OutMan;
Outgoing.RecordMan = LoginUserInfo.UserId;
Outgoing.Status = "DM";//刪除(補登專用)
if (ModelFactory.DeleteOutgoing(Outgoing))
{
return(Json("1"));//代表已經處裡完
}
else
{
return(Json("失敗!"));//失敗
}
}
catch (Exception ex)
{
return(Json(ex.Message));
}
catch
{
return(Json("系統發生錯誤"));
}
}
public JsonResult GetOutManEventList(string Company, string OutMan, string SDATE, string EDATE)
{
List <OutgoingEventModel> M = ModelFactory.GetEventList(OutMan, SDATE, EDATE);
//將工作日寫入日歷中
MainModelFactory MainFactory = new MainModelFactory();
List <CalendarEventModel> HolidayList = new List <CalendarEventModel>();
HolidayList.AddRange(MainFactory.GetHolidayList(Company, SDATE, EDATE));
for (int i = 0; i < HolidayList.Count; i++)
{
OutgoingEventModel Cal = new OutgoingEventModel();
Cal.OutId = "";
Cal.id = HolidayList[i].id;
Cal.title = HolidayList[i].title;
Cal.start = HolidayList[i].start;
Cal.allDay = true;
Cal.self = true;
Cal.backgroundColor = HolidayList[i].backgroundColor;
Cal.color = HolidayList[i].color;
Cal.textColor = HolidayList[i].textColor;
M.Add(Cal);
}
return(Json(M));
}
/// <summary>
/// 取得該人的該張單據的最後一筆 外出單據 (重點是要取得UpdateDate UpdateTime)
/// </summary>
/// <param name="OutMan">外出人</param>
/// <param name="OutId">外出單號</param>
public OutgoingEventModel GetLastOutgoing(string OutId, string OutMan)
{
//只會取得一筆資料 因為使用的table是 gvOutgoing
string Sql = "SELECT OutId,OutMan,UpdateDate,UpdateTime from gvOutgoing WHERE OutId=@OutId AND OutMan=@OutMan ";
DataTable dt = DbAccess.ExecuteDataTable(Sql, new DbParameter[] {
DataAccess.CreateParameter("OutId", DbType.String, OutId),
DataAccess.CreateParameter("OutMan", DbType.String, OutMan)
}
);
if (dt.Rows.Count == 0)
{
throw new Exception("查無外出單據");
}
OutgoingEventModel Data = new OutgoingEventModel();
Data.OutId = dt.Rows[0]["OutId"].ToString();
Data.OutMan = dt.Rows[0]["OutMan"].ToString();
Data.UpdateDate = dt.Rows[0]["UpdateDate"].ToString();
Data.UpdateTime = dt.Rows[0]["UpdateTime"].ToString();
return(Data);
}
public List <OutgoingEventModel> GetOutHistory(string OutId, string OutMan)
{
List <OutgoingEventModel> DataList = new List <OutgoingEventModel>();
string Sql = " SELECT *,Convert(char(10),Convert(datetime,UpdateDate),20) as UpdateDateFormat,Convert(char(10),Convert(datetime,OutDate),20) as start,left(OutTime,2)+':'+right(OutTime,2) as StartTime,left(GoOutTime,2)+':'+right(GoOutTime,2) AS GoOutTimeFormat FROM gvOutHistory WHERE OutId=@OutId AND OutMan=@OutMan ";
DataTable dt = DbAccess.ExecuteDataTable(Sql, new DbParameter[] {
DataAccess.CreateParameter("OutId", DbType.String, OutId),
DataAccess.CreateParameter("OutMan", DbType.String, OutMan)
}
);
for (int i = 0; i < dt.Rows.Count; i++)
{
OutgoingEventModel M = new OutgoingEventModel();
M.UpdateDate = dt.Rows[i]["UpdateDateFormat"].ToString();
M.start = dt.Rows[i]["start"].ToString() + " " + dt.Rows[i]["StartTime"].ToString();
M.GoOutTime = dt.Rows[i]["GoOutTimeFormat"].ToString();
M.OutId = dt.Rows[i]["OutId"].ToString();
M.OutMan = dt.Rows[i]["OutMan"].ToString();
M.OutTime = dt.Rows[i]["OutTime"].ToString();
M.Location = dt.Rows[i]["Location"].ToString();
M.CustomerName = dt.Rows[i]["CustomerName"].ToString();
M.Status = dt.Rows[i]["Status"].ToString().Trim();
M.RecordMan = dt.Rows[i]["RecordManName"].ToString();
M.OutDescription = dt.Rows[i]["OutDescription"].ToString();
M.EmployeeName = dt.Rows[i]["EmployeeName"].ToString();
DataList.Add(M);
}
return(DataList);
}
public JsonResult EditOutgoing(AppOutgoingModel AppOutgoing)
{
JsonresultModel ResultModel = new JsonresultModel();
try
{
CheckOutDatetime(AppOutgoing.SDate, AppOutgoing.STime);
CheckOutDatetime(AppOutgoing.SDate, AppOutgoing.GoOutTime);
OutgoingEventModel Outgoing = new OutgoingEventModel();
OutgoingModelFactory ModelFactory = new OutgoingModelFactory();
EmpModel EMP = ModelFactory.GetOutMan(AppOutgoing.OutMan);
Outgoing.OutId = AppOutgoing.OutId;
Outgoing.OutMan = EMP.EmployeeNo;
Outgoing.Company = EMP.Company;
Outgoing.RecordMan = EMP.EmployeeNo;
Outgoing.Location = System.Web.HttpUtility.UrlDecode(AppOutgoing.Location);
Outgoing.CustomerName = System.Web.HttpUtility.UrlDecode(AppOutgoing.CustomerName);
Outgoing.OutDescription = "";
Outgoing.Status = "U";//編輯
Outgoing.OutDate = AppOutgoing.SDate.Replace("-", "");
Outgoing.OutTime = AppOutgoing.STime.Replace(":", "");
Outgoing.GoOutTime = AppOutgoing.GoOutTime.Replace(":", "");
Outgoing.Equipment = "1";
if (ModelFactory.EditOutgoing(Outgoing))
{
ResultModel.Result = "1";
return(Json(ResultModel, JsonRequestBehavior.AllowGet));//代表已經處裡完
}
else
{
ResultModel.Result = "0";
ResultModel.ErrorMsg = "編輯外出失敗";
return(Json(ResultModel, JsonRequestBehavior.AllowGet));//失敗
}
}
catch (Exception ex)
{
ResultModel.Result = "0";
ResultModel.ErrorMsg = ex.Message;
return(Json(ResultModel, JsonRequestBehavior.AllowGet));
}
catch
{
ResultModel.Result = "0";
ResultModel.ErrorMsg = "系統發生錯誤";
return(Json(ResultModel, JsonRequestBehavior.AllowGet));
}
}
public void GetHistoryViewList(string Where, string Orderby, int PageNum, int Limit, ref List <OutgoingEventModel> DataList, ref int TotalRecord)
{
int offset = PageNum * Limit;
//主要SQL
string SqlMain = "SELECT ROW_NUMBER() OVER (ORDER BY " + Orderby + ") AS RowNum,*,Convert(char(10),Convert(datetime,UpdateDate),20) as UpdateDateFormat,Convert(char(10),Convert(datetime,OutDate),20) as start,left(OutTime,2)+':'+right(OutTime,2) as StartTime,left(GoOutTime,2)+':'+right(GoOutTime,2) AS GoOutTimeFormat FROM gvOutgoing WHERE 1 = 1 " + Where;
string Sql = "SELECT TOP " + Limit + " * FROM ( ";
Sql += SqlMain;
Sql += " ) TEMP WHERE RowNum> " + offset;
DataTable dt = DbAccess.ExecuteDataTable(Sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
OutgoingEventModel M = new OutgoingEventModel();
M.UpdateDate = dt.Rows[i]["UpdateDateFormat"].ToString();
M.start = dt.Rows[i]["start"].ToString() + " " + dt.Rows[i]["StartTime"].ToString();
M.GoOutTime = dt.Rows[i]["GoOutTimeFormat"].ToString();
M.OutId = dt.Rows[i]["OutId"].ToString();
M.OutMan = dt.Rows[i]["OutMan"].ToString();
M.OutTime = dt.Rows[i]["OutTime"].ToString();
M.Location = dt.Rows[i]["Location"].ToString();
M.CustomerName = dt.Rows[i]["CustomerName"].ToString();
M.Status = dt.Rows[i]["Status"].ToString().Trim();
M.RecordMan = dt.Rows[i]["RecordManName"].ToString();
M.OutDescription = dt.Rows[i]["OutDescription"].ToString();
M.EmployeeName = dt.Rows[i]["EmployeeName"].ToString();
DataList.Add(M);
}
//取得總數量
Sql = "SELECT COUNT(*) AS TOTAL FROM (" + SqlMain + ") TEMP";
dt = DbAccess.ExecuteDataTable(Sql);
TotalRecord = Convert.ToInt32(dt.Rows[0]["TOTAL"].ToString());
}
public JsonResult DeleteOutgoing(string OutId, string EmpNo)
{
OutgoingEventModel Outgoing = null;
JsonresultModel ResultModel = new JsonresultModel();
try
{
Outgoing = new OutgoingEventModel();
Outgoing.OutId = OutId;
Outgoing.OutMan = EmpNo;
Outgoing.RecordMan = EmpNo;
Outgoing.Status = "D"; //刪除
Outgoing.Equipment = "1"; //App
OutgoingModelFactory ModelFactory = new OutgoingModelFactory();
if (ModelFactory.DeleteOutgoing(Outgoing))
{
ResultModel.Result = "1";
return(Json(ResultModel, JsonRequestBehavior.AllowGet));//代表已經處裡完
}
else
{
ResultModel.Result = "0";
ResultModel.ErrorMsg = "無法刪除外出記錄";
return(Json(ResultModel, JsonRequestBehavior.AllowGet));//失敗
}
}
catch (Exception ex)
{
ResultModel.Result = "0";
ResultModel.ErrorMsg = ex.Message;
return(Json(ResultModel, JsonRequestBehavior.AllowGet));
}
catch
{
ResultModel.Result = "0";
ResultModel.ErrorMsg = "系統發生錯誤";
return(Json(ResultModel, JsonRequestBehavior.AllowGet));
}
}
public JsonResult GetCalendarAllList(string Company, string EmpNo, string SDATE, string EDATE)
{
OutgoingModelFactory ModelFactory = new OutgoingModelFactory();
List <OutgoingEventModel> Box = null;
if (EmpNo == null || EmpNo == "")
{
//未登入-預設台北行事曆
Box = new List <OutgoingEventModel>();
Company = "TPE";
}
else
{
Box = ModelFactory.GetEventList(EmpNo, SDATE, EDATE);
}
//將工作日寫入日歷中
MainModelFactory MainFactory = new MainModelFactory();
List <CalendarEventModel> HolidayList = new List <CalendarEventModel>();
HolidayList.AddRange(MainFactory.GetHolidayList(Company, SDATE, EDATE));
for (int i = 0; i < HolidayList.Count; i++)
{
OutgoingEventModel Cal = new OutgoingEventModel();
Cal.OutId = "";
Cal.id = HolidayList[i].id;
Cal.title = HolidayList[i].title;
Cal.start = HolidayList[i].start;
Cal.allDay = true;
Cal.self = true;
Cal.backgroundColor = HolidayList[i].backgroundColor;
Cal.color = HolidayList[i].color;
Cal.textColor = HolidayList[i].textColor;
Box.Add(Cal);
}
return(Json(Box, JsonRequestBehavior.AllowGet));
}
public JsonResult EditOutgoingManage(string OutMan, string OutgoingJson)
{
OutgoingEventModel Outgoing = null;
try
{
Outgoing = JsonConvert.DeserializeObject <OutgoingEventModel>(OutgoingJson);
//檢查預約日期合不合法 , 若不合法 報錯!
CheckOutDatetime(Outgoing.OutDate, Outgoing.OutTime, true);
CheckOutDatetime(Outgoing.OutDate, Outgoing.GoOutTime, true);
EmpModel EMP = ModelFactory.GetOutMan(OutMan);
Outgoing.OutMan = EMP.EmployeeNo;
Outgoing.Company = EMP.Company;
Outgoing.RecordMan = LoginUserInfo.UserId;
Outgoing.Status = "UM";//編輯(補登專用)
Outgoing.OutDate = Outgoing.OutDate.Replace("-", "");
Outgoing.OutTime = Outgoing.OutTime.Replace(":", "");
Outgoing.GoOutTime = Outgoing.GoOutTime.Replace(":", "");
if (ModelFactory.EditOutgoing(Outgoing))
{
return(Json("1"));//代表已經處裡完
}
else
{
return(Json("失敗!"));//失敗
}
}
catch (Exception ex)
{
return(Json(ex.Message));
}
catch
{
return(Json("系統發生錯誤"));
}
}
public bool DeleteOutgoing(OutgoingEventModel Outgoing)
{
/*解除 停用
* //判斷是否有實際出門時間
* if (IsGoOut(Outgoing.OutId.ToString(), Outgoing.OutMan.ToString()))
* {
* throw new Exception("已有實際出門時間..無法刪除!");
* }
*/
if (Outgoing.Equipment == null || Outgoing.Equipment == "")
{
Outgoing.Equipment = "";
}
//源頭單據 準備壓回CheckUpdateDateTime
OutgoingEventModel OutOrder = GetLastOutgoing(Outgoing.OutId, Outgoing.OutMan);
//賦予更新時間
string UpdateDate = DateTime.Now.ToString("yyyyMMdd");
string UpdateTime = DateTime.Now.ToString("HHmmss");
string CheckUpdateDateTime = DateTime.Now.AddMinutes(-10).ToString("yyyyMMddHHmm");
DbTransaction objTrans = DbAccess.CreateDbTransaction();
try
{
string sql = "INSERT INTO Outgoing (OutId,OutDate,OutTime,RecordMan,OutMan,Location,CustomerName,Company,GoOutTime,Status,UpdateDate,UpdateTime,Equipment,CheckUpdateDateTime) "
+ " SELECT TOP 1 OutId,OutDate,OutTime,RecordMan,OutMan,Location,CustomerName,Company,GoOutTime,@Status,@UpdateDate,@UpdateTime,@Equipment,@CheckUpdateDateTime FROM gvOutgoing "
+ " WHERE OutId = @OutId AND OutMan = @OutMan Order by UpdateDate DESC,UpdateTime DESC ";
DbAccess.ExecuteNonQuery(sql, objTrans,
new DbParameter[] {
DataAccess.CreateParameter("OutId", DbType.String, Outgoing.OutId.ToString()),
DataAccess.CreateParameter("RecordMan", DbType.String, Outgoing.RecordMan.ToString()),
DataAccess.CreateParameter("OutMan", DbType.String, Outgoing.OutMan.ToString()),
DataAccess.CreateParameter("Status", DbType.String, Outgoing.Status.ToString()),//D或DM
DataAccess.CreateParameter("UpdateDate", DbType.String, UpdateDate),
DataAccess.CreateParameter("UpdateTime", DbType.String, UpdateTime),
DataAccess.CreateParameter("Equipment", DbType.String, Outgoing.Equipment),
DataAccess.CreateParameter("CheckUpdateDateTime", DbType.String, CheckUpdateDateTime),
});
//找回上一筆外出單據 並押上 本次更新時間
sql = "Update Outgoing SET CheckUpdateDateTime = @CheckUpdateDateTime WHERE OutId = @OutId AND OutMan = @OutMan AND UpdateDate = @UpdateDate AND UpdateTime = @UpdateTime ";
DbAccess.ExecuteNonQuery(sql, objTrans,
new DbParameter[] {
DataAccess.CreateParameter("OutId", DbType.String, OutOrder.OutId.ToString()),
DataAccess.CreateParameter("OutMan", DbType.String, OutOrder.OutMan.ToString()),
DataAccess.CreateParameter("UpdateDate", DbType.String, OutOrder.UpdateDate),
DataAccess.CreateParameter("UpdateTime", DbType.String, OutOrder.UpdateTime),
DataAccess.CreateParameter("CheckUpdateDateTime", DbType.String, CheckUpdateDateTime),
});
objTrans.Commit();
return(true);
}
catch
{
objTrans.Rollback();
return(false);
}
finally
{
if (objTrans != null)
{
objTrans.Dispose();
}
}
}
public bool EditOutgoing(OutgoingEventModel Outgoing)
{
/* 停用 解除
* //判斷是否有實際出門時間
* if (IsGoOut(Outgoing.OutId.ToString(), Outgoing.OutMan.ToString()))
* {
* throw new Exception("已有實際出門時間..無法編輯!");
* }
*/
//賦予更新時間
string UpdateDate = DateTime.Now.ToString("yyyyMMdd");
string UpdateTime = DateTime.Now.ToString("HHmmss");
string CheckUpdateDateTime = DateTime.Now.AddMinutes(-10).ToString("yyyyMMddHHmm");
if (Outgoing.Equipment == null || Outgoing.Equipment == "")
{
Outgoing.Equipment = "";
}
//源頭單據 準備壓回CheckUpdateDateTime
OutgoingEventModel OutOrder = GetLastOutgoing(Outgoing.OutId, Outgoing.OutMan);
DbTransaction objTrans = DbAccess.CreateDbTransaction();
try
{
DbAccess.ExecuteNonQuery("INSERT INTO Outgoing (OutId,OutDate,OutTime,RecordMan,OutMan,Location,CustomerName,Status,UpdateDate,UpdateTime,Company,OutDescription,GoOutTime,Equipment,CheckUpdateDateTime) VALUES (@OutId,@OutDate,@OutTime,@RecordMan,@OutMan,@Location,@CustomerName,@Status,@UpdateDate,@UpdateTime,@Company,@OutDescription,@GoOutTime,@Equipment,@CheckUpdateDateTime) ", objTrans,
new DbParameter[] {
DataAccess.CreateParameter("OutId", DbType.String, Outgoing.OutId.ToString()),
DataAccess.CreateParameter("OutDate", DbType.String, Outgoing.OutDate.ToString()),
DataAccess.CreateParameter("OutTime", DbType.String, Outgoing.OutTime.ToString()),
DataAccess.CreateParameter("RecordMan", DbType.String, Outgoing.RecordMan.ToString()),
DataAccess.CreateParameter("OutMan", DbType.String, Outgoing.OutMan.ToString()),
DataAccess.CreateParameter("Location", DbType.String, Outgoing.Location.ToString()),
DataAccess.CreateParameter("CustomerName", DbType.String, Outgoing.CustomerName.ToString()),
DataAccess.CreateParameter("Status", DbType.String, Outgoing.Status.ToString()),//U或UM
DataAccess.CreateParameter("UpdateDate", DbType.String, UpdateDate),
DataAccess.CreateParameter("UpdateTime", DbType.String, UpdateTime),
DataAccess.CreateParameter("Company", DbType.String, Outgoing.Company),
DataAccess.CreateParameter("OutDescription", DbType.String, Outgoing.OutDescription),
DataAccess.CreateParameter("GoOutTime", DbType.String, Outgoing.GoOutTime.ToString()),
DataAccess.CreateParameter("Equipment", DbType.String, Outgoing.Equipment.ToString()),
DataAccess.CreateParameter("CheckUpdateDateTime", DbType.String, CheckUpdateDateTime),
});
string sql = "Update Outgoing SET CheckUpdateDateTime = @CheckUpdateDateTime WHERE OutId = @OutId AND OutMan = @OutMan AND UpdateDate = @UpdateDate AND UpdateTime = @UpdateTime ";
DbAccess.ExecuteNonQuery(sql, objTrans,
new DbParameter[] {
DataAccess.CreateParameter("OutId", DbType.String, OutOrder.OutId.ToString()),
DataAccess.CreateParameter("OutMan", DbType.String, OutOrder.OutMan.ToString()),
DataAccess.CreateParameter("UpdateDate", DbType.String, OutOrder.UpdateDate),
DataAccess.CreateParameter("UpdateTime", DbType.String, OutOrder.UpdateTime),
DataAccess.CreateParameter("CheckUpdateDateTime", DbType.String, CheckUpdateDateTime),
});
objTrans.Commit();
return(true);
}
catch
{
objTrans.Rollback();
return(false);
}
finally
{
if (objTrans != null)
{
objTrans.Dispose();
}
}
}
