private void txtEmpUnqID_Validated(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtEmpUnqID.Text.Trim()) || string.IsNullOrEmpty(txtCompCode.Text.Trim()))
{
mode = "NEW";
oldCode = "";
return;
}
clsEmp t = new clsEmp();
t.CompCode = txtCompCode.Text.Trim();
t.EmpUnqID = txtEmpUnqID.Text.Trim();
bool isold = t.GetEmpDetails(t.CompCode, t.EmpUnqID);
if (isold)
{
DisplayData(t);
}
else
{
mode = "NEW";
oldCode = "";
}
SetRights();
}
private string ValidateBulk(DataRow dr)
{
string err = string.Empty;
clsEmp t = new clsEmp();
t.CompCode = "01";
t.EmpUnqID = dr["EmpUnqID"].ToString().Trim();
t.GetEmpDetails(t.CompCode, t.EmpUnqID);
if (!t.Active)
{
err += "Please select active EmpUnqID..." + Environment.NewLine;
}
DateTime tToDate, tFromDt;
try
{
tFromDt = Convert.ToDateTime(dr["FromDate"].ToString());
}
catch (Exception ex)
{
err += "invalid from date" + Environment.NewLine;
return(err);
}
try
{
tToDate = Convert.ToDateTime(dr["ToDate"].ToString());
}
catch (Exception ex)
{
err += "invalid to date" + Environment.NewLine;
return(err);
}
if (tToDate < tFromDt)
{
err += "Invalid Date Range..." + Environment.NewLine;
}
//' added on 18-09-2014
TimeSpan ts = (tFromDt - tToDate);
if (Math.Abs(ts.TotalDays) > 31)
{
err += "Please Contact To System Administrator for More than 31 days Process" + Environment.NewLine;
}
string tyearmt = tFromDt.ToString("yyyyMM");
string pyearmt = Convert.ToDateTime(Utils.Helper.GetDescription("SELECT DateAdd(month, -1, Convert(date, GetDate()));", Utils.Helper.constr)).ToString("yyyyMM");
if (Convert.ToInt32(tyearmt) < Convert.ToInt32(pyearmt))
{
err += "Please Contact To System Administrator for Previous Month Process" + Environment.NewLine;
}
return(err);
}
private void txtEmpUnqID_Validated(object sender, EventArgs e)
{
cEmp = new clsEmp();
if (string.IsNullOrEmpty(txtEmpUnqID.Text.Trim()) || string.IsNullOrEmpty(txtCompCode.Text.Trim()))
{
IsValid = false;
return;
}
bool t = cEmp.GetEmpDetails(txtCompCode.Text.Trim().ToString(), txtEmpUnqID.Text.Trim().ToString());
if (t)
{
DisplayData();
}
else
{
IsValid = false;
ResetCtrl();
}
if (this.EmpUnqIDValidated != null)
{
this.EmpUnqIDValidated(sender, e);
}
}
private string DataValidate(string tEmpUnqID, DateTime tValidFrom, DateTime tValidTo)
{
string err = string.Empty;
clsEmp t = new clsEmp();
t.CompCode = "01";
t.EmpUnqID = tEmpUnqID;
if (!t.GetEmpDetails(t.CompCode, t.EmpUnqID))
{
err = err + "Invalid/InActive EmpUnqID..." + Environment.NewLine;
}
else
{
if (!t.Active)
{
err = err + "Invalid/InActive EmpUnqID..." + Environment.NewLine;
}
}
if (t.WrkGrp == "COMP")
{
err = err + "System Does Not Allowed for Company Onroll Employee..." + Environment.NewLine;
return(err);
}
if (tValidFrom == DateTime.MinValue)
{
err = err + "Please Enter Valid From Date..." + Environment.NewLine;
return(err);
}
if (tValidTo == DateTime.MinValue)
{
err = err + "Please Enter Valid To Date..." + Environment.NewLine;
return(err);
}
if (tValidFrom > tValidTo)
{
err = err + "Valid From Date must be less than Valid To Date..." + Environment.NewLine;
return(err);
}
if (tValidFrom < t.JoinDt)
{
err = err + "Valid From Date must be grator than Join Date..." + Environment.NewLine;
return(err);
}
if (tValidTo < t.JoinDt)
{
err = err + "Valid To Date must be grator than Join Date..." + Environment.NewLine;
return(err);
}
return(err);
}
private string DataValidate(DataRow tdr)
{
string err = string.Empty;
clsEmp t = new clsEmp();
t.CompCode = "01";
t.EmpUnqID = tdr["EmpUnqID"].ToString();
if (!t.GetEmpDetails(t.CompCode, t.EmpUnqID))
{
err = err + "Invalid/InActive EmpUnqID..." + Environment.NewLine;
}
return(err);
}
private void ctrlEmpValidateEvent_Handler(object sender, EventArgs e)
{
Emp.CompCode = ctrlEmp1.txtCompCode.Text.Trim();
Emp.EmpUnqID = ctrlEmp1.txtEmpUnqID.Text.Trim();
if (Emp.GetEmpDetails(Emp.CompCode, Emp.EmpUnqID))
{
mode = "OLD";
}
else
{
mode = "NEW";
Emp = new clsEmp();
}
DisplayData(Emp);
SetRights();
}
private string DataValidate(string tEmpUnqID, string tCostCode, DateTime tValidFrom)
{
string err = string.Empty;
clsEmp t = new clsEmp();
t.CompCode = "01";
t.EmpUnqID = tEmpUnqID;
if (!t.GetEmpDetails(t.CompCode, t.EmpUnqID))
{
err = err + "Invalid/InActive EmpUnqID..." + Environment.NewLine;
}
t.CostCode = tCostCode;
t.GetCostDesc(tCostCode);
if (string.IsNullOrEmpty(t.CostDesc))
{
err = err + "Invalid CostCode..." + Environment.NewLine;
}
if (tValidFrom == DateTime.MinValue)
{
err = err + "Please Enter Valid From Date..." + Environment.NewLine;
return(err);
}
string sql = "Select max(ValidFrom) From MastCostCodeEmp where EmpUnqId = '" + t.EmpUnqID + "' " +
" and ValidFrom > '" + tValidFrom.ToString("yyyy-MM-dd") + "'";
string tMaxDt = Utils.Helper.GetDescription(sql, Utils.Helper.constr);
if (!string.IsNullOrEmpty(tMaxDt))
{
err = err + "System Does not Allow to insert/update/delete between...." + Environment.NewLine;
}
return(err);
}
private string DataValidate(string tEmpUnqID, string tLeaveTyp)
{
string err = string.Empty;
clsEmp t = new clsEmp();
t.CompCode = "01";
t.EmpUnqID = tEmpUnqID;
if (!t.GetEmpDetails(t.CompCode, t.EmpUnqID))
{
err = err + "Invalid/InActive EmpUnqID..." + Environment.NewLine;
return(err);
}
if (string.IsNullOrEmpty(tLeaveTyp))
{
err = err + "Invalid Leave Type..." + Environment.NewLine;
return(err);
}
if (LeaveTable.Rows.Count > 0)
{
DataRow[] dr = LeaveTable.Select("WrkGrp='" + t.WrkGrp + "'");
foreach (DataRow tdr in dr)
{
string tl = tdr["Leaves"].ToString();
if (!tl.Contains(tLeaveTyp))
{
err = err + "Leave Type is not Balanced..." + Environment.NewLine;
}
}
}
else
{
err = err + "No Such Leave Type is configured..." + Environment.NewLine;
}
return(err);
}
private void btnAdd_Click(object sender, EventArgs e)
{
string err = string.Empty;
if (string.IsNullOrEmpty(ctrlEmp1.txtCompCode.Text.Trim()) || string.IsNullOrEmpty(ctrlEmp1.txtCompDesc.Text.Trim()))
{
err += "Please Select Company Code..." + Environment.NewLine;
}
if (string.IsNullOrEmpty(ctrlEmp1.txtEmpUnqID.Text.Trim()))
{
err += "Please Select EmpUnqID..." + Environment.NewLine;
}
clsEmp t = new clsEmp();
t.CompCode = ctrlEmp1.txtCompCode.Text.Trim();
t.EmpUnqID = ctrlEmp1.txtEmpUnqID.Text.Trim();
t.GetEmpDetails(t.CompCode, t.EmpUnqID);
if (!t.Active)
{
err += "Please select active EmpUnqID..." + Environment.NewLine;
}
if (txtAppFromDt.EditValue == null)
{
err += "Please select from date..." + Environment.NewLine;
}
if (txtAppToDate.EditValue == null)
{
err += "Please select to date..." + Environment.NewLine;
}
if (!string.IsNullOrEmpty(err))
{
MessageBox.Show(err, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (txtAppToDate.DateTime < txtAppFromDt.DateTime)
{
MessageBox.Show("Invalid Date Range...", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//' added on 18-09-2014
TimeSpan ts = (txtAppFromDt.DateTime - txtAppToDate.DateTime);
if (Math.Abs(ts.TotalDays) > 31)
{
MessageBox.Show("Please Contact To System Administrator for More than 31 days Process", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string tyearmt = txtAppFromDt.DateTime.ToString("yyyyMM");
string pyearmt = Convert.ToDateTime(Utils.Helper.GetDescription("SELECT DateAdd(month, -1, Convert(date, GetDate()));", Utils.Helper.constr)).ToString("yyyyMM");
if (Convert.ToInt32(tyearmt) < Convert.ToInt32(pyearmt))
{
MessageBox.Show("Please Contact To System Administrator for Previous Month Process", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//check for duplicate employee...
foreach (DataRow dr in ProcessList.Rows)
{
if (dr["EmpUnqID"].ToString() == t.EmpUnqID)
{
MessageBox.Show("Employee already exist...", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
DataRow tdr = ProcessList.NewRow();
tdr["EmpUnqID"] = t.EmpUnqID;
tdr["EmpName"] = t.EmpName;
tdr["FromDate"] = txtAppFromDt.DateTime;
tdr["ToDate"] = txtAppToDate.DateTime;
tdr["Status"] = "Pending";
tdr["IsDone"] = false;
ProcessList.Rows.Add(tdr);
ctrlEmp1.ResetCtrl();
gridApp.Refresh();
CalcProcessed();
}
private void btnCreate_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;
}
}
string sql = string.Empty;
string question = string.Empty;
DialogResult drq;
if (string.IsNullOrEmpty(txtEmpUnqID.Text.Trim()))
{
question = "Are You Sure to create Muster Table For : " + txtWrkGrpCode.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,EmpUnqID,WeekOff From MastEmp Where CompCode ='" + txtCompCode.Text.Trim() + "' and WrkGrp = '" + txtWrkGrpCode.Text.Trim() + "' And Active = '1' Order By EmpUnqID";
}
else
{
question = "Are You Sure to create Muster Table 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,EmpUnqID,WeekOff From MastEmp Where CompCode ='" + txtCompCode.Text.Trim() + "' "
+ " and WrkGrp = '" + txtWrkGrpCode.Text.Trim() + "' "
+ " and EmpUnqID ='" + txtEmpUnqID.Text.Trim() + "' "
+ " And Active = '1' Order By EmpUnqID";
}
drq = MessageBox.Show(question, "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (drq == DialogResult.No)
{
MessageBox.Show("Process Canceled..", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
btnCreate.Enabled = false;
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 dr in ds.Tables[0].Rows)
{
//update progressbar
pBar.PerformStep();
pBar.Update();
string EmpUnqID = dr["EmpUnqID"].ToString();
string WeekOff = dr["WeekOff"].ToString();
string CompCode = dr["CompCode"].ToString();
DateTime fromdt = txtFromDt.DateTime;
DateTime todt = txtToDate.DateTime;
clsEmp t = new clsEmp();
if (t.GetEmpDetails(CompCode, EmpUnqID))
{
string err1 = string.Empty;
bool t1 = t.CreateMuster(fromdt, todt, out err1);
if (!t1)
{
MessageBox.Show(err1, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
} //foreach loop
}
MessageBox.Show("Muster Table Created", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
Cursor.Current = Cursors.Default;
ResetCtrl();
SetRights();
}
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 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 btnUpdate_Click(object sender, EventArgs e)
{
string err = DataValidate();
bool WrkGrpChange = false;
if (!string.IsNullOrEmpty(err))
{
MessageBox.Show(err, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (dupadhar)
{
string msg = "This Adhar No is Already Registered with " + dupadharemp + Environment.NewLine
+ " Are you sure to Update this Adhar No ?";
DialogResult qdr = MessageBox.Show(msg, "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (qdr == DialogResult.No)
{
return;
}
}
GrpMain.Enabled = false;
Cursor.Current = Cursors.WaitCursor;
using (SqlConnection cn = new SqlConnection(Utils.Helper.constr))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
cn.Open();
cmd.Connection = cn;
string sql = "insert into MastEmpHistory " +
" select 'Before Update Master Data, Action By " + Utils.User.GUserID + "', GetDate(), * from MastEmp where CompCode = '" + txtCompCode.Text.Trim() + "' " +
" and EmpUnqID ='" + txtEmpUnqID.Text.Trim() + "'";
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
clsEmp t = new clsEmp();
t.CompCode = txtCompCode.Text.Trim();
t.EmpUnqID = txtEmpUnqID.Text.Trim();
t.GetEmpDetails(t.CompCode, t.EmpUnqID);
//WrkGrp is Changed.. need to process in all tables..
if (t.WrkGrp != txtWrkGrpCode.Text.Trim())
{
try
{
sql = "Exec ChangeWrkGrp '" + t.EmpUnqID + "','" + txtWrkGrpCode.Text.Trim() + "','" + t.WrkGrp + "';";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
WrkGrpChange = true;
}
catch (Exception ex)
{
WrkGrpChange = false;
GrpMain.Enabled = true;
Cursor.Current = Cursors.Default;
MessageBox.Show("Kindly Clear the Job Profile First,(EmpTypeCode,CatCode,GradeCode,DesgCode,DeptCode,StatCode)" + Environment.NewLine +
"and try again..."
, "WrkGrp Change Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
else
{
WrkGrpChange = false;
}
if (string.IsNullOrEmpty(txtBasic.Text.Trim()))
{
txtBasic.Text = "0";
}
if (string.IsNullOrEmpty(txtBAAll.Text.Trim()))
{
txtBAAll.Text = "0";
}
if (string.IsNullOrEmpty(txtSplALL.Text.Trim()))
{
txtSplALL.Text = "0";
}
sql = "Update MastEmp set WrkGrp ='{0}',EmpName='{1}',FatherName = '{2}'," +
" UnitCode = '{3}',MessCode={4},MessGrpCode = {5},BirthDt ='{6}',JoinDt ='{7}',ValidFrom = {8},ValidTo = {9}," +
" ADHARNO = '{10}',IDPRF3No = '{11}',Sex='{12}',ContractFlg='{13}',PayrollFlg='{14}',OTFLG='{15}',Weekoff='{16}',UpdDt=GetDate(),UpdID ='{17}'," +
" Basic='{18}' , ValidityExpired = '{19}' , SPLALL = '{20}', BAALL = '{21}' Where " +
" CompCode ='{22}' and EmpUnqID = '{23}'";
sql = string.Format(sql, txtWrkGrpCode.Text.Trim().ToString(), txtEmpName.Text.Trim().ToString(), txtFatherName.Text.Trim(),
txtUnitCode.Text.ToString(), ((txtMessCode.Text.Trim() == "") ? "null" : "'" + txtMessCode.Text.Trim() + "'"),
((txtMessGrpCode.Text.Trim() == "") ? "null" : "'" + txtMessGrpCode.Text.Trim() + "'"),
txtBirthDT.DateTime.ToString("yyyy-MM-dd"), txtJoinDt.DateTime.ToString("yyyy-MM-dd"),
((txtWrkGrpCode.Text.Trim() == "COMP")? "null" :"'" + txtValidFrom.DateTime.ToString("yyyy-MM-dd") + "'"),
((txtWrkGrpCode.Text.Trim() == "COMP")? "null" :"'" + txtValidTo.DateTime.ToString("yyyy-MM-dd") + "'"),
txtAdharNo.Text.Trim(), txtAdharNo.Text.Trim(), ((Convert.ToBoolean(txtGender.EditValue))?1:0),
((chkCont.Checked) ? 1 : 0), ((chkComp.Checked) ? 1 : 0), ((chkOTFlg.Checked) ? 1 : 0), txtWeekOff.Text.Trim(),
Utils.User.GUserID,
txtBasic.Text.Trim(),
((txtWrkGrpCode.Text.Trim() == "COMP") ? "0" : (txtValidTo.DateTime > DateTime.Now?"0":"1")),
txtSplALL.Text.Trim().ToString(),
txtBAAll.Text.Trim().ToString(),
txtCompCode.Text.Trim(), txtEmpUnqID.Text.Trim()
);
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
if (WrkGrpChange)
{
MessageBox.Show("Employee Job Profile is Discarded.." + Environment.NewLine +
"Please Fill the Employee Job Profile Again.."
, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
MessageBox.Show("Record Updated...", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
ResetCtrl();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
GrpMain.Enabled = true;
Cursor.Current = Cursors.Default;
}
private void btnAdd_Click(object sender, EventArgs e)
{
string err = DataValidate();
if (!string.IsNullOrEmpty(err))
{
MessageBox.Show(err, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (mode == "NEW" && dupadhar)
{
string msg = "This Adhar No is Already Registered with " + dupadharemp + Environment.NewLine
+ " Are you sure to Insert this as New Employee ?";
DialogResult qdr = MessageBox.Show(msg, "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (qdr == DialogResult.No)
{
return;
}
}
Cursor.Current = Cursors.WaitCursor;
GrpMain.Enabled = false;
using (SqlConnection cn = new SqlConnection(Utils.Helper.constr))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
if (string.IsNullOrEmpty(txtBasic.Text.Trim()))
{
txtBasic.Text = "0";
}
if (string.IsNullOrEmpty(txtBAAll.Text.Trim()))
{
txtBAAll.Text = "0";
}
if (string.IsNullOrEmpty(txtSplALL.Text.Trim()))
{
txtSplALL.Text = "0";
}
cn.Open();
cmd.Connection = cn;
string sql = "Insert into MastEmp (CompCode,WrkGrp,EmpUnqID,EmpName,FatherName," +
" UnitCode,MessCode,MessGrpCode,BirthDt,JoinDt,ValidFrom,ValidTo," +
" ADHARNO,IDPRF3,IDPRF3No,Sex,ContractFlg,PayrollFlg,OTFLG,Weekoff,Active,AddDt,AddID,Basic,ValidityExpired,SPLALL,BAALL) Values (" +
"'{0}','{1}','{2}','{3}','{4}' ," +
" '{5}',{6},{7},'{8}','{9}',{10},{11}," +
" '{12}','ADHARCARD','{13}','{14}','{15}','{16}','{17}','{18}','1',GetDate(),'{19}','{20}','{21}','{22}','{23}')";
sql = string.Format(sql, txtCompCode.Text.Trim().ToString(), txtWrkGrpCode.Text.Trim().ToString(), txtEmpUnqID.Text.Trim().ToString(), txtEmpName.Text.Trim().ToString(), txtFatherName.Text.Trim(),
txtUnitCode.Text.ToString(), ((txtMessCode.Text.Trim() == "")? "null" :"'" + txtMessCode.Text.Trim() + "'"),
((txtMessGrpCode.Text.Trim() == "")? "null" :"'" + txtMessGrpCode.Text.Trim() + "'"),
txtBirthDT.DateTime.ToString("yyyy-MM-dd"), txtJoinDt.DateTime.ToString("yyyy-MM-dd"),
((txtWrkGrpCode.Text.Trim() == "COMP") ? "null" : "'" + txtValidFrom.DateTime.ToString("yyyy-MM-dd") + "'"),
((txtWrkGrpCode.Text.Trim() == "COMP") ? "null" : "'" + txtValidTo.DateTime.ToString("yyyy-MM-dd") + "'"),
txtAdharNo.Text.Trim(), txtAdharNo.Text.Trim(), ((Convert.ToBoolean(txtGender.EditValue))?1:0),
((chkCont.Checked)?1:0), ((chkComp.Checked)?1:0), ((chkOTFlg.Checked)?1:0), txtWeekOff.Text.Trim(),
Utils.User.GUserID, txtBasic.Text.Trim().ToString(), 0,
txtSplALL.Text.Trim().ToString(), txtBAAll.Text.Trim().ToString()
);
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
//createmuster
clsEmp t = new clsEmp();
string err2 = string.Empty;
if (t.GetEmpDetails(txtCompCode.Text.Trim(), txtEmpUnqID.Text.Trim()))
{
DateTime sFromDt, sToDt, sCurDt;
sCurDt = Convert.ToDateTime(Utils.Helper.GetDescription("Select GetDate()", Utils.Helper.constr));
if (txtJoinDt.DateTime.Year < sCurDt.Year)
{
sFromDt = Convert.ToDateTime(Utils.Helper.GetDescription("Select CalendarStartOfYearDate from dbo.F_TABLE_DATE(GetDate(),GetDate())", Utils.Helper.constr));
sToDt = Convert.ToDateTime(Utils.Helper.GetDescription("Select CalendarEndOfYearDate from dbo.F_TABLE_DATE(GetDate(),GetDate())", Utils.Helper.constr));
}
else
{
sFromDt = txtJoinDt.DateTime;
sToDt = Convert.ToDateTime(Utils.Helper.GetDescription("Select CalendarEndOfYearDate from dbo.F_TABLE_DATE('" + sFromDt.ToString("yyyy-MM-dd") + "','" + sFromDt.ToString("yyyy-MM-dd") + "')", Utils.Helper.constr));
}
if (!t.CreateMuster(sFromDt, sToDt, out err2))
{
MessageBox.Show(err, "Error While Creating Muster Table", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
if (string.IsNullOrEmpty(err2))
{
MessageBox.Show("Record saved...", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Record saved with error please check muster table created...", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
ResetCtrl();
}catch (Exception ex) {
MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
GrpMain.Enabled = true;
Cursor.Current = Cursors.Default;
}
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;
}