//USERINSERT
public int UserInsert(String Name, string EmployeeId, string Contact, string UserLocation, string EmailId, string Role)
{
DynamicParameters con = new DynamicParameters();
con.Add("@Name", Name);
con.Add("@EmployeeID", EmployeeId);
con.Add("@ContactNumber", Contact);
con.Add("@UserLocation", UserLocation);
con.Add("@EmailId", EmailId);
con.Add("@RoleId", Role);
string sqlQuery = "sp_userinsert";
int rowsEffected = DAO.Execute(commandType: System.Data.CommandType.StoredProcedure, sql: sqlQuery, parameters: con);
return(rowsEffected);
}
public ResponseObject <User> CreateUser(User user)
{
ResponseObject <User> response = new ResponseObject <User>();
try
{
var userIDQuery = $"SELECT ID FROM usertable ORDER BY ID DESC;";
int lastID = dapperRepository.Query <int>(userIDQuery).FirstOrDefault();
user.Username = user.Name + user.Surname;
user.ID = lastID == 0 ? 1 : lastID + 1;
user.Password = Cryptography_Algorithms.Calculate_SHA256(user.Password, user.Username + user.Password);
var userInsertQuery = $"INSERT INTO usertable (ID,Username,Name,Surname,Password,Yetki,SirketKodu) " +
$"VALUES ({user.ID},'{user.Username}','{user.Name}','{user.Surname}','{user.Password}','{user.Yetki}','{user.SirketKodu}')";
dapperRepository.Execute(userInsertQuery);
response.IsSuccess = true;
response.StatusCode = "200";
response.Explanation = "success";
response.Object = user;
}
catch (Exception ex)
{
response.IsSuccess = false;
response.Explanation = ex.Message;
response.StatusCode = "400";
}
return(response);
}
public int Register(string Name, string EmployeeID, string Password, string UserLocation, string EmailId)
{
string storedProc = "sp_Registration";
DynamicParameters param = new DynamicParameters();
param.Add("@Name", Name);
param.Add("@EmployeeID", EmployeeID);
param.Add("@Password", Password);
param.Add("@UserLocation", UserLocation);
param.Add("@EmailId", EmailId);
param.Add("@return", dbType: System.Data.DbType.Int32, direction: System.Data.ParameterDirection.ReturnValue);
dao.Execute(commandType: System.Data.CommandType.StoredProcedure, sql: storedProc, parameters: param);
int retVal = param.Get <int>("return");
return(retVal);
}
public Response <bool> goWork(List <StaffWorkInfo> model)
{
Response <bool> rsp = new Response <bool>();
List <StaffOnlie> entity = new List <StaffOnlie>();
List <StaffOnlineHis> entity_his = new List <StaffOnlineHis>();
foreach (var item in model)
{
StaffOnlie obj = new StaffOnlie();
StaffOnlineHis obj_his = new StaffOnlineHis();
obj.empNo = item.empno;
obj.empName = item.empname;
obj.line = item.line;
obj.online_time = DateTime.Now;
obj.shift_no = item.shift;
obj.old_line = item.line;
obj.work_date = DateTime.Now.ToString("yyyy-MM-dd");
obj.Status_flag = "0";
obj_his.empNo = item.empno;
obj_his.line = item.line;
obj_his.work_date = DateTime.Now.ToString("yyyy-MM-dd");
entity.Add(obj);
entity_his.Add(obj_his);
}
List <KeyValuePair <string, object> > cmd = new List <KeyValuePair <string, object> >();
try
{
string sql = @"insert into mfg_onlinewip
(work_date, empNo, empName, line, Status_flag, online_time, shift_no, old_line)
VALUES (@work_date, @empNo, @empName, @line, @Status_flag, @online_time, @shift_no, @old_line)";
string sql_his = "insert into mfg_onlinehistory(work_date, empNo, empName, line, Status_flag, online_time, offline_time, offline_Mark, shift_no, ligang_time, huigang_time, ligang_mark, old_line, new_line, new_line_time) (select work_date, empNo, empName, line, Status_flag, online_time, offline_time, offline_Mark, shift_no, ligang_time, huigang_time, ligang_mark, old_line, new_line, new_line_time from mfg_onlinewip where work_date=@work_date and line=@line and empNo=@empno ) ";
cmd.Add(new KeyValuePair <string, object>(sql, entity));
cmd.Add(new KeyValuePair <string, object>(sql_his, entity_his));
rsp.data = DapperRepository.Execute(cmd);
if (!rsp.data)
{
rsp.flag = 0;
}
}
catch (Exception ex)
{
rsp.flag = 0;
rsp.msg = ex.Message.ToString();
}
return(rsp);
}
public int insertToken(int SRID)
{
string storedProc = "sp_insertToken";
string guid = generateGUID();
DynamicParameters param = new DynamicParameters();
param.Add("@guid", guid);
param.Add("@ServiceRequest_ID", SRID);
param.Add("@Status_ID", 1);
param.Add("@CreatedTimeStamp", DateTime.Now);
param.Add("@LastModifiedTimeStamp", DateTime.Now);
param.Add("@MetaActive", 1);
int retVal = dao.Execute(System.Data.CommandType.StoredProcedure, sql: storedProc, parameters: param);
sendMailToUser(SRID);
sendMailToApprover(SRID, guid, 1);
return(retVal);
}
//USERINSERT
public int AssetsInsert(string SystemName, string SystemIP, string SerialNumber, string MachineCode, string MachineType, DateTime RegistrationDate, DateTime ExpirationDate)
{
DynamicParameters con = new DynamicParameters();
con.Add("@SystemName", SystemName);
con.Add("@SystemIP", SystemIP);
con.Add("@SerialNumber", SerialNumber);
con.Add("@MachineCode", MachineCode);
con.Add("@MachineType", MachineType);
con.Add("@RegistrationDate", RegistrationDate);
con.Add("@ExpirationDate", ExpirationDate);
string sqlQuery = "sp_Assetsinsert";
int rowsEffected = dao.Execute(commandType: System.Data.CommandType.StoredProcedure, sql: sqlQuery, parameters: con);
return(rowsEffected);
}
//回岗
public Response <bool> returnpost(List <StaffWorkInfo> model)
{
Response <bool> rsp = new Response <bool>();
List <StaffOnlie> entity = new List <StaffOnlie>();
List <StaffOnlineHis> entity_his = new List <StaffOnlineHis>();
List <OffWorkDetail> offWorkDetails = new List <OffWorkDetail>();
foreach (var item in model)
{
StaffOnlie obj = new StaffOnlie();
StaffOnlineHis obj_his = new StaffOnlineHis();
OffWorkDetail offwork = new OffWorkDetail();
obj.empNo = item.empno;
obj.empName = item.empname;
obj.line = item.new_line;
obj.shift_no = item.shift;
obj.online_time = DateTime.Now;
obj.huigang_time = DateTime.Now;
obj.old_line = item.line;
obj.work_date = DateTime.Now.ToString("yyyy-MM-dd");
obj.Status_flag = "0";
obj_his.empNo = item.empno;
obj_his.line = item.new_line;
obj_his.work_date = DateTime.Now.ToString("yyyy-MM-dd");
offwork.emp_no = item.empno;
offwork.work_date = DateTime.Now.ToString("yyyy-MM-dd");
offwork.ligang_time = item.ligang_time;
offwork.huigang_time = DateTime.Now;
offwork.ligang_time_length = DateTime.Now.Subtract(item.ligang_time).TotalSeconds;
offwork.shift_no = item.shift;
entity.Add(obj);
entity_his.Add(obj_his);
offWorkDetails.Add(offwork);
}
List <KeyValuePair <string, object> > cmd = new List <KeyValuePair <string, object> >();
try
{
string sql = @"update mfg_onlinewip set huigang_time=@huigang_time,Status_flag=@Status_flag
where work_date=@work_date and empNo=@empNo and line=@old_line ";
string sql_his = "insert into mfg_onlinehistory(work_date, empNo, empName, line, Status_flag, online_time, offline_time, offline_Mark, shift_no, ligang_time, huigang_time, ligang_mark, old_line, new_line, new_line_time) (select work_date, empNo, empName, line, Status_flag, online_time, offline_time, offline_Mark, shift_no, ligang_time, huigang_time, ligang_mark, old_line, new_line, new_line_time from mfg_onlinewip where work_date=@work_date and line=@line and empNo=@empno ) ";
string sql_record = @"INSERT into mfg_ligang_total(emp_no, work_date, ligang_ci, ligang_time, huigang_time, ligang_time_length, shift_no) VALUES
(@emp_no, @work_date, @ligang_ci, @ligang_time, @huigang_time, @ligang_time_length, @shift_no)";
cmd.Add(new KeyValuePair <string, object>(sql, entity));
cmd.Add(new KeyValuePair <string, object>(sql_his, entity_his));
cmd.Add(new KeyValuePair <string, object>(sql_record, offWorkDetails));
rsp.data = DapperRepository.Execute(cmd);
if (!rsp.data)
{
rsp.flag = 0;
}
}
catch (Exception ex)
{
rsp.flag = 0;
rsp.msg = ex.Message.ToString();
}
return(rsp);
}
public int generateSRNumber(string SRDescription, int AssetUsageType_ID, DateTime FromDate,
DateTime TillDate, int User_ID, string UserLocation, string ContactNumber)
{
DynamicParameters param = new DynamicParameters();
param.Add("@SRDescription", SRDescription);
param.Add("@AssetUsageType_ID", AssetUsageType_ID);
param.Add("@FromDate", FromDate);
param.Add("@TillDate", TillDate);
param.Add("@User_ID", User_ID);
param.Add("@UserLocation", UserLocation);
param.Add("@ContactNumber", ContactNumber);
param.Add("@CreatedTimeStamp", DateTime.Now);
param.Add("@LastModifiedTimeStamp", DateTime.Now);
param.Add("@MetaActive", 1);
param.Add("@SRID", dbType: System.Data.DbType.Int32, direction: System.Data.ParameterDirection.Output);
string sqlQuery = "sp_ServiceRequest";
dao.Execute(commandType: System.Data.CommandType.StoredProcedure, sql: sqlQuery, parameters: param);
int SRID = param.Get <int>("SRID");
return(SRID);
}