public List <movementhistory> getFilteredMovementHistory(DateTime from, DateTime to, int opt, Dictionary <String, String> dict)
{
movementhistory mh;
List <movementhistory> movementHistory = new List <movementhistory>();
try
{
//No Filter
string query1 = strQry +
"CAST(a.CreateTime as DATE) <= '" + to.ToString("yyyy-MM-dd") + "' and CAST(a.CreateTime as DATE)>='" + from.ToString("yyyy-MM-dd") +
"' and a.DocumentStatus in (1,2,3,4,10,98,99,6) and a.Status in (1,98) order by a.CreateTime desc";
//Only EmpID
string query2 = strQry +
" CAST(a.CreateTime as DATE) <= '" + to.ToString("yyyy-MM-dd") + "' and CAST(a.CreateTime as DATE)>='" + from.ToString("yyyy-MM-dd") + "'" +
" and a.EmployeeID='" + dict["EmpId"] + "' and a.DocumentStatus in (1,2,3,4,10,98,99,6) and a.Status in (1,98) order by a.CreateTime desc";
//only purpose
string query3 = strQry +
" CAST(a.CreateTime as DATE) <= '" + to.ToString("yyyy-MM-dd") + "' and CAST(a.CreateTime as DATE)>='" + from.ToString("yyyy-MM-dd") + "'" +
" and a.Purpose='" + dict["Purpose"] + "' and a.DocumentStatus in (1,2,3,4,10,98,99,6) and a.Status in (1,98) order by a.CreateTime desc";
//only Doc Status
string query4 = "";
if (dict["DocStat"].Contains(Convert.ToString(6)))
{
query4 = strQry +
" CAST(a.CreateTime as DATE) <= '" + to.ToString("yyyy-MM-dd") + "' and CAST(a.CreateTime as DATE)>='" + from.ToString("yyyy-MM-dd") + "'" +
" and a.DocumentStatus in ( " + dict["DocStat"] + ") and a.Status in (1, 98) order by a.CreateTime desc";
}
else
{
query4 = strQry +
" CAST(a.CreateTime as DATE) <= '" + to.ToString("yyyy-MM-dd") + "' and CAST(a.CreateTime as DATE)>='" + from.ToString("yyyy-MM-dd") + "'" +
" and a.DocumentStatus = " + dict["DocStat"] + " and a.Status = 1 order by a.CreateTime desc";
}
//EmpID & Purpose
string query5 = strQry +
" CAST(a.CreateTime as DATE) <= '" + to.ToString("yyyy-MM-dd") + "' and CAST(a.CreateTime as DATE)>='" + from.ToString("yyyy-MM-dd") + "'" +
" and a.EmployeeID='" + dict["EmpId"] + "'" +
" and a.Purpose='" + dict["Purpose"] + "' and a.Status = 1 order by a.CreateTime desc";
//EmpID & Doc Stat
string query6 = "";
if (dict["DocStat"].Contains(Convert.ToString(6)))
{
query6 = strQry +
" CAST(a.CreateTime as DATE) <= '" + to.ToString("yyyy-MM-dd") + "' and CAST(a.CreateTime as DATE)>='" + from.ToString("yyyy-MM-dd") + "'" +
" and a.EmployeeID='" + dict["EmpId"] + "'" +
" and a.DocumentStatus in (" + dict["DocStat"] + ") and a.Status in (1, 98) order by a.CreateTime desc";
}
else
{
query6 = strQry +
" CAST(a.CreateTime as DATE) <= '" + to.ToString("yyyy-MM-dd") + "' and CAST(a.CreateTime as DATE)>='" + from.ToString("yyyy-MM-dd") + "'" +
" and a.EmployeeID='" + dict["EmpId"] + "'" +
" and a.DocumentStatus = " + dict["DocStat"] + " and a.Status = 1 order by a.CreateTime desc";
}
//Purpose & Doc Stat
string query7 = "";
if (dict["DocStat"].Contains(Convert.ToString(6)))
{
query7 = strQry +
" CAST(a.CreateTime as DATE) <= '" + to.ToString("yyyy-MM-dd") + "' and CAST(a.CreateTime as DATE)>='" + from.ToString("yyyy-MM-dd") + "'" +
" and a.Purpose='" + dict["Purpose"] + "'" +
" and a.DocumentStatus in (" + dict["DocStat"] + ") and a.Status in (1, 98) order by a.CreateTime desc";
}
else
{
query7 = strQry +
" CAST(a.CreateTime as DATE) <= '" + to.ToString("yyyy-MM-dd") + "' and CAST(a.CreateTime as DATE)>='" + from.ToString("yyyy-MM-dd") + "'" +
" and a.Purpose='" + dict["Purpose"] + "'" +
" and a.DocumentStatus = " + dict["DocStat"] + " and a.Status = 1 order by a.CreateTime desc";
}
//EmpID & Purpose & Doc Stat
string query8 = "";
if (dict["DocStat"].Contains(Convert.ToString(6)))
{
query8 = strQry +
" CAST(a.CreateTime as DATE) <= '" + to.ToString("yyyy-MM-dd") + "' and CAST(a.CreateTime as DATE)>='" + from.ToString("yyyy-MM-dd") + "'" +
" and a.EmployeeID='" + dict["EmpId"] + "'" +
" and a.Purpose='" + dict["Purpose"] + "'" +
" and a.DocumentStatus in (" + dict["DocStat"] + ") and a.Status in (1, 98) order by a.CreateTime desc";
}
else
{
query8 = strQry +
" CAST(a.CreateTime as DATE) <= '" + to.ToString("yyyy-MM-dd") + "' and CAST(a.CreateTime as DATE)>='" + from.ToString("yyyy-MM-dd") + "'" +
" and a.EmployeeID='" + dict["EmpId"] + "'" +
" and a.Purpose='" + dict["Purpose"] + "'" +
" and a.DocumentStatus = " + dict["DocStat"] + " and a.Status = 1 order by a.CreateTime desc";
}
SqlConnection conn = new SqlConnection(Login.connString);
string query = "";
switch (opt)
{
case 1:
query = query1; // No Filter
break;
case 2:
query = query2; // EmpID
break;
case 3:
query = query3; //Purpose
break;
case 4:
query = query4; //Document Status
break;
case 5:
query = query5; //EmpID & Purpose
break;
case 6:
query = query6; //EmpID & Doc Stat
break;
case 7:
query = query7; // Purpose & DocStat
break;
case 8:
query = query8; // EmpID & Purpose & DocStat
break;
default:
query = "";
break;
}
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
mh = new movementhistory();
mh.rowid = reader.GetInt32(0);
mh.date = reader.GetDateTime(1);
mh.empid = reader.GetString(2);
mh.empname = reader.GetString(3);
mh.purpose = reader.GetString(4);
if (!reader.IsDBNull(5))
{
mh.exittime = reader.GetDateTime(5);
}
else
{
mh.exittime = null;
}
if (!reader.IsDBNull(6))
{
mh.returntime = reader.GetDateTime(6);
}
else
{
mh.returntime = null;
}
if (!reader.IsDBNull(7))
{
mh.actexittime = reader.GetDateTime(7);
}
else
{
mh.actexittime = null;
}
if (!reader.IsDBNull(8))
{
mh.actreturntime = reader.GetDateTime(8);
}
else
{
mh.actreturntime = null;
}
mh.documentstatus = reader.GetInt32(9);
mh.status = reader.GetInt32(10);
////////mh.exittime = !reader.IsDBNull(5) ? reader.GetDateTime(5);
////////mh.exittime = null;
////////mh.returntime = reader.GetDateTime(6);
////////mh.actexittime = reader.GetDateTime(7);
////////mh.actreturntime = reader.GetDateTime(8);
movementHistory.Add(mh);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
}
return(movementHistory);
}
public List <movementhistory> getMovementHistoryForAll(DateTime from, DateTime to, String empId, string purpose)
{
movementhistory mh;
List <movementhistory> movementHistory = new List <movementhistory>();
try
{
//EmpId & Purpose
string query1 = strQry +
" CAST(a.CreateTime as DATE) <= '" + to.ToString("yyyy-MM-dd") + "' and CAST(a.CreateTime as DATE)>='" + from.ToString("yyyy-MM-dd") + "'" +
" and a.EmployeeID='" + empId + "' and a.Purpose = '" + purpose + "'" +
" and a.DocumentStatus in (1,2,3,4,99,98,10,6) and a.Status in (1,98) order by a.CreateTime desc";
//EmpID only
string query2 = strQry +
" CAST(a.CreateTime as DATE) <= '" + to.ToString("yyyy-MM-dd") + "' and CAST(a.CreateTime as DATE)>='" + from.ToString("yyyy-MM-dd") + "'" +
" and a.EmployeeID='" + empId + "'" +
" and a.DocumentStatus in (1,2,3,4,99,98,10,6) and a.Status in (1,98) order by a.CreateTime desc";
//Purpose Only
string query3 = strQry +
" CAST(a.CreateTime as DATE) <= '" + to.ToString("yyyy-MM-dd") + "' and CAST(a.CreateTime as DATE)>='" + from.ToString("yyyy-MM-dd") + "'" +
" and a.Purpose = '" + purpose + "'" +
" and a.DocumentStatus in (1,2,3,4,99,98,10,6) and a.Status in (1,98) order by a.CreateTime desc";
//Only all
string query4 = strQry +
" CAST(a.CreateTime as DATE) <= '" + to.ToString("yyyy-MM-dd") + "' and CAST(a.CreateTime as DATE)>='" + from.ToString("yyyy-MM-dd") + "'" +
" and a.DocumentStatus in (1,2,3,4,99,98,10,6) and a.Status in (1,98) order by a.CreateTime desc";
SqlConnection conn = new SqlConnection(Login.connString);
string query = "";
if (empId.Length != 0 && purpose.Length != 0)
{
query = query1;
}
else if (empId.Length != 0)
{
query = query2;
}
else if (purpose.Length != 0)
{
query = query3;
}
else
{
query = query4;
}
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
mh = new movementhistory();
mh.rowid = reader.GetInt32(0);
mh.date = reader.GetDateTime(1);
mh.empid = reader.GetString(2);
mh.empname = reader.GetString(3);
mh.purpose = reader.GetString(4);
if (!reader.IsDBNull(5))
{
mh.exittime = reader.GetDateTime(5);
}
else
{
mh.exittime = null;
}
if (!reader.IsDBNull(6))
{
mh.returntime = reader.GetDateTime(6);
}
else
{
mh.returntime = null;
}
if (!reader.IsDBNull(7))
{
mh.actexittime = reader.GetDateTime(7);
}
else
{
mh.actexittime = null;
}
if (!reader.IsDBNull(8))
{
mh.actreturntime = reader.GetDateTime(8);
}
else
{
mh.actreturntime = null;
}
mh.documentstatus = reader.GetInt32(9);
mh.status = reader.GetInt32(10);
movementHistory.Add(mh);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
}
return(movementHistory);
}