/// <summary>
/// Process Names (ATTD,MESS,LUNCHINOUT) GLOBAL VAR OF FORM
/// Process Mode (APP,SERVER) -> Kind of Process wether application side , server side
/// ProcessType (EMP,WRKGRP) -> Employee Wise or WrkGrp Wise
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
/// <param name="ProcessMode">APP/SERVER</param>
/// <param name="ProcessType">EMP/WRKGRP</param>
private void ProcessDATA(object sender, EventArgs e, string tProcessMode = "APP", string tProcessType = "EMP")
{
if (ProcessList.Rows.Count <= 0)
{
MessageBox.Show("No Records found to process..", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
btnAdd.Enabled = false;
btnDelete.Enabled = false;
btnClearList.Enabled = false;
btnProcessWrkGrp.Enabled = false;
txtWrkFromDt.Enabled = false;
txtWrkToDate.Enabled = false;
txtWrkGrpCode.Enabled = false;
ISProcessStarted = true;
appstatus_Tick(sender, e);
Cursor.Current = Cursors.WaitCursor;
clsProcess pr = new clsProcess();
if (ProcessName == "ATTD")
{
foreach (DataRow dr in ProcessList.Rows)
{
string tEmpUnqID = dr["EmpUnqID"].ToString();
DateTime tFromDt = Convert.ToDateTime(dr["FromDate"]);
DateTime tToDt = Convert.ToDateTime(dr["ToDate"]);
int res;
string proerr = string.Empty;
pr.AttdProcess(tEmpUnqID, tFromDt, tToDt, out res, out proerr);
//update processed status
if (res > 0)
{
dr.BeginEdit();
dr["IsDone"] = 1;
dr["Status"] = "Processed";
dr.EndEdit();
dr.AcceptChanges();
RefreshAppGrid(sender, e);
Application.DoEvents();
}
if (!string.IsNullOrEmpty(proerr))
{
dr["Status"] = "Processed but with error : " + proerr;
}
}
}
else if (ProcessName == "MESS")
{
foreach (DataRow dr in ProcessList.Rows)
{
string tEmpUnqID = dr["EmpUnqID"].ToString();
DateTime tFromDt = Convert.ToDateTime(dr["FromDate"]);
DateTime tToDt = Convert.ToDateTime(dr["ToDate"]);
int res;
pr.LunchProcess(tEmpUnqID, tFromDt, tToDt, out res);
Application.DoEvents();
//update processed status
if (res > 0)
{
dr.BeginEdit();
dr["IsDone"] = 1;
dr["Status"] = "Processed";
dr.EndEdit();
dr.AcceptChanges();
RefreshAppGrid(sender, e);
}
}
}
else if (ProcessName == "LUNCHINOUT")
{
foreach (DataRow dr in ProcessList.Rows)
{
string tEmpUnqID = dr["EmpUnqID"].ToString();
DateTime tFromDt = Convert.ToDateTime(dr["FromDate"]);
DateTime tToDt = Convert.ToDateTime(dr["ToDate"]);
int res;
pr.LunchInOutProcess(tEmpUnqID, tFromDt, tToDt, out res);
Application.DoEvents();
//update processed status
if (res > 0)
{
dr.BeginEdit();
dr["IsDone"] = 1;
dr["Status"] = "Processed";
dr.EndEdit();
dr.AcceptChanges();
RefreshAppGrid(sender, e);
}
}
}
Cursor.Current = Cursors.Default;
btnAdd.Enabled = true;
btnDelete.Enabled = true;
btnProcessWrkGrp.Enabled = true;
txtWrkFromDt.Enabled = true;
txtWrkToDate.Enabled = true;
txtWrkGrpCode.Enabled = true;
btnClearList.Enabled = true;
ISProcessStarted = false;
appstatus_Tick(sender, e);
MessageBox.Show("Process Completed...", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void btnImport_Click(object sender, EventArgs e)
{
txtYearMT.Enabled = false;
btnBrowse.Enabled = false;
btnImport.Enabled = false;
btnPreview.Enabled = false;
if (grd_view1.DataRowCount <= 0)
{
MessageBox.Show("No Rows to Upload", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
Cursor.Current = Cursors.WaitCursor;
DataTable dtMaterial = new DataTable();
DataTable sortedDT = new DataTable();
try
{
foreach (GridColumn column in grd_view1.VisibleColumns)
{
if (column.FieldName != string.Empty)
{
dtMaterial.Columns.Add(column.FieldName, column.ColumnType);
}
}
for (int i = 0; i < grd_view1.DataRowCount; i++)
{
DataRow row = dtMaterial.NewRow();
foreach (GridColumn column in grd_view1.VisibleColumns)
{
row[column.FieldName] = grd_view1.GetRowCellValue(i, column);
}
dtMaterial.Rows.Add(row);
}
DataView dv = dtMaterial.DefaultView;
dv.Sort = "EmpUnqID asc";
sortedDT = dv.ToTable();
using (SqlConnection cn = new SqlConnection(Utils.Helper.constr))
{
DateTime StartDt = new DateTime(), EndDt = new DateTime();
string sql = string.Empty;
string tYearMt = txtYearMT.DateTime.ToString("yyyyMM");
string stdt = Utils.Helper.GetDescription("Select CAL_MthStart From V_CAL0815 where Cal_Date ='" + txtYearMT.DateTime.ToString("yyyy-MM-dd") + "'", Utils.Helper.constr);
string endt = Utils.Helper.GetDescription("Select CAL_MthEnd From V_CAL0815 where Cal_Date ='" + txtYearMT.DateTime.ToString("yyyy-MM-dd") + "'", Utils.Helper.constr);
StartDt = Convert.ToDateTime(stdt);
EndDt = Convert.ToDateTime(endt);
foreach (DataRow dr in sortedDT.Rows)
{
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
bool ProcessFlg = chkProcessFlg.Checked;
cn.Open();
bool brkflg = false;
string tEmpUnqID = dr["EmpUnqID"].ToString();
string tCompCode = Utils.Helper.GetDescription("Select CompCode From MastEmp where EmpUnqID ='" + tEmpUnqID + "'", Utils.Helper.constr);
clsEmp Emp = new clsEmp();
Emp.GetEmpDetails(tCompCode, tEmpUnqID);
if (!Emp.Active)
{
dr["Remarks"] = "Invalid/InActive Employee..";
continue;
}
if (!Globals.GetWrkGrpRights(GFormID, Emp.WrkGrp, Emp.EmpUnqID))
{
dr["Remarks"] = "you are not authorised..";
continue;
}
List <int> woidx = new List <int>();
#region Chk_ValidShift
for (int i = 1; i <= EndDt.Day; i++)
{
string fldnm = "D" + i.ToString("00");
string fldval = dr[fldnm].ToString().Trim().ToUpper();
if (fldval != "WO")
{
if (!Globals.G_ShiftList.Contains(fldval))
{
dr["Remarks"] = dr["Remarks"].ToString() + " Invalid Shift..";
brkflg = true;
break;
}
}
else
{
woidx.Add(i);
}
if (fldval.Trim() == string.Empty)
{
dr["Remarks"] = dr["Remarks"].ToString() + " Invalid Shift..";
brkflg = true;
break;
}
}
if (woidx.Count == 0)
{
dr["Remarks"] = dr["Remarks"].ToString() + " No Week of has been defined..";
brkflg = true;
}
if (brkflg)
{
continue;
}
#region Check_WO_Order
//int firstWOon = woidx.First();
//int tchk = 0;
//for (int i = 1; i <= woidx.Count - 1; i++)
//{
// tchk = (7 * i) + firstWOon;
// if(woidx.ElementAt(i) != tchk)
// {
// dr["Remarks"] = dr["Remarks"].ToString() + string.Format("{0} WeekOff is not in correct order..",i);
// //brkflg = true;
// break;
// }
//}
//if (brkflg)
//{
// continue;
//}
#endregion
#endregion
SqlTransaction trn = cn.BeginTransaction("ShiftSch-" + Emp.EmpUnqID);
SqlCommand cmd = new SqlCommand();
#region Chk_ShiftSchedule_Rec
try
{
int cnt = Convert.ToInt32(Utils.Helper.GetDescription("Select Count(*) from MastShiftSchedule where EmpUnqID = '" + tEmpUnqID + "' And YearMt ='" + tYearMt + "'", Utils.Helper.constr));
if (cnt == 0)
{
sql = "Insert into MastShiftSchedule (YearMt,EmpUnqID,AddDt,AddId) Values ('" + tYearMt + "','" + tEmpUnqID + "',GetDate(),'" + Utils.User.GUserID + "')";
cmd = new SqlCommand(sql, cn, trn);
cmd.ExecuteNonQuery();
}
else
{
sql = "insert into MastShiftScheduleHistory ( [YearMt],[EmpUnqID], " +
" [D01],[D02],[D03],[D04],[D05],[D06],[D07],[D08],[D09],[D10]," +
" [D11],[D12],[D13],[D14],[D15],[D16],[D17],[D18],[D19],[D20],[D21], " +
" [D22],[D23],[D24],[D25],[D26],[D27],[D28],[D29],[D30],[D31],[ADDID], " +
" [ADDDT],[UPDID],[UPDDT]) " +
" Select " +
" [YearMt],[EmpUnqID]," +
" [D01],[D02],[D03],[D04],[D05],[D06],[D07],[D08],[D09],[D10]," +
" [D11],[D12],[D13],[D14],[D15],[D16],[D17],[D18],[D19],[D20],[D21], " +
" [D22],[D23],[D24],[D25],[D26],[D27],[D28],[D29],[D30],[D31],[ADDID]," +
" [ADDDT],[UPDID],[UPDDT] from MastShiftSchedule " +
" where YearMt = '" + tYearMt + "' " +
" and EmpUnqID = '" + tEmpUnqID + "' ";
cmd = new SqlCommand(sql, cn, trn);
cmd.ExecuteNonQuery();
sql = "Update MastShiftSchedule Set UpdDt = GetDate() , UpdID = '" + Utils.User.GUserID + "' Where YearMt = '" + tYearMt + "' And EmpUnqID ='" + tEmpUnqID + "'";
cmd = new SqlCommand(sql, cn, trn);
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
brkflg = true;
dr["Remarks"] = dr["Remarks"].ToString() + Environment.NewLine + ex.ToString();
}
if (brkflg)
{
trn.Rollback();
cn.Close();
trn.Dispose();
continue;
}
#endregion
#region Get_IFAnyLeavePosted
sql = "Select * from LeaveEntry Where " +
" compcode = '" + Emp.CompCode + "'" +
" and WrkGrp ='" + Emp.WrkGrp + "'" +
" And tYear ='" + tYearMt.Substring(0, 4) + "'" +
" And EmpUnqID='" + tEmpUnqID + "'" +
" And ( FromDt between '" + StartDt.ToString("yyyy-MM-dd") + "' And '" + EndDt.ToString("yyyy-MM-dd") + "' " +
" OR ToDt Between '" + StartDt.ToString("yyyy-MM-dd") + "' And '" + EndDt.ToString("yyyy-MM-dd") + "' " +
" ) Order by FromDt ";
DataSet dsLeave = Utils.Helper.GetData(sql, Utils.Helper.constr);
List <LeaveData> leave = new List <LeaveData>();
bool hasRows = dsLeave.Tables.Cast <DataTable>().Any(table => table.Rows.Count != 0);
if (hasRows)
{
foreach (DataRow drl in dsLeave.Tables[0].Rows)
{
LeaveData t = new LeaveData();
t.FromDt = Convert.ToDateTime(drl["FromDt"]);
t.ToDt = ((Convert.ToDateTime(drl["ToDt"]) > EndDt) ? EndDt : Convert.ToDateTime(drl["ToDt"]));
t.LeaveTyp = drl["LeaveTyp"].ToString();
leave.Add(t);
}
}
#endregion
#region Upd_ShiftSchedule_AttdData
string ShiftSql = string.Empty;
for (DateTime date = StartDt; date.Date <= EndDt.Date; date = date.AddDays(1))
{
string fldnm = "D" + date.ToString("dd");
string fldval = dr[fldnm].ToString().Trim().ToUpper();
try
{
string sqlattd = "Update AttdData " +
" Set ScheduleShift ='" + fldval + "' Where tYear = '" + date.Year.ToString() + "' And tDate ='" + date.ToString("yyyy-MM-dd") + "' " +
" And CompCode = '" + Emp.CompCode + "' And WrkGrp ='" + Emp.WrkGrp + "' And EmpUnqID='" + Emp.EmpUnqID + "'";
cmd = new SqlCommand(sqlattd, cn, trn);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
brkflg = true;
dr["Remarks"] = dr["Remarks"].ToString() + Environment.NewLine + ex.ToString();
break;
}
ShiftSql += fldnm + " = '" + fldval + "',";
} //end foreach date
if (brkflg)
{
trn.Rollback();
cn.Close();
trn.Dispose();
continue;
}
try
{
//update shiftschedule
sql = "Update MastShiftSchedule Set " + ShiftSql.Substring(0, ShiftSql.Length - 1) + " Where Yearmt = '" + tYearMt + "' and EmpUnqId ='" + Emp.EmpUnqID + "' ";
cmd = new SqlCommand(sql, cn, trn);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
brkflg = true;
dr["Remarks"] = dr["Remarks"].ToString() + Environment.NewLine + ex.ToString();
}
if (brkflg)
{
trn.Rollback();
cn.Close();
trn.Dispose();
continue;
}
#endregion
#region chk_LeavePosted
if (leave.Count == 0)
{
#region ifnoleavefound
//delete schedule wo
string delsql = "Delete From MastLeaveSchedule Where tYear ='" + StartDt.Year.ToString() + "' " +
" And tYearMT='" + tYearMt + "' " +
" And EmpUnqID ='" + Emp.EmpUnqID + "' And SchLeave='WO' ";
try
{
cmd = new SqlCommand(delsql, cn, trn);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
dr["Remarks"] = dr["Remarks"].ToString() + Environment.NewLine + ex.ToString();
brkflg = true;
}
if (brkflg)
{
trn.Rollback();
cn.Close();
trn.Dispose();
continue;
}
//'Insert "WO" MastLeaveSchedule Table
for (DateTime date = StartDt; date.Date <= EndDt.Date; date = date.AddDays(1))
{
string fldnm = "D" + date.ToString("dd");
string fldval = dr[fldnm].ToString().Trim().ToUpper();
if (fldval == "WO")
{
string inssql = " Insert into MastLeaveSchedule " +
" ( EmpUnqID,WrkGrp,tDate,SchLeave,Adddt,AddId )" +
" Values ('" + Emp.EmpUnqID + "','" + Emp.WrkGrp + "','" + date.ToString("yyyy-MM-dd") + "','WO',GetDate(),'ShiftSch')";
try
{
cmd = new SqlCommand(inssql, cn, trn);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
dr["Remarks"] = dr["Remarks"].ToString() + Environment.NewLine + ex.ToString();
brkflg = true;
break;
}
}
}
if (brkflg)
{
trn.Rollback();
cn.Close();
trn.Dispose();
continue;
}
#endregion
}
else
{
#region ifleavefound
DateTime LastWO = new DateTime();
for (DateTime date = StartDt; date.Date <= EndDt.Date; date = date.AddDays(1))
{
string fldnm = "D" + date.ToString("dd");
string fldval = dr[fldnm].ToString().Trim().ToUpper();
bool LeavPos = false;
if (fldval == "WO")
{
foreach (LeaveData t in leave)
{
//check if date is fall between leave posted
if (date >= t.FromDt && date <= t.ToDt)
{
LeavPos = true;
date = t.ToDt;
LastWO = t.ToDt;
break;
}
}
if (LeavPos)
{
continue;
}
//'if there is no leave posted on tdate
if (!LeavPos)
{
if (LastWO != DateTime.MinValue)
{
try
{
string delwo = "Delete from MastLeaveSchedule Where EmpUnqID = '" + Emp.EmpUnqID + "' "
+ " and tDate > '" + LastWO.ToString("yyyy-MM-dd") + "' and tDate <= '" + date.ToString("yyyy-MM-dd") + "' and SchLeave ='WO'";
LastWO = date;
cmd = new SqlCommand(delwo, cn, trn);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
dr["Remarks"] = dr["Remarks"].ToString() + Environment.NewLine + ex.ToString();
brkflg = true;
break;
}
}
else
{
try
{
string delwo = "Delete from MastLeaveSchedule Where EmpUnqID = '" + Emp.EmpUnqID + "' and tDate = '" + date.ToString("yyyy-MM-dd") + "' and SchLeave ='WO'";
cmd = new SqlCommand(delwo, cn, trn);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
dr["Remarks"] = dr["Remarks"].ToString() + Environment.NewLine + ex.ToString();
brkflg = true;
break;
}
}
try
{
string inssql = " Insert into MastLeaveSchedule ( EmpUnqID,WrkGrp,tDate,SchLeave,Adddt,AddId )" +
" Values ('" + Emp.EmpUnqID + "','" + Emp.WrkGrp + "','" + date.ToString("yyyy-MM-dd") + "','WO',GetDate(),'ShiftSch')";
cmd = new SqlCommand(inssql, cn, trn);
cmd.ExecuteNonQuery();
}catch (Exception ex)
{
dr["Remarks"] = dr["Remarks"].ToString() + Environment.NewLine + ex.ToString();
brkflg = true;
break;
}
}
}//end if WO
else
{
LeavPos = false;
foreach (LeaveData t in leave)
{
//check if date is fall between leave posted
if (date >= t.FromDt && date <= t.ToDt)
{
LeavPos = true;
break;
}
}
if (!LeavPos)
{
try
{
sql = "Delete from MastLeaveSchedule Where EmpUnqID = '" + Emp.EmpUnqID + "' " +
" and tDate = '" + date.ToString("yyyy-MM-dd") + "' and SchLeave ='WO'";
cmd = new SqlCommand(sql, cn, trn);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
dr["Remarks"] = dr["Remarks"].ToString() + Environment.NewLine + ex.ToString();
brkflg = true;
break;
}
}
}
} //end of eachday
if (brkflg)
{
trn.Rollback();
cn.Close();
trn.Dispose();
continue;
}
//remove any extra "WO" if
try
{
if (LastWO != DateTime.MinValue)
{
sql = "Delete from MastLeaveSchedule Where EmpUnqID = '" + Emp.EmpUnqID + "' and tDate > '" + LastWO.ToString("yyyy-MM-dd") + "' " +
" and tDate <= '" + EndDt.ToString("yyyy-MM-dd") + "' and SchLeave ='WO'";
cmd = new SqlCommand(sql, cn, trn);
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
dr["Remarks"] = dr["Remarks"].ToString() + Environment.NewLine + ex.ToString();
brkflg = true;
break;
}
#endregion
} //end if leavefound
if (brkflg)
{
trn.Rollback();
cn.Close();
trn.Dispose();
continue;
}
#endregion
try
{
trn.Commit();
dr["Remarks"] = "Uploded";
//process data
if (ProcessFlg)
{
clsProcess pro = new clsProcess();
int result = 0;
string proerr = string.Empty;
pro.AttdProcess(Emp.EmpUnqID, StartDt, EndDt, out result, out proerr);
if (result > 0)
{
pro.LunchInOutProcess(Emp.EmpUnqID, StartDt, EndDt, out result);
//dr["remarks"] = dr["remarks"].ToString() + "Record updated...";
}
}
}
catch (Exception ex)
{
trn.Rollback();
dr["Remarks"] = dr["Remarks"].ToString() + Environment.NewLine + ex.ToString();
brkflg = true;
}
trn.Dispose();
cn.Close();
} //using foreach of all employee
} //using connection
Cursor.Current = Cursors.Default;
MessageBox.Show("file uploaded Successfully, please check the remarks for indivisual record status...", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex) { MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
DataSet ds = new DataSet();
ds.Tables.Add(sortedDT);
grd_view.DataSource = ds;
grd_view.DataMember = ds.Tables[0].TableName;
grd_view.Refresh();
Cursor.Current = Cursors.Default;
txtYearMT.Enabled = true;
btnBrowse.Enabled = true;
btnPreview.Enabled = true;
if (GRights.Contains("A") || GRights.Contains("U") || GRights.Contains("D"))
{
btnImport.Enabled = true;
}
else
{
btnImport.Enabled = false;
}
}
private void btnImport_Click(object sender, EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;
DataTable LunchMachines = Utils.Helper.GetData("Select * from [LunchMachine]", Utils.Helper.constr).Tables[0];
DataTable dtMaterial = new DataTable();
DataTable sortedDT = new DataTable();
try
{
foreach (GridColumn column in grd_view1.VisibleColumns)
{
if (column.FieldName != string.Empty)
{
dtMaterial.Columns.Add(column.FieldName, column.ColumnType);
}
}
for (int i = 0; i < grd_view1.DataRowCount; i++)
{
DataRow row = dtMaterial.NewRow();
foreach (GridColumn column in grd_view1.VisibleColumns)
{
row[column.FieldName] = grd_view1.GetRowCellValue(i, column);
}
dtMaterial.Rows.Add(row);
}
DataView dv = dtMaterial.DefaultView;
dv.Sort = "EmpUnqID asc";
sortedDT = dv.ToTable();
using (SqlConnection con = new SqlConnection(Utils.Helper.constr))
{
DateTime tdt;
con.Open();
foreach (DataRow dr in sortedDT.Rows)
{
string tEmpUnqID = dr["EmpUnqID"].ToString();
try
{
tdt = Convert.ToDateTime(dr["SanDate"]);
}
catch (Exception ex)
{
dr["Remarks"] = "Sanction Date Conversion failed...";
continue;
}
string err = DataValidate(dr);
if (!string.IsNullOrEmpty(err))
{
dr["Remarks"] = err;
continue;
}
if (string.IsNullOrEmpty(dr["Location"].ToString().Trim()))
{
dr["Remarks"] = "Location Must be Required..";
continue;
}
clsEmp Emp = new clsEmp();
#region Chk_Primary
try
{
Emp.CompCode = "01";
Emp.EmpUnqID = tEmpUnqID;
Emp.GetEmpDetails(Emp.CompCode, Emp.EmpUnqID);
}
catch (Exception ex)
{
dr["Remarks"] = ex.ToString();
continue;
}
#endregion
#region Chk_AllVals
//check all values if all empty skip
if (dr["LunchInTime"].ToString() == "" && dr["LunchOutTime"].ToString() == "")
{
dr["Remarks"] = dr["Remarks"].ToString() + " Nothing to update...";
continue;
}
#endregion
DateTime tInTime = new DateTime(), tOutTime = new DateTime(), tDate = new DateTime();
DateTime tSanDate = Convert.ToDateTime(dr["SanDate"]).Date;
#region Chk_InTime
if (dr["LunchInTime"].ToString().Trim() != "" && dr["SanDate"] != DBNull.Value)
{
tInTime = Convert.ToDateTime(dr["SanDate"]);
string[] inary = dr["LunchInTime"].ToString().Split(':');
if (inary.GetLength(0) >= 2)
{
tInTime = tInTime.AddHours(Convert.ToInt32(inary[0].ToString().Trim()));
tInTime = tInTime.AddMinutes(Convert.ToInt32(inary[1].ToString().Trim()));
}
else
{
dr["LunchInTime"] = DBNull.Value;
dr["Remarks"] = dr["Remarks"].ToString() + " Invalid InTime, will not be considered..";
}
}
#endregion
#region Chk_OutTime
if (dr["LunchOutTime"].ToString().Trim() != "" && dr["SanDate"] != DBNull.Value)
{
tOutTime = Convert.ToDateTime(dr["SanDate"]);
string[] inary = dr["LunchOutTime"].ToString().Split(':');
if (inary.GetLength(0) >= 2)
{
tOutTime = tOutTime.AddHours(Convert.ToInt32(inary[0].ToString().Trim()));
tOutTime = tOutTime.AddMinutes(Convert.ToInt32(inary[1].ToString().Trim()));
}
else
{
dr["LunchOutTime"] = DBNull.Value;
dr["Remarks"] = dr["Remarks"].ToString() + " Invalid OutTime, will not be considered..";
}
}
#endregion
#region Chk_AllVals
//check all values if all empty skip
if (dr["LunchInTime"].ToString().Trim() == "" && dr["LunchOutTime"].ToString().Trim() == "")
{
dr["Remarks"] = dr["Remarks"].ToString() + " Nothing to update...";
continue;
}
string sWrkGrp = "", sDate = "", sInTime = "", sOutTime = "", sLocation = "", sInMachine = "", sOutMachine = "";
sWrkGrp = Emp.WrkGrp;
sLocation = dr["Location"].ToString().Trim();
string expression;
expression = "Location = '" + sLocation + "'";
DataRow[] foundRows;
foundRows = LunchMachines.Select(expression);
if (foundRows.Length <= 0)
{
dr["Remarks"] = dr["Remarks"].ToString() + " Invalid Location...";
continue;
}
else
{
sInMachine = foundRows[0]["InMachine"].ToString();
sOutMachine = foundRows[0]["OutMachine"].ToString();
}
#endregion
#region Set_InTime
if (tInTime == DateTime.MinValue || tInTime == Convert.ToDateTime(dr["SanDate"]))
{
sInTime = " NULL ";
}
else if (tInTime.Hour > 0 || tInTime.Minute >= 0)
{
sInTime = "'" + tInTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
}
else
{
sInTime = " NULL ";
}
#endregion
#region Set_OutTime
if (tOutTime == DateTime.MinValue || tOutTime == Convert.ToDateTime(dr["SanDate"]))
{
sOutTime = " NULL ";
}
else if (tOutTime.Hour > 0 || tOutTime.Minute >= 0)
{
sOutTime = "'" + tOutTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
}
else
{
sOutTime = " NULL ";
}
#endregion
#region Final_Update
string sql = string.Empty;
using (SqlCommand cmd = new SqlCommand())
{
try
{
sDate = Convert.ToDateTime(dr["SanDate"]).ToString("yyyy-MM-dd");
tDate = Convert.ToDateTime(dr["SanDate"]);
sql = string.Empty;
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
if (!string.IsNullOrEmpty(sInTime) && sInTime != " NULL ")
{
sql = "Insert Into AttdLunchGate " +
" (PunchDate,EmpUnqID,IOFLG,MachineIP,LunchFlg,tYear,tYearMt,t1Date,AddDt,AddID) Values (" +
" " + sInTime + ",'" + Emp.EmpUnqID + "','I','" + sInMachine + "',0,'" + tDate.Year + "','" + tDate.ToString("yyyyMM") + "','" + sDate + "',GetDate(),'" + Utils.User.GUserID + "-San" + "')";
}
cmd.CommandText = sql;
cmd.CommandTimeout = 0;
cmd.ExecuteNonQuery();
if (!string.IsNullOrEmpty(sOutTime) && sOutTime != " NULL ")
{
sql = "Insert Into AttdLunchGate " +
" (PunchDate,EmpUnqID,IOFLG,MachineIP,LunchFlg,tYear,tYearMt,t1Date,AddDt,AddID) Values (" +
" " + sOutTime + ",'" + Emp.EmpUnqID + "','O','" + sOutMachine + "',0,'" + tDate.Year + "','" + tDate.ToString("yyyyMM") + "','" + sDate + "',GetDate(),'" + Utils.User.GUserID + "-San" + "')";
}
cmd.CommandText = sql;
cmd.CommandTimeout = 0;
cmd.ExecuteNonQuery();
clsProcess pro = new clsProcess();
int result = 0;
string proerr = string.Empty;
pro.AttdProcess(Emp.EmpUnqID, tDate, tDate.AddDays(1), out result, out proerr);
if (result > 0)
{
pro.LunchInOutProcess(Emp.EmpUnqID, tDate, tDate.AddDays(1), out result);
dr["remarks"] = dr["remarks"].ToString() + "Record updated...";
}
}
catch (Exception ex)
{
dr["remarks"] = dr["remarks"].ToString() + ex.ToString();
continue;
}
} //using sqlcommand
#endregion
} //using foreach
con.Close();
}//using connection
Cursor.Current = Cursors.Default;
MessageBox.Show("file uploaded Successfully, please check the remarks for indivisual record status...", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex) { MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
DataSet ds = new DataSet();
ds.Tables.Add(sortedDT);
grd_view.DataSource = ds;
grd_view.DataMember = ds.Tables[0].TableName;
grd_view.Refresh();
Cursor.Current = Cursors.Default;
}
private void btnImport_Click(object sender, EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;
DataTable dtMaterial = new DataTable();
DataTable sortedDT = new DataTable();
try
{
foreach (GridColumn column in grd_view1.VisibleColumns)
{
if (column.FieldName != string.Empty)
{
dtMaterial.Columns.Add(column.FieldName, column.ColumnType);
}
}
for (int i = 0; i < grd_view1.DataRowCount; i++)
{
DataRow row = dtMaterial.NewRow();
foreach (GridColumn column in grd_view1.VisibleColumns)
{
row[column.FieldName] = grd_view1.GetRowCellValue(i, column);
}
dtMaterial.Rows.Add(row);
}
DataView dv = dtMaterial.DefaultView;
dv.Sort = "EmpUnqID asc";
sortedDT = dv.ToTable();
using (SqlConnection con = new SqlConnection(Utils.Helper.constr))
{
DateTime tdt;
con.Open();
foreach (DataRow dr in sortedDT.Rows)
{
string tEmpUnqID = dr["EmpUnqID"].ToString();
try
{
tdt = Convert.ToDateTime(dr["SanDate"]);
}
catch (Exception ex)
{
dr["Remarks"] = "Sanction Date Conversion failed...";
continue;
}
string err = DataValidate(dr);
if (!string.IsNullOrEmpty(err))
{
dr["Remarks"] = err;
continue;
}
clsEmp Emp = new clsEmp();
#region Chk_Primary
try
{
DateTime tAddDt = Convert.ToDateTime(Utils.Helper.GetDescription("Select GetDate()", Utils.Helper.constr));
//added on 02-12-2014 full rights skip the validation
if (Utils.User.IsAdmin == false)
{
//'check does not allow morethan 2 days blank intime and outtime
TimeSpan ts = tAddDt - Convert.ToDateTime(dr["SanDate"]);
if (ts.Days > rSanDayLimit && (dr["InTime"].ToString() != "" || dr["OutTime"].ToString() != ""))
{
if (dr["InTime"].ToString() != "")
{
dr["InTime"] = DBNull.Value;
}
if (dr["OutTime"].ToString() != "")
{
dr["Outtime"] = DBNull.Value;
}
dr["Remarks"] = "Did not consider In/Out time (Limit Exceed)";
}
//'added on 30/06/2016
if (Globals.GetWrkGrpRights(GFormID, "", tEmpUnqID) == false)
{
//dr["InTime"] = DBNull.Value;
//dr["OutTime"] = DBNull.Value;
//dr["ShiftCode"] = DBNull.Value;
//dr["TPAHours"] = DBNull.Value;
dr["Remarks"] = "Unauthorised..";
continue;
}
}
Emp.CompCode = "01";
Emp.EmpUnqID = tEmpUnqID;
Emp.GetEmpDetails(Emp.CompCode, Emp.EmpUnqID);
}
catch (Exception ex)
{
dr["Remarks"] = ex.ToString();
continue;
}
#endregion
#region Chk_AllVals
//check all values if all empty skip
if (dr["InTime"].ToString() == "" && dr["OutTime"].ToString() == "" &&
dr["ShiftCode"].ToString() == "" && dr["TPAHours"].ToString() == "")
{
dr["Remarks"] = dr["Remarks"].ToString() + " Nothing to update...";
continue;
}
#endregion
DateTime tInTime = new DateTime(), tOutTime = new DateTime(), tDate = new DateTime();
#region Chk_InTime
if (dr["InTime"].ToString().Trim() != "" && dr["SanDate"] != DBNull.Value)
{
tInTime = Convert.ToDateTime(dr["SanDate"]);
string[] inary = dr["InTime"].ToString().Split(':');
if (inary.GetLength(0) == 2)
{
tInTime = tInTime.AddHours(Convert.ToInt32(inary[0].ToString().Trim()));
tInTime = tInTime.AddMinutes(Convert.ToInt32(inary[1].ToString().Trim()));
}
else
{
dr["InTime"] = DBNull.Value;
dr["Remarks"] = dr["Remarks"].ToString() + " Invalid InTime, will not be considered..";
}
}
#endregion
#region Chk_OutTime
if (dr["OutTime"].ToString().Trim() != "" && dr["SanDate"] != DBNull.Value)
{
tOutTime = Convert.ToDateTime(dr["SanDate"]);
string[] inary = dr["OutTime"].ToString().Split(':');
if (inary.GetLength(0) == 2)
{
tOutTime = tOutTime.AddHours(Convert.ToInt32(inary[0].ToString().Trim()));
tOutTime = tOutTime.AddMinutes(Convert.ToInt32(inary[1].ToString().Trim()));
}
else
{
dr["OutTime"] = DBNull.Value;
dr["Remarks"] = dr["Remarks"].ToString() + " Invalid OutTime, will not be considered..";
}
}
#endregion
#region Chk_ShiftCode
if (dr["ShiftCode"].ToString().Trim() != "")
{
if (!Globals.G_ShiftList.Contains(dr["ShiftCode"].ToString()))
{
dr["ShiftCode"] = "";
dr["Remarks"] = dr["Remarks"].ToString() + " Invalid ShiftCode, will not be considered..";
}
}
#endregion
#region Chk_OverTime
if (dr["TPAHours"].ToString() != "")
{
double t = 0;
if (double.TryParse(dr["TPAHours"].ToString(), out t))
{
if (t > 24)
{
dr["TPAHours"] = "";
dr["Remarks"] = dr["Remarks"].ToString() + " Invalid TPAHours, will not be considered..";
}
}
else
{
dr["TPAHours"] = "";
dr["Remarks"] = dr["Remarks"].ToString() + " Invalid TPAHours, will not be considered..";
}
}
#endregion
#region Chk_AllVals
//check all values if all empty skip
if (dr["InTime"].ToString() == "" && dr["OutTime"].ToString() == "" &&
dr["ShiftCode"].ToString() == "" && dr["TPAHours"].ToString() == "")
{
dr["Remarks"] = dr["Remarks"].ToString() + " Nothing to update...";
continue;
}
#endregion
string sWrkGrp = "", sDate = "", sInTime = "", sOutTime = "", sShiftCode = "", sOverTime = "";
sWrkGrp = Emp.WrkGrp;
#region Set_InTime
if (tInTime == DateTime.MinValue || tInTime == Convert.ToDateTime(dr["SanDate"]))
{
sInTime = " NULL ";
}
else if (tInTime.Hour > 0 || tInTime.Minute > 0)
{
sInTime = "'" + tInTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
}
else
{
sInTime = " NULL ";
}
#endregion
#region Set_OutTime
if (tOutTime == DateTime.MinValue || tOutTime == Convert.ToDateTime(dr["SanDate"]))
{
sOutTime = " NULL ";
}
else if (tOutTime.Hour > 0 || tOutTime.Minute > 0)
{
sOutTime = "'" + tOutTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
}
else
{
sOutTime = " NULL ";
}
#endregion
#region Set_ShiftCode
if (string.IsNullOrEmpty(dr["ShiftCode"].ToString()))
{
sShiftCode = " Null ";
}
else
{
sShiftCode = "'" + dr["ShiftCode"].ToString().ToUpper().Trim() + "'";
}
#endregion
#region Set_OverTime
if (string.IsNullOrEmpty(dr["TPAHours"].ToString()))
{
sOverTime = " Null ";
}
else
{
sOverTime = "'" + dr["TPAHours"].ToString().Trim() + "'";
}
#endregion
#region Final_Update
using (SqlCommand cmd = new SqlCommand())
{
try
{
sDate = Convert.ToDateTime(dr["SanDate"]).ToString("yyyy-MM-dd");
tDate = Convert.ToDateTime(dr["SanDate"]);
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
string sql = "Insert Into MastLeaveSchedule " +
" (tDate,EmpUnqID,WrkGrp,ConsInTime,ConsOutTime,ConsShift,ConsOverTime,AddId,AddDt) Values (" +
" '" + sDate + "','" + Emp.EmpUnqID + "','" + sWrkGrp + "'," + sInTime + "," +
sOutTime + "," + sShiftCode + "," + sOverTime + ",'" + Utils.User.GUserID + "',GetDate())";
cmd.CommandText = sql;
cmd.CommandTimeout = 0;
cmd.ExecuteNonQuery();
clsProcess pro = new clsProcess();
int result = 0;
string proerr = string.Empty;
pro.AttdProcess(Emp.EmpUnqID, tDate, tDate.AddDays(1), out result, out proerr);
if (result > 0)
{
pro.LunchInOutProcess(Emp.EmpUnqID, tDate, tDate.AddDays(1), out result);
dr["remarks"] = dr["remarks"].ToString() + "Record updated...";
}
}
catch (Exception ex)
{
dr["remarks"] = dr["remarks"].ToString() + ex.ToString();
continue;
}
} //using sqlcommand
#endregion
} //using foreach
con.Close();
}//using connection
Cursor.Current = Cursors.Default;
MessageBox.Show("file uploaded Successfully, please check the remarks for indivisual record status...", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex) { MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
DataSet ds = new DataSet();
ds.Tables.Add(sortedDT);
grd_view.DataSource = ds;
grd_view.DataMember = ds.Tables[0].TableName;
grd_view.Refresh();
Cursor.Current = Cursors.Default;
}
private void btnProcess_Click(object sender, EventArgs e)
{
string err = DataValidate();
if (!string.IsNullOrEmpty(err))
{
MessageBox.Show(err, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (!string.IsNullOrEmpty(txtEmpUnqID.Text.Trim()))
{
if (string.IsNullOrEmpty(txtEmpName.Text.Trim()))
{
MessageBox.Show("Please Enter Valid Employee", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
if (!GRights.Contains("AUDV"))
{
MessageBox.Show("You are not fully authorised..", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string sql = string.Empty;
string question = string.Empty;
DialogResult drq;
string sWrkGrp = txtWrkGrpCode.Text.Trim().ToString();
string sEmpUnqID = txtEmpUnqID.Text.Trim().ToString();
if (string.IsNullOrEmpty(txtEmpUnqID.Text.Trim()))
{
question = "Are You Sure to Process/Post Lunch Half Day Check For : " + sWrkGrp + Environment.NewLine
+ "Processed Data will be deleted between '" + txtFromDt.DateTime.ToString("yyyy-MM-dd") + "' And '" + txtToDate.DateTime.ToString("yyyy-MM-dd") + "' ";
sql = "Select CompCode,WrkGrp,EmpUnqID,OTFlg,WeekOff from mastemp where CompCode = '01' and Wrkgrp = '" + sWrkGrp + "' And Active = 1 Order By EmpUnqID";
}
else
{
question = "Are You Sure to to Process/Post Lunch Half Day Check For : " + txtEmpUnqID.Text.Trim().ToString() + Environment.NewLine
+ "Processed Data will be deleted between '" + txtFromDt.DateTime.ToString("yyyy-MM-dd") + "' And '" + txtToDate.DateTime.ToString("yyyy-MM-dd") + "' ";
sql = "Select CompCode,WrkGrp,EmpUnqID,OTFlg,WeekOff From MastEmp Where CompCode ='" + txtCompCode.Text.Trim() + "' "
+ " and WrkGrp = '" + sWrkGrp + "' "
+ " and EmpUnqID ='" + sEmpUnqID + "' ";
}
drq = MessageBox.Show(question, "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (drq == DialogResult.No)
{
MessageBox.Show("Process Canceled..", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
btnProcess.Enabled = false;
txtWrkGrpCode.Enabled = false;
txtEmpUnqID.Enabled = false;
txtFromDt.Enabled = false;
txtToDate.Enabled = false;
string sFromDt = txtFromDt.DateTime.ToString("yyyy-MM-dd");
string sToDt = txtToDate.DateTime.ToString("yyyy-MM-dd");
Cursor.Current = Cursors.WaitCursor;
DataSet ds = Utils.Helper.GetData(sql, Utils.Helper.constr);
bool hasRows = ds.Tables.Cast <DataTable>()
.Any(table => table.Rows.Count != 0);
if (hasRows)
{
pBar.Properties.Maximum = ds.Tables[0].Rows.Count + 1;
foreach (DataRow drs in ds.Tables[0].Rows)
{
//update progressbar
pBar.PerformStep();
pBar.Update();
sEmpUnqID = drs["EmpUnqID"].ToString();
string WeekOff = drs["WeekOff"].ToString();
//save in db for accountibility
using (SqlConnection cn = new SqlConnection(Utils.Helper.constr))
{
try
{
cn.Open();
SqlTransaction tr = cn.BeginTransaction("LunchHalfDayPost");
DataSet AttdLunchDs = new DataSet();
sql = "Select * From AttdLunchHistory where tDate " +
" Between '" + sFromDt + "' and '" + sToDt + "' and " +
" EmpUnqID = '" + drs["EmpUnqID"].ToString() + "' and ignore = 0 and posted = 0 Order By tDate Asc ";
AttdLunchDs = Utils.Helper.GetData(sql, Utils.Helper.constr);
int lngRecAffected = 0, tLeaveHalf = 0;
bool posted = false;
hasRows = AttdLunchDs.Tables.Cast <DataTable>().Any(table => table.Rows.Count != 0);
#region trsloop
if (hasRows)
{
posted = false;
foreach (DataRow trs in AttdLunchDs.Tables[0].Rows)
{
lngRecAffected = 0; tLeaveHalf = 0;
#region posting
if ((trs["LeaveStatus"].ToString()) != "" && (trs["LeaveStatus"].ToString()) == "AB")
{
lngRecAffected = 0;
tLeaveHalf = 1;
sql = "Select Count(*) from LeaveEntry where CompCode = '" + drs["CompCode"] + "' and " +
" WrkGrp = '" + drs["WrkGrp"] + "' and tYear = '" + Convert.ToDateTime(trs["tDate"]).ToString("yyyy") + "' " +
" And EmpUnqID = '" + drs["EmpUnqID"].ToString() + "' and FromDt ='" + Convert.ToDateTime(trs["tDate"]).ToString("yyyy-MM-dd") + "' and isnull(LeaveTyp,'') <> '' ";
int cnt = Convert.ToInt32(Utils.Helper.GetDescription(sql, Utils.Helper.constr));
if (cnt == 0)
{
sql = " Insert into LeaveEntry (CompCode,WrkGrp,tYear,EmpUnqID,FromDt,ToDt,LeaveTyp,TotDay," +
" WoDay,PublicHL,LeaveDed,LeaveAdv,LeaveHalf,Remark,AddDt,AddID,DelFlg)" +
" Values (" +
" '" + drs["CompCode"].ToString() + "', " +
" '" + drs["WrkGrp"].ToString() + "'," +
" '" + Convert.ToDateTime(trs["tDate"]).ToString("yyyy") + "', " +
" '" + drs["EmpUnqID"].ToString() + "'," +
" '" + Convert.ToDateTime(trs["tDate"]).ToString("yyyy-MM-dd") + "', " +
" '" + Convert.ToDateTime(trs["tDate"]).ToString("yyyy-MM-dd") + "', " +
" 'AB',0.5,0,0,0.5,0,1,'Lunch Rules', GetDate(),'Sys',0)";
SqlCommand cmd = new SqlCommand(sql, cn, tr);
cmd.CommandType = CommandType.Text;
lngRecAffected = (int)cmd.ExecuteNonQuery();
if (lngRecAffected > 0)
{
posted = true;
lngRecAffected = 0;
sql = "Insert into MastLeaveSchedule " +
"(EmpUnqID,WrkGrp,tDate,AddDt,AddID,schLeave,SchLeaveHalf) " +
" Values ('" + trs["EmpUnqID"].ToString() + "'," +
" '" + trs["WrkGrp"].ToString() + "','" + Convert.ToDateTime(trs["tDate"]).ToString("yyyy-MM-dd") + "'," +
" GetDate(),'Sys','AB','" + tLeaveHalf + "')";
cmd = new SqlCommand(sql, cn, tr);
cmd.CommandType = CommandType.Text;
lngRecAffected = (int)cmd.ExecuteNonQuery();
if (lngRecAffected > 0)
{
sql = "Update AttdLunchHistory Set Posted = 1, PostedBy ='" + Utils.User.GUserID + "' " +
" where tDate = '" + Convert.ToDateTime(trs["tDate"]).ToString("yyyy-MM-dd") + "' And " +
" EmpUnqID = '" + trs["EmpUnqID"].ToString() + "' ";
cmd = new SqlCommand(sql, cn, tr);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
}
else
{
txtError.Text += drs["EmpUnqID"].ToString() + " : " + "Transaction Failur.." + Environment.NewLine;
int res = 0;
pro.LunchInOutProcess(drs["EmpUnqID"].ToString(), Convert.ToDateTime(trs["tDate"]), Convert.ToDateTime(trs["tDate"]), out res);
}
}
#endregion posting
}//for each AttdLunchDs Rows
try
{
tr.Commit();
}
catch (Exception ex)
{
txtError.Text += txtError.Text + ex.ToString();
tr.Rollback();
continue;
}
#region processAttendance
if (posted == true)
{
//get minimum date of postedflg = true between from date and todate
//get maximum date of posted flg = true between from date and todate
string minsql = "Select Convert(date,Min(tDate),121) from AttdLunchHistory where Empunqid = '" + drs["EmpUnqID"].ToString() + "' and tDate Between '" + sFromDt + "' and '" + sToDt + "' and posted = 1";
string maxsql = "Select Convert(date,Max(tDate),121) from AttdLunchHistory where Empunqid = '" + drs["EmpUnqID"].ToString() + "' and tDate Between '" + sFromDt + "' and '" + sToDt + "' and posted = 1";
string s1dt = Utils.Helper.GetDescription(minsql, Utils.Helper.constr);
string s2dt = Utils.Helper.GetDescription(maxsql, Utils.Helper.constr);
if (!string.IsNullOrEmpty(s1dt) && !string.IsNullOrEmpty(s2dt))
{
int res = 0;
err = string.Empty;
DateTime tFromDt = Convert.ToDateTime(s1dt);
DateTime tToDt = Convert.ToDateTime(s2dt);
string tEmpUnq = drs["EmpUnqID"].ToString();
pro.AttdProcess(tEmpUnq, tFromDt, tToDt, out res, out err);
}
}
#endregion processAttendance
}//if has row
#endregion trsloop
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
} //using connection
} //foreach loop drs
}
MessageBox.Show("Process Completed..", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
Cursor.Current = Cursors.Default;
ResetCtrl();
SetRights();
}