private void ToolStripButton1_Click(object sender, EventArgs e)
{
var ds1 = new DataSet();
var ds2 = new DataSet();
var MaxRow = ((SFeedingGrid.FocusedView as GridView).RowCount);
for (var i = 0; i < MaxRow; i++)
{
var currentrow = SFeedingGridView.GetDataRow(i);
var str1 = "Select EmpCode,EmpName from EmpMst where EmpCode='" + currentrow["EmpCode"].ToString().PadLeft(5, '0') + "'";
ds1 = ProjectFunctions.GetDataSet(str1);
if (ds1.Tables[0].Rows.Count > 0)
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["EmpName"], ds1.Tables[0].Rows[0]["EmpName"].ToString());
}
else
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["EmpCode"], "XXXXXXXXXXXXXXXXXXX");
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["EmpName"], "XXXXXXXXXXXXXXXXXXX");
XtraMessageBox.Show("Invalid Dept ");
}
}
SFeedingGridView.BestFitColumns();
}
private void OpenFileDialog1_FileOk(object sender, CancelEventArgs e)
{
SFeedingGrid.Refresh();
var xlConn = string.Empty;
xlConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + openFileDialog1.FileName + ";Extended Properties=\"Excel 12.0;\";";
using (var myCommand = new OleDbDataAdapter("SELECT MonthYear,EmpCode,EmpName,EmpDW,EmpPH,EmpEL,EmpCL,EmpSL,EmpPymtMode FROM [Sheet1$]", xlConn))
{
myCommand.Fill(dt);
SFeedingGrid.DataSource = dt;
SFeedingGridView.BestFitColumns();
}
}
private void OpenFileDialog1_FileOk(object sender, CancelEventArgs e)
{
SFeedingGrid.Refresh();
var xlConn = string.Empty;
xlConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + openFileDialog1.FileName + ";Extended Properties=\"Excel 12.0;\";";
using (var myCommand = new OleDbDataAdapter("SELECT EmpPartyCode,AccName,UnitCode,UnitName, EmpCode,EmpName,EmpFHRelationTag,EmpFHName,EmpDeptCode,DeptDesc,EmpDesgCode,DesgDesc,EmpDOJ,EmpPFno,EmpUANNo,EmpBasic,EmpHRA,EmpConv,EmpPET,EmpSplAlw,EmpTDS,EmpMscD1,EmpPanNo,EmpAdharCardNo,EmpBankIFSCode,EmpBankAcNo,EmpDOB,PFTAG,FPFTAG,ESITAG FROM[Sheet1$]", xlConn))
{
myCommand.Fill(dt);
SFeedingGrid.DataSource = dt;
SFeedingGridView.BestFitColumns();
}
}
private bool ValidateData()
{
var MaxRow = ((SFeedingGrid.FocusedView as GridView).RowCount);
for (var i = 0; i < MaxRow; i++)
{
var currentrow = SFeedingGridView.GetDataRow(i);
if (currentrow["EmpName"].ToString().Contains("x") || currentrow["EmpName"].ToString() == string.Empty)
{
XtraMessageBox.Show("Invalid Employee Name");
return(false);
}
if (currentrow["EmpDW"].ToString() == string.Empty)
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["EmpDW"], "0");
}
if (currentrow["EmpPH"].ToString() == string.Empty)
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["EmpPH"], "0");
}
if (currentrow["EmpEL"].ToString() == string.Empty)
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["EmpEL"], "0");
}
if (currentrow["EmpCL"].ToString() == string.Empty)
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["EmpCL"], "0");
}
if (currentrow["EmpSL"].ToString() == string.Empty)
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["EmpSL"], "0");
}
if (currentrow["EmpPymtMode"].ToString() == string.Empty)
{
XtraMessageBox.Show("Invalid Payment Mode");
return(false);
}
if (currentrow["MonthYear"].ToString() == string.Empty)
{
XtraMessageBox.Show("Invalid Month Year");
return(false);
}
}
return(true);
}
private void BtnSave_Click(object sender, EventArgs e)
{
var MaxRow = ((SFeedingGrid.FocusedView as GridView).RowCount);
if (ValidateData())
{
using (var con = new SqlConnection(ProjectFunctions.ConnectionString))
{
con.Open();
var cmd = new SqlCommand
{
Connection = con
};
for (var i = 0; i < MaxRow; i++)
{
var currentrow = SFeedingGridView.GetDataRow(i);
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Insert into AtnData(MonthYear,EmpCode,EmpDW,EmpPH,EmpEL,EmpCL,EmpSL,EmpPymtMode)values(@MonthYear,@EmpCode,@EmpDW,@EmpPH,@EmpEL,@EmpCL,@EmpSL,@EmpPymtMode)";
cmd.Parameters.Add("@MonthYear", SqlDbType.NVarChar).Value = currentrow["MonthYear"].ToString();
cmd.Parameters.Add("@EmpCode", SqlDbType.NVarChar).Value = currentrow["EmpCode"].ToString().PadLeft(5, '0');
cmd.Parameters.Add("@EmpDW", SqlDbType.NVarChar).Value = currentrow["EmpDW"].ToString();
cmd.Parameters.Add("@EmpPH", SqlDbType.NVarChar).Value = currentrow["EmpPH"].ToString();
cmd.Parameters.Add("@EmpEL", SqlDbType.NVarChar).Value = currentrow["EmpEL"].ToString();
cmd.Parameters.Add("@EmpCL", SqlDbType.NVarChar).Value = currentrow["EmpCL"].ToString();
cmd.Parameters.Add("@EmpSL", SqlDbType.NVarChar).Value = currentrow["EmpSL"].ToString();
cmd.Parameters.Add("@EmpPymtMode", SqlDbType.NVarChar).Value = currentrow["EmpPymtMode"].ToString();
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
}
SFeedingGrid.DataSource = null;
}
private void BtnSave_Click(object sender, EventArgs e)
{
try
{
var MaxRow = ((SFeedingGrid.FocusedView as GridView).RowCount);
if (ValidateData())
{
using (var con = new SqlConnection(ProjectFunctions.ConnectionString))
{
con.Open();
var cmd = new SqlCommand
{
Connection = con
};
for (var i = 0; i < MaxRow; i++)
{
var doc = GetNewDocumentNo().PadLeft(5, '0');
var currentrow = SFeedingGridView.GetDataRow(i);
cmd.CommandType = CommandType.Text;
//cmd.CommandText = "Insert into EmpMst(EmpPartyCode, EmpDOB,EmpCode,EmpName,EmpFHRelationTag,EmpFHName,EmpDeptCode,EmpDesgCode,EmpDOJ,EmpPFno,EmpUANNo,EmpBasic,EmpHRA,EmpConv,EmpPET,EmpSplAlw,EmpTDS,EmpMscD1,EmpPanNo,EmpAdharCardNo,EmpBankIFSCode,EmpBankAcNo,UnitCode)values(@EmpPartyCode,@EmpDOB,@EmpCode,@EmpName,@EmpFHRelationTag,@EmpFHName,@EmpDeptCode,@EmpDesgCode,@EmpDOJ,@EmpPFno,@EmpUANNo,@EmpBasic,@EmpHRA,@EmpConv,@EmpPET,@EmpSplAlw,@EmpTDS,@EmpMscD1,@EmpPanNo,@EmpAdharCardNo,@EmpBankIFSCode,@EmpBankAcNo,@UnitCode)";
cmd.CommandText = "Insert into EmpMst( EmpDOB,EmpCode,EmpName,EmpFHRelationTag,EmpFHName,EmpDeptCode,EmpDesgCode,EmpDOJ,EmpPFno,EmpUANNo,EmpBasic,EmpHRA,EmpConv,EmpPET,EmpSplAlw,EmpTDS,EmpMscD1,EmpPanNo,EmpAdharCardNo,EmpBankIFSCode,EmpBankAcNo,UnitCode,EmpPFDTag,EmpFpfDTag,EmpESIDTag)values(@EmpDOB,@EmpCode,@EmpName,@EmpFHRelationTag,@EmpFHName,@EmpDeptCode,@EmpDesgCode,@EmpDOJ,@EmpPFno,@EmpUANNo,@EmpBasic,@EmpHRA,@EmpConv,@EmpPET,@EmpSplAlw,@EmpTDS,@EmpMscD1,@EmpPanNo,@EmpAdharCardNo,@EmpBankIFSCode,@EmpBankAcNo,@UnitCode,@EmpPFDTag,@EmpFpfDTag,@EmpESIDTag)";
//cmd.Parameters.Add("@EmpPartyCode", SqlDbType.NVarChar).Value = currentrow["EmpPartyCode"].ToString();
cmd.Parameters.Add("@EmpDOB", SqlDbType.DateTime).Value = Convert.ToDateTime(currentrow["EmpDOB"]).ToString("yyyy-MM-dd");
cmd.Parameters.Add("@EmpCode", SqlDbType.NVarChar).Value = currentrow["EmpCode"].ToString().PadLeft(5, '0');
cmd.Parameters.Add("@EmpName", SqlDbType.NVarChar).Value = currentrow["EmpName"].ToString();
cmd.Parameters.Add("@EmpFHRelationTag", SqlDbType.NVarChar).Value = currentrow["EmpFHRelationTag"].ToString();
cmd.Parameters.Add("@EmpFHName", SqlDbType.NVarChar).Value = currentrow["EmpFHName"].ToString();
cmd.Parameters.Add("@EmpDeptCode", SqlDbType.NVarChar).Value = currentrow["EmpDeptCode"].ToString().PadLeft(4, '0');
cmd.Parameters.Add("@EmpDesgCode", SqlDbType.NVarChar).Value = currentrow["EmpDesgCode"].ToString().PadLeft(4, '0');
cmd.Parameters.Add("@EmpDOJ", SqlDbType.DateTime).Value = Convert.ToDateTime(currentrow["EmpDOJ"]).ToString("yyyy-MM-dd");
cmd.Parameters.Add("@EmpPFno", SqlDbType.NVarChar).Value = currentrow["EmpPFno"].ToString();
cmd.Parameters.Add("@EmpUANNo", SqlDbType.NVarChar).Value = currentrow["EmpUANNo"].ToString();
cmd.Parameters.Add("@EmpBasic", SqlDbType.NVarChar).Value = currentrow["EmpBasic"].ToString();
cmd.Parameters.Add("@EmpHRA", SqlDbType.NVarChar).Value = currentrow["EmpHRA"].ToString();
cmd.Parameters.Add("@EmpConv", SqlDbType.NVarChar).Value = currentrow["EmpConv"].ToString();
cmd.Parameters.Add("@EmpPET", SqlDbType.NVarChar).Value = currentrow["EmpPET"].ToString();
cmd.Parameters.Add("@EmpSplAlw", SqlDbType.NVarChar).Value = currentrow["EmpSplAlw"].ToString();
cmd.Parameters.Add("@EmpTDS", SqlDbType.NVarChar).Value = currentrow["EmpTDS"].ToString();
cmd.Parameters.Add("@EmpMscD1", SqlDbType.NVarChar).Value = currentrow["EmpMscD1"].ToString();
cmd.Parameters.Add("@EmpPanNo", SqlDbType.NVarChar).Value = currentrow["EmpPanNo"].ToString();
cmd.Parameters.Add("@EmpAdharCardNo", SqlDbType.NVarChar).Value = currentrow["EmpAdharCardNo"].ToString();
cmd.Parameters.Add("@EmpBankIFSCode", SqlDbType.NVarChar).Value = currentrow["EmpBankIFSCode"].ToString();
cmd.Parameters.Add("@EmpBankAcNo", SqlDbType.NVarChar).Value = currentrow["EmpBankAcNo"].ToString();
cmd.Parameters.Add("@UnitCode", SqlDbType.NVarChar).Value = currentrow["UnitCode"].ToString().PadLeft(4, '0');
cmd.Parameters.Add("@EmpPFDTag", SqlDbType.NVarChar).Value = currentrow["PFTAG"].ToString();
cmd.Parameters.Add("@EmpFpfDTag", SqlDbType.NVarChar).Value = currentrow["FPFTAG"].ToString();
cmd.Parameters.Add("@EmpESIDTag", SqlDbType.NVarChar).Value = currentrow["ESITAG"].ToString();
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
}
SFeedingGrid.DataSource = null;
}
catch (Exception ex)
{
XtraMessageBox.Show(ex.Message);
}
}
private bool ValidateData()
{
var MaxRow = ((SFeedingGrid.FocusedView as GridView).RowCount);
for (var i = 0; i < MaxRow; i++)
{
var currentrow = SFeedingGridView.GetDataRow(i);
//if (currentrow["AccName"].ToString().Contains("x") || currentrow["AccName"].ToString() == string.Empty)
//{
// XtraMessageBox.Show("Invalid Party Name");
// return false;
//}
//if (currentrow["UnitName"].ToString().Contains("x") || currentrow["UnitName"].ToString() == string.Empty)
//{
// XtraMessageBox.Show("Invalid Unit");
// return false;
//}
//if (currentrow["DeptDesc"].ToString().Contains("x") || currentrow["DeptDesc"].ToString() == string.Empty)
//{
// XtraMessageBox.Show("Invalid Department");
// return false;
//}
//if (currentrow["DesgDesc"].ToString().Contains("x") || currentrow["DesgDesc"].ToString() == string.Empty)
//{
// XtraMessageBox.Show("Invalid Designation");
// return false;
//}
//if (currentrow["EmpDOJ"].ToString() == string.Empty)
//{
// XtraMessageBox.Show("Invalid Date Of Joining");
// return false;
//}
//if (currentrow["EmpDOB"].ToString() == string.Empty)
//{
// XtraMessageBox.Show("Invalid Date Of Birth");
// return false;
//}
if (currentrow["EmpBasic"].ToString() == string.Empty)
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["EmpBasic"], "0");
}
if (currentrow["EmpHRA"].ToString() == string.Empty)
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["EmpHRA"], "0");
}
if (currentrow["EmpConv"].ToString() == string.Empty)
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["EmpConv"], "0");
}
if (currentrow["EmpPET"].ToString() == string.Empty)
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["EmpPET"], "0");
}
if (currentrow["EmpSplAlw"].ToString() == string.Empty)
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["EmpSplAlw"], "0");
}
if (currentrow["EmpTDS"].ToString() == string.Empty)
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["EmpTDS"], "0");
}
if (currentrow["EmpMscD1"].ToString() == string.Empty)
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["EmpMscD1"], "0");
}
//if (currentrow["EmpAdharCardNo"].ToString() == string.Empty)
//{
// XtraMessageBox.Show("Invalid Aadhaar Card No");
// return false;
//}
}
return(true);
}
private void ToolStripButton1_Click(object sender, EventArgs e)
{
var ds1 = new DataSet();
var ds2 = new DataSet();
var ds3 = new DataSet();
var ds4 = new DataSet();
var MaxRow = ((SFeedingGrid.FocusedView as GridView).RowCount);
for (var i = 0; i < MaxRow; i++)
{
var currentrow = SFeedingGridView.GetDataRow(i);
var str1 = "Select DeptCode,DeptDesc from DeptMst where DeptCode='" + currentrow["EmpDeptCode"].ToString().PadLeft(4, '0') + "'";
ds1 = ProjectFunctions.GetDataSet(str1);
var str2 = "Select DesgCode,DesgDesc from DesgMst where DesgCode='" + currentrow["EmpDesgCode"].ToString().PadLeft(4, '0') + "'";
ds2 = ProjectFunctions.GetDataSet(str2);
var str3 = "Select UnitCode,UnitName from UnitMst where UnitCode='" + currentrow["UnitCode"].ToString().PadLeft(4, '0') + "'";
ds3 = ProjectFunctions.GetDataSet(str3);
var str4 = "Select AccCode,AccName from ActMst where AccCode='" + currentrow["EmpPartyCode"].ToString().PadLeft(4, '0') + "'";
ds4 = ProjectFunctions.GetDataSet(str4);
if (ds1.Tables[0].Rows.Count > 0)
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["DeptDesc"], ds1.Tables[0].Rows[0]["DeptDesc"].ToString());
}
else
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["EmpDeptCode"], "XXXXXXXXXXXXXXXXXXX");
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["DeptDesc"], "XXXXXXXXXXXXXXXXXXX");
XtraMessageBox.Show("Invalid Dept ");
}
if (ds2.Tables[0].Rows.Count > 0)
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["DesgDesc"], ds2.Tables[0].Rows[0]["DesgDesc"].ToString());
}
else
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["EmpDesgCode"], "XXXXXXXXXXXXXX");
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["DesgDesc"], "XXXXXXXXXXXXXX");
XtraMessageBox.Show("Invalid Designation ");
}
if (ds3.Tables[0].Rows.Count > 0)
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["UnitName"], ds3.Tables[0].Rows[0]["UnitName"].ToString());
}
else
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["UnitCode"], "XXXXXXXXXXXXXX");
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["UnitName"], "XXXXXXXXXXXXXX");
XtraMessageBox.Show("Invalid Unit ");
}
if (ds4.Tables[0].Rows.Count > 0)
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["AccName"], ds4.Tables[0].Rows[0]["AccName"].ToString());
}
else
{
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["EmpPartyCode"], "XXXXXXXXXXXXXX");
SFeedingGridView.SetRowCellValue(i, SFeedingGridView.Columns["AccName"], "XXXXXXXXXXXXXX");
XtraMessageBox.Show("Invalid Unit ");
}
}
SFeedingGridView.BestFitColumns();
}
