public int AddUpdateDelete(string mode, int rowid, string rampid, string validfrom, string validto, string timefrom, string timeto, int mon, int tue, int wed, int thu, int fri, int sat, int sun, int jumpable, string remark)
{
int result = 0;
if (mode.ToUpper() == "NEW" || mode.ToUpper() == "MODIFY")
{
result = TruckrampFuncs.DetectBlockResColision(mode, rowid, rampid, validfrom, validto, timefrom, timeto, mon, tue, wed, thu, fri, sat, sun);
}
if (result == 0)
{
SqlConnection conn = new SqlConnection(ConnString.Value);
SqlCommand cmd = new SqlCommand();
try
{
rampid = rampid.Trim();
timefrom = timefrom.Trim();
timeto = timeto.Trim();
remark = (remark.Trim() != "-" ? remark.Trim() : "");
cmd.Connection = conn;
cmd.Connection.Open();
cmd.Parameters.Add("@Rampid", SqlDbType.NVarChar).Value = rampid;
cmd.Parameters.Add("@Validfrom", SqlDbType.Date).Value = validfrom;
cmd.Parameters.Add("@Validto", SqlDbType.Date).Value = validto;
cmd.Parameters.Add("@Timefrom", SqlDbType.NVarChar).Value = timefrom;
cmd.Parameters.Add("@Timeto", SqlDbType.NVarChar).Value = timeto;
cmd.Parameters.Add("@Mon", SqlDbType.TinyInt).Value = mon;
cmd.Parameters.Add("@Tue", SqlDbType.TinyInt).Value = tue;
cmd.Parameters.Add("@Wed", SqlDbType.TinyInt).Value = wed;
cmd.Parameters.Add("@Thu", SqlDbType.TinyInt).Value = thu;
cmd.Parameters.Add("@Fri", SqlDbType.TinyInt).Value = fri;
cmd.Parameters.Add("@Sat", SqlDbType.TinyInt).Value = sat;
cmd.Parameters.Add("@Sun", SqlDbType.TinyInt).Value = sun;
cmd.Parameters.Add("@Jumpable", SqlDbType.TinyInt).Value = jumpable;
cmd.Parameters.Add("@Remark", SqlDbType.NVarChar).Value = remark;
cmd.Parameters.Add("@Rowid", SqlDbType.SmallInt).Value = rowid;
switch (mode.ToUpper())
{
case "NEW":
cmd.CommandText = "INSERT INTO truckramps_block (rampid,validfrom,validto,timefrom,timeto,mon,tue,wed,thu,fri,sat,sun,jumpable,remark) ";
cmd.CommandText = cmd.CommandText + "VALUES (@Rampid, @Validfrom, @Validto, @Timefrom, @Timeto, @Mon, @Tue, @Wed, @Thu, @Fri, @Sat, @Sun, @Jumpable, @Remark);";
cmd.CommandText = cmd.CommandText + "SELECT CAST(scope_identity() AS int) AS rowid";
result = (Int32)cmd.ExecuteScalar();
cmd.Dispose();
break;
case "MODIFY":
cmd.CommandText = "UPDATE truckramps_block SET rampid=@Rampid, validfrom=@Validfrom, validto=@Validto, timefrom=@Timefrom, ";
cmd.CommandText = cmd.CommandText + "timeto=@Timeto, mon=@Mon, tue=@Tue, wed=@Wed, thu=@Thu, ";
cmd.CommandText = cmd.CommandText + "fri=@Fri, sat=@Sat, sun=@Sun, jumpable=@Jumpable, remark=@Remark";
cmd.CommandText = cmd.CommandText + "' WHERE rowid=@Rowid";
cmd.ExecuteNonQuery();
cmd.Dispose();
break;
case "DELETE":
cmd.CommandText = "DELETE FROM truckramps_block WHERE rowid=@Rowid";
cmd.ExecuteNonQuery();
cmd.Dispose();
break;
}
}
catch
{
result = -12;
}
finally
{
conn.Close();
conn.Dispose();
}
}
return(result);
}
public static int DetectBlockResColision(string mode, int rowid, string rampid, string validfrom, string validto, string timefrom, string timeto, int mon, int tue, int wed, int thu, int fri, int sat, int sun)
{
DateTime date;
int result = 0, retval = 0;
for (int i = 0; i <= (DateTime.Parse(validto) - DateTime.Parse(validfrom)).Days; i++)
{
date = DateTime.Parse(validfrom).AddDays(i);
if ((date.DayOfWeek == DayOfWeek.Monday && mon == 1) || (date.DayOfWeek == DayOfWeek.Tuesday && tue == 1) || (date.DayOfWeek == DayOfWeek.Wednesday && wed == 1) ||
(date.DayOfWeek == DayOfWeek.Thursday && thu == 1) || (date.DayOfWeek == DayOfWeek.Friday && fri == 1) || (date.DayOfWeek == DayOfWeek.Saturday && sat == 1) ||
(date.DayOfWeek == DayOfWeek.Sunday && sun == 1))
{
//date = DateTime.ParseExact(validfrom + " 00:00:00", "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture).AddDays(i);
// Kontrola na koliziu s blokovanymi casami rampy
retval = TruckrampFuncs.BlockResColision("truckramps_block", rampid, date, timefrom, timeto, (mode.ToUpper() == "NEW" ? 0 : rowid));
switch (retval)
{
case 1:
// Zistena kolizia
result = -1;
break;
case -1:
// Vynimka v metode IsRampBlockTimeColision
result = -2;
break;
}
if (result == 0)
{
// Kontrola na koliziu s pevnymi rezervaciami rampy
retval = TruckrampFuncs.BlockResColision("sapvendors_res", rampid, date, timefrom, timeto, 0);
switch (retval)
{
case 1:
// Zistena kolizia
result = -3;
break;
case -1:
// Vynimka v metode IsSAPVendResTimeColision
result = -4;
break;
}
}
}
if (result == 0)
{
retval = WorkCalendar.IsCalWorkDay(date); // Zistenie, ci je den pracovny
switch (retval)
{
case 0:
// Den nie je pracovny podla pracovneho kalendara firmy, kontrola nie je potrebna
break;
case 1:
// Kontrola zaciatku blokovaneho casu na koliziu s definiciou pracovných smien rampy
retval = WorkCalendar.IsCalWorkDayShiftInDateTime(rampid, DateTime.ParseExact(date.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture) + " " + timefrom + ":00", "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture));
switch (retval)
{
case 0:
// V dany cas nie je v kalendary rampy pracovna smena (zistena kolizia)
result = -5;
break;
case 1:
break;
case -1:
// Vynimka v metode IsCalWorkDayShift
result = -6;
break;
case -2:
// Chybajuci pracovný kalendar smien rampy
result = -7;
break;
}
if (result == 0)
{
// Kontrola konca blokovaneho casu na koliziu s definiciou pracovných smien
retval = WorkCalendar.IsCalWorkDayShiftInDateTime(rampid, DateTime.ParseExact(date.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture) + " " + timeto + ":00", "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture).AddSeconds(-1));
switch (retval)
{
case 0:
// V dany cas nie je v kalendary rampy pracovna smena (zistena kolizia)
result = -8;
break;
case 1:
break;
case -1:
// Vynimka v metode IsCalWorkDayShift
result = -9;
break;
case -2:
// Chybajuci kalendar smien rampy
result = -10;
break;
}
}
break;
case -1:
// Vynimka v metode IsCalWorkDay
result = -11;
break;
}
}
if (result != 0)
{
break; // Opustenie cyklu for
}
}
return(result);
}
public int AddUpdateDelete(string mode, int rowid, string num, string rampid, string validfrom, string validto, string timefrom, string timeto, int mon, int tue, int wed, int thu, int fri, int sat, int sun)
{
int result = 0;
if (mode.ToUpper() == "NEW" || mode.ToUpper() == "MODIFY")
{
result = TruckrampFuncs.DetectBlockResColision(mode, rowid, rampid, validfrom, validto, timefrom, timeto, mon, tue, wed, thu, fri, sat, sun);
}
if (result == 0 && (mode.ToUpper() == "NEW" || mode.ToUpper() == "MODIFY"))
{
// Tento dodavatel sa na tejto rampe nevyklada! (1) (pripad, ze firmu, co ma vyhradenu svoju jednu rampu alebo viac ramp, sa pokusame zapisat na inu rampu)
result = TruckrampFuncs.AllowReservationVendOnRamp(1, rampid, num) == true ? 0 : -98;
}
if (result == 0 && (mode.ToUpper() == "NEW" || mode.ToUpper() == "MODIFY"))
{
// Tento dodavatel sa na tejto rampe nevyklada! (2) (pripad, ze firmu, co nema vyhradenu ziadnu svoju rampu, sa pokusame zapisat na vyhradenu rampu inych firiem)
result = TruckrampFuncs.AllowReservationVendOnRamp(2, rampid, num) == true ? 0 : -99;
}
if (result == 0)
{
SqlConnection conn = new SqlConnection(ConnString.Value);
SqlCommand cmd = new SqlCommand();
try
{
num = num.Trim();
rampid = rampid.Trim();
timefrom = timefrom.Trim();
timeto = timeto.Trim();
cmd.Connection = conn;
cmd.Connection.Open();
cmd.Parameters.Add("@Num", SqlDbType.NVarChar).Value = num;
cmd.Parameters.Add("@Rampid", SqlDbType.NVarChar).Value = rampid;
cmd.Parameters.Add("@Validfrom", SqlDbType.Date).Value = validfrom;
cmd.Parameters.Add("@Validto", SqlDbType.Date).Value = validto;
cmd.Parameters.Add("@Timefrom", SqlDbType.NVarChar).Value = timefrom;
cmd.Parameters.Add("@Timeto", SqlDbType.NVarChar).Value = timeto;
cmd.Parameters.Add("@Mon", SqlDbType.TinyInt).Value = mon;
cmd.Parameters.Add("@Tue", SqlDbType.TinyInt).Value = tue;
cmd.Parameters.Add("@Wed", SqlDbType.TinyInt).Value = wed;
cmd.Parameters.Add("@Thu", SqlDbType.TinyInt).Value = thu;
cmd.Parameters.Add("@Fri", SqlDbType.TinyInt).Value = fri;
cmd.Parameters.Add("@Sat", SqlDbType.TinyInt).Value = sat;
cmd.Parameters.Add("@Sun", SqlDbType.TinyInt).Value = sun;
cmd.Parameters.Add("@Rowid", SqlDbType.SmallInt).Value = rowid;
switch (mode.ToUpper())
{
case "NEW":
cmd.CommandText = "INSERT INTO sapvendors_res (num,rampid,validfrom,validto,timefrom,timeto,mon,tue,wed,thu,fri,sat,sun) VALUES (@Num, @Rampid, ";
cmd.CommandText = cmd.CommandText + "@Validfrom, @Validto, @Timefrom, @Timeto, @Mon, @Tue, @Wed, @Thu, @Fri, @Sat, @Sun);";
cmd.CommandText = cmd.CommandText + "SELECT CAST(scope_identity() AS int) AS rowid";
result = (Int32)cmd.ExecuteScalar();
break;
case "MODIFY":
cmd.CommandText = "UPDATE sapvendors_res SET num=@Num, rampid=@Rampid, validfrom=@Validfrom, validto=@Validto, timefrom=@Timefrom, timeto=@Timeto, ";
cmd.CommandText = cmd.CommandText + "mon=@Mon, tue=@Tue, wed=@Wed, thu=@Thu, fri=@Fri, sat=@Sat, sun=@Sun WHERE rowid=" + rowid.ToString();
cmd.ExecuteNonQuery();
break;
case "DELETE":
cmd.CommandText = "DELETE FROM sapvendors_res WHERE rowid=@Rowid";
cmd.ExecuteNonQuery();
break;
}
cmd.Dispose();
}
catch
{
result = -12;
}
finally
{
conn.Close();
conn.Dispose();
}
}
return(result);
}
