public string AddNewMail(MailData aMailData)
{
string insertString = "INSERT INTO maildata (`MailID`, `MailDescription`,`DateArrival`,`DateDeparture`,`MailToID`,`MailFromID`) VALUES ('" + aMailData.MailID + "', '" + aMailData.MailDescription + "','" + aMailData.MailArrivalDate.ToString("yyyy-MM-dd") + "','" + aMailData.MailDepartureDate.ToString("yyyy-MM-dd") + "'," + aMailData.MailTo + "," + aMailData.MailFrom + "); ";
Hashtable aHashtable = new Hashtable()
{
{"MailID",aMailData.MailID},
{"mailDescription",aMailData.MailDescription},
{"DateArrival",aMailData.MailArrivalDate.ToString("yyyy-MM-dd")},
{"DateDeparture",aMailData.MailDepartureDate.ToString("yyyy-MM-dd")},
{"MailTo",aMailData.MailTo},
{"MailFrom",aMailData.MailFrom}
};
int lastInsertId = aGateway.Insert(insertString, aHashtable);
return "Data Successfully Inserted.";
}
public string EditMail(MailData aMailData)
{
string updateQuery = "UPDATE maildata SET MailID='"+aMailData.MailID+"', MailDescription='"+aMailData.MailDescription+
"',MailFromID='"+aMailData.MailFrom+"',MailToID='"+aMailData.MailTo+"'";
if (aMailData.MailArrivalDate.ToString("dd/MM/yyyy") != "01/01/0001")
{
updateQuery += ",DateArrival='"+aMailData.MailArrivalDate.ToString("yyyy-MM-dd")+"'";
}
if (aMailData.MailDepartureDate.ToString("dd/MM/yyyy") != "01/01/0001")
{
updateQuery += ",DateDeparture='" + aMailData.MailDepartureDate.ToString("yyyy-MM-dd") + "'";
}
updateQuery += " WHERE ID='"+aMailData.MailDBID+"';";
string message = aGateway.Update(updateQuery);
return message;
}
public List<MailData> GetMailData(string mailId, string mailDescription,
string mailFrom, string mailTo, string dateArrivalFrom, string dateArrivalTo,int start, int length,out int totalRecords)
{
string query = "select m.ID,m.MailID,m.MailDescription,s1.Value as MailFrom," +
"s2.Value as MailTo,m.DateArrival,m.DateDeparture from maildata m left join menustations s1 on " +
"s1.ID=m.MailFromID left join menustations s2 on s2.ID=m.MailToID where ";
if (mailId.Trim().Length != 0)
{
query += " m.MailID like '%" + mailId + "%' and ";
}
if (mailDescription.Trim().Length != 0)
{
query += " m.MailDescription like '%" + mailDescription + "%' and ";
}
if (mailFrom.Trim().Length != 0)
{
query += " s1.Value like '%" + mailFrom + "%' and ";
}
if (mailTo.Trim().Length != 0)
{
query += " s2.Value like '%" + mailTo + "%' and ";
}
if (dateArrivalFrom.Trim().Length != 0)
{
if (dateArrivalTo.Trim().Length != 0)
{
dateArrivalFrom = Convert.ToDateTime(dateArrivalFrom).ToString("yyyy-MM-dd");
dateArrivalTo = Convert.ToDateTime(dateArrivalTo).ToString("yyyy-MM-dd");
query += " m.DateArrival between " + dateArrivalFrom + " and " + dateArrivalTo + " and ";
}
else
{
dateArrivalFrom = Convert.ToDateTime(dateArrivalFrom).ToString("yyyy-MM-dd");
query += " m.DateArrival between " + dateArrivalFrom + " and " + dateArrivalFrom + " and ";
}
}
if (mailId.Trim().Length == 0 && mailDescription.Trim().Length == 0
&& mailFrom.Trim().Length == 0 && mailTo.Trim().Length == 0
&& dateArrivalFrom.Trim().Length == 0)
{
query = query.Trim().Substring(0, query.Trim().LastIndexOf("where"));
}
else
{
query = query.Trim().Substring(0, query.Trim().LastIndexOf("and"));
}
string totalRecordsQuery = query;
query += " order by m.DateArrival limit " + start + "," + length + ";";
List<MailData> aList = new List<MailData>();
DataSet aSet = aGateway.Select(query);
foreach (DataRow dataRow in aSet.Tables[0].Rows)
{
MailData aData = new MailData();
aData.MailDBID = dataRow["ID"].ToString();
aData.MailID = dataRow["MailID"].ToString();
aData.MailDescription = dataRow["MailDescription"].ToString();
aData.MailFrom = dataRow["MailFrom"].ToString();
aData.MailTo = dataRow["MailTo"].ToString();
aData.MailArrivalDate = Convert.ToDateTime(dataRow["DateArrival"].ToString());
aData.MailDepartureDate = Convert.ToDateTime(dataRow["DateDeparture"].ToString());
aList.Add(aData);
}
aSet = aGateway.Select("select count(*) as TotalRecords from mailData;");
totalRecords = Convert.ToInt32(aSet.Tables[0].Rows[0]["TotalRecords"].ToString());
return aList;
}
public List<MailData> SingleMailInfo(int id)
{
List<MailData> aMailData = new List<MailData>();
string query = "select m.ID,m.MailID,m.MailDescription,s1.Value as MailFrom," +
"s2.Value as MailTo,m.DateArrival,m.DateDeparture from maildata m left join menustations s1 on " +
"s1.ID=m.MailFromID left join menustations s2 on s2.ID=m.MailToID where m.ID='" + id + "';";
DataSet aSet = aGateway.Select(query);
foreach (DataRow dataRow in aSet.Tables[0].Rows)
{
MailData aData = new MailData()
{
MailDBID = dataRow["ID"].ToString(),
MailID = dataRow["MailID"].ToString(),
MailDescription = dataRow["MailDescription"].ToString(),
MailFrom = dataRow["MailFrom"].ToString(),
MailTo = dataRow["MailTo"].ToString(),
MailArrivalDate = Convert.ToDateTime(dataRow["DateArrival"].ToString()),
MailDepartureDate = Convert.ToDateTime(dataRow["DateDeparture"].ToString())
};
aMailData.Add(aData);
}
return aMailData;
}
