public bool CheckIfFileAlreadyImported(ResidentRollCall residentRollCall)
{
DataTable dt = dbc.GetDataTable("SELECT * FROM ResidentRollCallImportLog WHERE FileName ='" + residentRollCall.FileName + "'");
// dbc.CloseConnection();
return(dt.Rows.Count != 0);
}
private string CheckAllRollValues(ResidentRollCall residenceRollCall)
{
List <string> errors = new List <string>();
List <string> properties = CommonFunctions.GetAllPropertiesFromType(residenceRollCall);
foreach (DataRow dr in residenceRollCall.RollTable.Rows)
{
foreach (string property in properties)
{
string numericValue = property.Replace("d", "");
if (CommonFunctions.IsNumeric(numericValue))
{
if (CommonFunctions.IsDate(residenceRollCall.YearNumber, residenceRollCall.MonthNumber, Convert.ToInt16(numericValue)))
{
string str = dr[numericValue].ToString();
if (str != string.Empty)
{
if (!CheckIfValueAcceptables(str))
{
errors.Add(str);
}
}
}
}
}
}
if (errors.Count > 0)
{
return(String.Join(", ", errors.ToArray()));
}
else
{
return(string.Empty);
}
}
private ResidentRollCall GetRollCall(DataRow dr)
{
ResidentRollCall rrc = new ResidentRollCall();
rrc.ResidentRollCallId = (int)dr["ResidentRollCallId"];
rrc.StudentId = (int)dr["StudentId"];
rrc.ResidentName = dr["ResidentName"].ToString();
rrc.LocationName = dr["LocationName"].ToString();
rrc.Abbreviation = dr["Abbreviation"].ToString();
rrc.MonthNumber = (int)dr["MonthNumber"];
rrc.YearNumber = (int)dr["YearNumber"];
rrc.FileName = dr["FileName"].ToString();
rrc.RollReference = dr["RollReference"].ToString();
rrc.d1 = dr["1"].ToString();
rrc.d2 = dr["2"].ToString();
rrc.d3 = dr["3"].ToString();
rrc.d4 = dr["4"].ToString();
rrc.d5 = dr["5"].ToString();
rrc.d6 = dr["6"].ToString();
rrc.d7 = dr["7"].ToString();
rrc.d8 = dr["8"].ToString();
rrc.d9 = dr["9"].ToString();
rrc.d10 = dr["10"].ToString();
rrc.d11 = dr["11"].ToString();
rrc.d12 = dr["12"].ToString();
rrc.d13 = dr["13"].ToString();
rrc.d14 = dr["14"].ToString();
rrc.d15 = dr["15"].ToString();
rrc.d16 = dr["16"].ToString();
rrc.d17 = dr["17"].ToString();
rrc.d18 = dr["18"].ToString();
rrc.d19 = dr["19"].ToString();
rrc.d20 = dr["20"].ToString();
rrc.d21 = dr["21"].ToString();
rrc.d22 = dr["22"].ToString();
rrc.d23 = dr["23"].ToString();
rrc.d24 = dr["24"].ToString();
rrc.d25 = dr["25"].ToString();
rrc.d26 = dr["26"].ToString();
rrc.d27 = dr["27"].ToString();
rrc.d28 = dr["28"].ToString();
rrc.d29 = dr["29"].ToString();
rrc.d30 = dr["30"].ToString();
rrc.d31 = dr["31"].ToString();
return(rrc);
}
private string ValidateAllStudentIds(ResidentRollCall residenceRollCall)
{
List <string> list = new List <string>();
foreach (DataRow dr in residenceRollCall.RollTable.Rows)
{
string studentid = dr[0].ToString();
DataTable dt = dbc.GetDataTable("SELECT StudentId FROM student where StudentId=" + studentid);
if (dt.Rows.Count == 0)
{
list.Add(studentid);
}
}
if (list.Count > 0)
{
return(String.Join(", ", list.ToArray()));
}
else
{
return(string.Empty);
}
}
public void Update(ResidentRollCall rollCall)
{
//PropertyInfo[] properties = GetRollCallProperties();
List <string> list = new List <string>();
StringBuilder sb = new StringBuilder();
sb.Append("UPDATE ResidentRollCall Set ");
for (int i = 1; i <= 31; i++)
{
string propertyName = "d" + i;
var rollValue = rollCall.GetType().GetProperty(propertyName).GetValue(rollCall, null);
//var rollValue = rollCall.GetType().GetProperties()..GetValue(property.Name, null);
if (rollValue != null && rollValue.ToString() != string.Empty)
{
if (CheckIfValueAcceptables(rollValue.ToString()))
{
list.Add(i + "='" + rollValue + "'");
//sb.Append("d" + i + "='" + rollValue + "'");
}
else
{
throw new Exception(rollValue.ToString() + " not acceptable");
}
}
else
{
list.Add(i + "=null");
}
//sb.Append(i + "=null");
}
sb.Append(String.Join(",", list.ToArray()));
sb.Append(" WHERE ResidentRollCallId=" + rollCall.ResidentRollCallId);
string sql = sb.ToString();
dbc.ExecuteCommand(sql);
//StringBuilder sb = new StringBuilder();
//sb.Append("UPDATE ResidentRollCall Set (");
//sb.Append("1='" + rollCall.d1 + "'");
//sb.Append("2='" + rollCall.d2 + "'");
//sb.Append("3='" + rollCall.d2 + "'");
//sb.Append("4='" + rollCall.d4 + "'");
//sb.Append("5='" + rollCall.d5 + "'");
//sb.Append("5='" + rollCall.d6 + "'");
//sb.Append("7='" + rollCall.d7 + "'");
//sb.Append("8='" + rollCall.d8 + "'");
//sb.Append("9='" + rollCall.d9 + "'");
//sb.Append("10='" + rollCall.d10 + "'");
//sb.Append("11='" + rollCall.d11 + "'");
//sb.Append("12='" + rollCall.d12 + "'");
//sb.Append("13='" + rollCall.d13 + "'");
//sb.Append("14='" + rollCall.d14 + "'");
//sb.Append("15='" + rollCall.d15 + "'");
//sb.Append("16='" + rollCall.d16 + "'");
//sb.Append("17='" + rollCall.d17 + "'");
//sb.Append("18='" + rollCall.d18 + "'");
//sb.Append("19='" + rollCall.d19 + "'");
//sb.Append("20='" + rollCall.d20 + "'");
//sb.Append("21='" + rollCall.d21 + "'");
//sb.Append("22='" + rollCall.d22 + "'");
//sb.Append("23='" + rollCall.d23 + "'");
//sb.Append("24='" + rollCall.d21 + "'");
//sb.Append("25='" + rollCall.d21 + "'");
//sb.Append("26='" + rollCall.d21 + "'");
//sb.Append("27='" + rollCall.d21 + "'");
//sb.Append("28='" + rollCall.d21 + "'");
}
public void SaveRollCalll(ResidentRollCall residenceRollCall)
{
string rtn = ValidateAllStudentIds(residenceRollCall);
if (rtn != string.Empty)
{
throw new Exception(rtn + " not valid Student Id(s)");
}
rtn = CheckAllRollValues(residenceRollCall);
if (rtn != string.Empty)
{
throw new Exception(rtn + " values not acceptable, please check spread sheet");
}
string sql = "INSERT INTO ResidentRollCallImportLog (FileLocation, FileName) VALUES ('" + residenceRollCall.FileLocation + "','" + residenceRollCall.FileName + "')";
int ResidentRollCallImportId = dbc.ExecuteCommand(sql);
foreach (DataRow dr in residenceRollCall.RollTable.Rows)
{
string studentId = dr[0].ToString();
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO ResidentRollCall (StudentId,MonthNumber,YearNumber,LocationId,RollReference,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,ResidentRollCallImportId) VALUES (");
sb.Append(dr[0].ToString());
sb.Append(",'" + residenceRollCall.MonthNumber + "'");
sb.Append(",'" + residenceRollCall.YearNumber + "'");
sb.Append(",'" + residenceRollCall.LocationId + "'");
sb.Append(",'" + residenceRollCall.LocationReference + "'");
sb.Append(",'" + dr["2"].ToString() + "'");
sb.Append(",'" + dr["2"].ToString() + "'");
sb.Append(",'" + dr["3"].ToString() + "'");
sb.Append(",'" + dr["4"].ToString() + "'");
sb.Append(",'" + dr["5"].ToString() + "'");
sb.Append(",'" + dr["6"].ToString() + "'");
sb.Append(",'" + dr["7"].ToString() + "'");
sb.Append(",'" + dr["8"].ToString() + "'");
sb.Append(",'" + dr["9"].ToString() + "'");
sb.Append(",'" + dr["10"].ToString() + "'");
sb.Append(",'" + dr["11"].ToString() + "'");
sb.Append(",'" + dr["12"].ToString() + "'");
sb.Append(",'" + dr["13"].ToString() + "'");
sb.Append(",'" + dr["14"].ToString() + "'");
sb.Append(",'" + dr["15"].ToString() + "'");
sb.Append(",'" + dr["16"].ToString() + "'");
sb.Append(",'" + dr["17"].ToString() + "'");
sb.Append(",'" + dr["18"].ToString() + "'");
sb.Append(",'" + dr["19"].ToString() + "'");
sb.Append(",'" + dr["20"].ToString() + "'");
sb.Append(",'" + dr["21"].ToString() + "'");
sb.Append(",'" + dr["22"].ToString() + "'");
sb.Append(",'" + dr["23"].ToString() + "'");
sb.Append(",'" + dr["24"].ToString() + "'");
sb.Append(",'" + dr["25"].ToString() + "'");
sb.Append(",'" + dr["26"].ToString() + "'");
sb.Append(",'" + dr["27"].ToString() + "'");
sb.Append(",'" + dr["28"].ToString() + "'");
if (residenceRollCall.RollTable.Columns.Contains("29"))
{
sb.Append(",'" + dr["29"].ToString() + "'");
}
else
{
sb.Append(",null");
}
if (residenceRollCall.RollTable.Columns.Contains("30"))
{
sb.Append(",'" + dr["30"].ToString() + "'");
}
else
{
sb.Append(",null");
}
if (residenceRollCall.RollTable.Columns.Contains("31"))
{
sb.Append(",'" + dr["31"].ToString() + "'");
}
else
{
sb.Append(",null");
}
sb.Append("," + ResidentRollCallImportId);
sb.Append(")");
sql = sb.ToString();
dbc.ExecuteCommand(sql);
}
}
