private String BuildInsertCommand(ApplyShift AppS)
{
String command;
StringBuilder sb = new StringBuilder();
int day = AppS.Shiftdate.Day;
int month = AppS.Shiftdate.Month;
int year = AppS.Shiftdate.Year;
string shiftdate = month.ToString() + "/" + day.ToString() + "/" + year.ToString();
string Unitid = AppS.Unitid.ToString();
string isApply = AppS.Isaplly1.ToString();
sb.AppendFormat("Values('{0}', '{1}','{2}', '{3}', '{4}', '{5}')", AppS.Userid, AppS.Comment, Unitid, AppS.Shifttype, shiftdate, isApply);
String prefix = "INSERT INTO BlockShift_2020 " + "(UserId,Comments,UnitId,ShiftType,ShiftDate,isApply)";
command = prefix + sb.ToString();
return(command);
}
public List <ApplyShift> GetApplyShift(int id)
{
List <ApplyShift> AS = new List <ApplyShift>();
SqlConnection con = null;
string today = DateTime.Today.ToString("yyyy-MM-dd");
string unitId = id.ToString();
try
{
con = connect("DBConnectionString");
String selectSTR = "select BlockShift_2020.ShiftType,BlockShift_2020.UserId, BlockShift_2020.ShiftDate, BlockShift_2020.UserId,BlockShift_2020.isApply, BlockShift_2020.Comments, User_2020.FirstName, User_2020.LastName";
selectSTR += " from BlockShift_2020 left join Shift_2020 on BlockShift_2020.ShiftDate = Shift_2020.ShiftDate and BlockShift_2020.ShiftType = Shift_2020.ShiftType left join User_2020 on BlockShift_2020.UserId = User_2020.UserId";
selectSTR += " where BlockShift_2020.UnitId = " + id + " AND Shift_2020.StartPeriod >'" + today + "'";
SqlCommand cmd = new SqlCommand(selectSTR, con);
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
ApplyShift Applyshift = new ApplyShift();
Applyshift.Userid = Convert.ToString(dr["UserId"]);
Applyshift.Name = Convert.ToString(dr["FirstName"]) + " " + Convert.ToString(dr["LastName"]);
Applyshift.Shiftdate = Convert.ToDateTime(dr["ShiftDate"]).Date;
Applyshift.Shifttype = Convert.ToString(dr["ShiftType"]);
Applyshift.Isaplly1 = Convert.ToBoolean(dr["isApply"]);
Applyshift.Comment = Convert.ToString(dr["Comments"]);
AS.Add(Applyshift);
}
return(AS);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
if (con != null)
{
con.Close();
}
}
}
// PUT api/<controller>/5
public void Put([FromBody] ApplyShift AS)
{
AS.UpdateApplyShift();
}
// POST api/<controller>
public void Post([FromBody] List <ApplyShift> AS)
{
ApplyShift appS = new ApplyShift();
appS.InsertApplyShift(AS);
}
public List <ApplyShift> Get(string User, string Unit)
{
ApplyShift appS = new ApplyShift();
return(appS.GetApplyShift(Unit, User));
}
public List <ApplyShift> Get(int Unitid)
{
ApplyShift appS = new ApplyShift();
return(appS.GetAS(Unitid));
}
