public String GET_CURRENT_POSITION(non_static_dbcon usethisconnection)
{
DataTable dt = new DataTable();
MySqlCommand cmd = new MySqlCommand();
usethisconnection.SET_COMMAND_PARAMS(cmd, "EMP_POSITION_SELECT_LATEST_BY_ID");
cmd.Parameters.AddWithValue("_empid", empid);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
da.Fill(dt);
if (dt != null)
{
if (dt.Rows.Count > 0)
{
String pos = "";
pos = (dt.Rows[0].Field <String>("position_") != null) ? dt.Rows[0].Field <String>("position_") : "";
position = pos;
return(pos);
}
else
{
return("");
}
}
else
{
return("");
}
}
public Byte[] GET_IMAGE_BY_ID(non_static_dbcon usethisconnection)
{
DataTable dt = new DataTable();
MySqlCommand cmd = new MySqlCommand();
usethisconnection.SET_COMMAND_PARAMS(cmd, "EMP_PIC_SELECT_BY_ID");
cmd.Parameters.AddWithValue("_empid", empid);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
da.Fill(dt);
if (dt != null)
{
if (dt.Rows.Count > 0)
{
Byte[] image = dt.Rows[0].Field <Byte[]>("imagebyte");
pic = image;
return(image);
}
else
{
return(null);
}
}
else
{
return(null);
}
}
}//end select bween dates
public String SELECT_STR_BY_EMPID_BY_DATE(DateTime thisdate, non_static_dbcon usethisconnection)
{
DataTable dt = new DataTable();
MySqlCommand cmd = new MySqlCommand();
usethisconnection.SET_COMMAND_PARAMS(cmd, "ATTENDANCE_STR_BY_EMPID_BY_DATE");
cmd.Parameters.AddWithValue("_empid", empid);
cmd.Parameters.AddWithValue("_thisdate", thisdate);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
try
{
da.Fill(dt);
if (dt != null)
{
return(dt.Rows[0].Field <String>("attendance"));
}
else
{
return("");
}
//return dt;
}
catch (Exception e)
{
Logger.WriteErrorLog("ATTENDANCE_STR_BY_EMPID_BW_DATES MODULE:" + e.Message);
return("");
}
}//end str select bween dates
public Employee SELECT_BY_ID(non_static_dbcon usethisconnection)
{
DataTable dt = new DataTable();
MySqlCommand cmd = new MySqlCommand();
usethisconnection.SET_COMMAND_PARAMS(cmd, "EMP_SELECT_BY_ID");
cmd.Parameters.AddWithValue("_empid", empid);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
da.Fill(dt);
if (dt != null)
{
if (dt.Rows.Count > 0)
{
Employee e = new Employee();
foreach (DataRow r in dt.Rows)
{
e.empid = r["empid"].ToString();
e.fname = r["fname"].ToString();
e.lname = r["lname"].ToString();
e.mname = r["mname"].ToString();
e.address = r["address"].ToString();
e.birthdate = Convert.ToDateTime(r["birthday"].ToString());
e.contactno = r["contactno"].ToString();
e.gender = r["gender"].ToString();
e.COOP_MEMBERSHIP_ID = r["COOP_MEMBERSHIP_ID"].ToString();
e.tinno = r["tinno"].ToString();
}
return(e);
}
else
{
return(null);
}
}
else
{
return(null);
}
}
//for asychronous transaction
public Emp_Sched SELECT_BY_EMPID(non_static_dbcon usethisconnection)
{
DataTable dt = new DataTable();
MySqlCommand cmd = new MySqlCommand();
usethisconnection.SET_COMMAND_PARAMS(cmd, "EMP_SCHED_SELECT_BY_EMPID");
cmd.Parameters.AddWithValue("_empid", empid);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
da.Fill(dt);
if (dt != null)
{
if (dt.Rows.Count > 0)
{
DataRow r = dt.Rows[0];
Emp_Sched es = new Emp_Sched();
es.empid = r["empid"].ToString();
es.mon = r["mon"].ToString();
es.tue = r["tue"].ToString();
es.wed = r["wed"].ToString();
es.thu = r["thu"].ToString();
es.fri = r["fri"].ToString();
es.sat = r["sat"].ToString();
es.sun = r["sun"].ToString();
return(es);
}
else
{
return(null);
}
}
else
{
return(null);
}
}
}//end select bween dates
public DataTable SELECT_BY_EMPID_BW_DATES(DateTime from, DateTime to, non_static_dbcon usethisconnection)
{
DataTable dt = new DataTable();
MySqlCommand cmd = new MySqlCommand();
usethisconnection.SET_COMMAND_PARAMS(cmd, "ATTENDANCE_BY_EMPID_BW_DATES");
cmd.Parameters.AddWithValue("_empid", empid);
cmd.Parameters.AddWithValue("_from", from);
cmd.Parameters.AddWithValue("_to", to);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
try
{
da.Fill(dt);
return(dt);
}
catch (Exception e)
{
Logger.WriteErrorLog("ATTENDANCE_BY_EMPID_BW_DATES MODULE:" + e.Message);
return(null);
}
}//end select bween dates
private void frmCutoffAttendanceReport_Load(object sender, EventArgs e)
{
this.rptViewer.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Local;
this.rptViewer.LocalReport.DataSources.Clear();
string path = System.Reflection.Assembly.GetExecutingAssembly().Location.ToString();
var directory = System.IO.Path.GetDirectoryName(path);
directory += "\\reports\\atttendance_cutoff.rdlc";
//C:\projects\MMG-PIAPS\MMG-PIAPS\bin\Debug\reports\cutoff_attendance.rdlc
//C:\projects\MMG-PIAPS\MMG-PIAPS\bin\Debug\reports\atttendance_cutoff.rdlc
this.rptViewer.LocalReport.ReportPath = directory;
//MessageBox.Show(directory);
Employee emp = new Employee();
emp.branch = "2";
DataTable dt = emp.SELECT_BY_BRANCH();
DataSet1 ds = new DataSet1();
cutoffdt = ds.Tables["cutoffattendance"];
//CREATE AN IMAGE COLUMN
//SET THE IMAGE VALUE
//THEN ADD TO DATASET FOR REPORT REFERENCE
DataColumn colByteArray = new DataColumn("emp_img");
colByteArray.DataType = System.Type.GetType("System.Byte[]");
dt.Columns.Add(colByteArray);
foreach (DataRow item in dt.Rows)
{
Employee e1 = new Employee();
e1.empid = item["empid"].ToString();
item["emp_img"] = e1.GET_IMAGE_BY_ID();
//=====================================
//FOR SUBREPORT DATASOURCE
//POPULATE THE DATASET, FOR SUBREPORT USAGE
//WITH THE EMPLOYEE'S ATTENDANCE FROM BIOMETRICS
//THEN CHECKS THE ATTENDANCE WHETHER GOOD (IN,OUT,IN,OUT)
//OR INAPPROPRIATE (IN ONLY, OR (IN, OUT, IN), OR (IN,OUT,OUT,IN,OUT)
//AND OTHER IMPROPER ATTENDANCE LOGS
int x = 1;
foreach (DataRow cutoffitem in cutoffdetailsdt.Rows)
{
DataRow c = cutoffdt.NewRow();
DateTime d = Convert.ToDateTime(cutoffitem["date_"].ToString());
c.BeginEdit();
c["empid"] = e1.empid;
c["no"] = x;
c["date_"] = d.ToString("MM/dd/yyyy");
//CRREATE A DYNAMIC CONNECTION TO DB
non_static_dbcon cn = new non_static_dbcon();
//COMPARE CUTOFF DATE WITH ATTENDANCE HERE
//==============================================
String attendance;
String status;
if (cn.CONNECT())
{
Attendance att = new Attendance();
att.empid = e1.empid;
attendance = att.SELECT_STR_BY_EMPID_BY_DATE(d, cn);
String[] attarr = attendance.Split(',');
if (attendance != "")
{
if ((attarr.Length == 3) || (attarr.Length > 4))
{
status = "INAPPROPRIATE";
}
else if ((attarr.Length == 2) || attarr.Length == 4)
{
status = "GOOD";
}
else
{
status = "INAPPROPRIATE";
}
}
else
{
status = "";
}
}
else
{
attendance = "";
status = "";
}
cn.DISCONNECT();
c["attendance_"] = attendance;
//===============================================
c["day_type"] = cutoffitem["day_type"].ToString();
c["status_"] = status;
cutoffdt.Rows.Add(c);
x++;
}
//=====================================
}//end foreach item in dt.rows
ReportDataSource r = new ReportDataSource("DataSet1", dt);
this.rptViewer.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(localReport_SubreportProcessing);
//SET REPORT PARAMETER
ReportParameter from_date = new ReportParameter("cutoff_date_from", cutoff_date_from.ToString("MM/dd/yyyy"));
ReportParameter to_date = new ReportParameter("cutoff_date_to", cutoff_date_to.ToString("MM/dd/yyyy"));
rptViewer.LocalReport.SetParameters(from_date);
rptViewer.LocalReport.SetParameters(to_date);
this.rptViewer.LocalReport.DataSources.Add(r);
// this.rptViewer.SetDisplayMode(DisplayMode.PrintLayout);
this.rptViewer.RefreshReport();
}
