private void btnUpload_Click(object sender, EventArgs e)
{
Boolean objvalidate;
objvalidate = ValidateControls();
if (objvalidate== false)
{
this.toolStripProgressBar1.Minimum = 1;
this.toolStripProgressBar1.Maximum = 5;
this.toolStripProgressBar1.Value = 1;
this.toolStripProgressBar1.Value = this.toolStripProgressBar1.Value + 1;
DisplayStatus("Checking excel file format...");
this.toolStripProgressBar1.Value = 5;
ExcelHandler OExcelHandler = new ExcelHandler();
DataSet dsGrpUpload = OExcelHandler.GetDataFromExcel(txtFilename.Text);
strgrpColumnarray = OExcelHandler.strColumnarray;
if (dsGrpUpload != null)
{
for (int i = 0; i <= dsGrpUpload.Tables[0].Rows.Count - 1; i++)
{
if (dsGrpUpload.Tables[0].Rows[i][strgrpColumnarray[4]] == DBNull.Value) //"Productivity"
{
dsGrpUpload.Tables[0].Rows[i][strgrpColumnarray[4]] = 0;
}
if (dsGrpUpload.Tables[0].Rows[i][strgrpColumnarray[5]] == DBNull.Value) //"Dom"
{
dsGrpUpload.Tables[0].Rows[i][strgrpColumnarray[5]] = 0;
}
if (dsGrpUpload.Tables[0].Rows[i][strgrpColumnarray[6]] == DBNull.Value) //"Intl"
{
dsGrpUpload.Tables[0].Rows[i][strgrpColumnarray[6]] = 0;
}
}
gblGrpds = null;
dsGrpUpload.AcceptChanges();
gblGrpds = dsGrpUpload;
SqlConnection sqlcon = new SqlConnection();
sqlcon = objConLivedatabase;
string strResult;
SqlTransaction objTrans = null;
////////////to be polulate from drop down////////////////////////
int intMonth = System.Convert.ToInt16(drpGrpMonth.SelectedIndex);
int intYear = System.Convert.ToInt16(drpGrpYear.SelectedItem); ;
/////////////////end////////////////////////////////////////////
string MessageBoxTitle = "Upload Group Data";
string MessageBoxContent = "GroupData is available for the selected month [" + intMonth + "] and year [" + intYear + "]." + "\n\n" + " Are you sure want to upload again ? ";
IPNetworking objIp = new IPNetworking();
objIp.GetIP4Address(out strIpaddress, out strHostname);
if (sqlcon.State == ConnectionState.Open)
{
sqlcon.Close();
}
sqlcon.Open();
objTrans = sqlcon.BeginTransaction(); //Transaction
try
{
if (gblGrpds != null)
{
if (gblGrpds.Tables.Count > 0)
{
////////////////////////// check between selection and uploaded file ///////////////////////////
if (((int)gblGrpds.Tables[0].Rows[0]["Month"] != intMonth) || ((int)gblGrpds.Tables[0].Rows[0]["Year"] != intYear))
{
MessageBox.Show("Please select the correct Month and Year.The selected file containing Month," + gblGrpds.Tables[0].Rows[0]["Month"] + " and Year," + gblGrpds.Tables[0].Rows[0]["Year"], MessageBoxTitle, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
//CASE STATUS 1 , FILE WAS LOADED FINALLY AND USE WANTS TO RELOAD AGAIN
using (SqlCommand objcmd = new SqlCommand("UP_INC_GROUP_DATA_CHECK_UPDATE", sqlcon))
{
objcmd.CommandType = CommandType.StoredProcedure;
objcmd.Parameters.AddWithValue("@MONTH", intMonth);
objcmd.Parameters.AddWithValue("@YEAR", intYear);
objcmd.Parameters.AddWithValue("@STATEMENT", "STATEMENT4");
objcmd.Parameters.AddWithValue("@IPADDRESS", strIpaddress);
SqlParameter OprmRESULT = new SqlParameter();
OprmRESULT.ParameterName = "@RESULT";
OprmRESULT.SqlDbType = SqlDbType.VarChar;
OprmRESULT.Size = 11;
OprmRESULT.Direction = ParameterDirection.Output;
objcmd.Parameters.Add(OprmRESULT);
objcmd.Transaction = objTrans; //Transaction
objcmd.ExecuteNonQuery();
strResult = (string)OprmRESULT.Value.ToString();
if (strResult == "NO")
{
MessageBox.Show("Group data available in system with status T(rue),pls contact to Admin for uploading this file.", MessageBoxTitle, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
}
//END CASE
//////////////////////////////////////////////////////////////////////////
using (SqlCommand objcmd = new SqlCommand("UP_INC_GROUP_DATA_CHECK_UPDATE", sqlcon))
{
objcmd.CommandType = CommandType.StoredProcedure;
objcmd.Parameters.AddWithValue("@MONTH", intMonth);
objcmd.Parameters.AddWithValue("@YEAR", intYear);
objcmd.Parameters.AddWithValue("@STATEMENT", "STATEMENT1");
objcmd.Parameters.AddWithValue("@IPADDRESS", strIpaddress);
SqlParameter OprmRESULT = new SqlParameter();
OprmRESULT.ParameterName = "@RESULT";
OprmRESULT.SqlDbType = SqlDbType.VarChar;
OprmRESULT.Size = 10;
OprmRESULT.Direction = ParameterDirection.Output;
objcmd.Parameters.Add(OprmRESULT);
objcmd.Transaction = objTrans;
objcmd.ExecuteNonQuery();
strResult = (string)OprmRESULT.Value.ToString();
}
if (strResult == "EXIST")
{
DialogResult dialogResult = MessageBox.Show(MessageBoxContent, MessageBoxTitle, MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (dialogResult == DialogResult.Yes)
{
using (SqlCommand objcmd = new SqlCommand("UP_INC_GROUP_DATA_CHECK_UPDATE", sqlcon))
{
objcmd.CommandType = CommandType.StoredProcedure;
objcmd.Parameters.AddWithValue("@MONTH", intMonth);
objcmd.Parameters.AddWithValue("@YEAR", intYear);
objcmd.Parameters.AddWithValue("@STATEMENT", "STATEMENT2");
objcmd.Parameters.AddWithValue("@IPADDRESS", strIpaddress);
SqlParameter OprmRESULT = new SqlParameter();
OprmRESULT.ParameterName = "@RESULT";
OprmRESULT.SqlDbType = SqlDbType.VarChar;
OprmRESULT.Size = 10;
OprmRESULT.Direction = ParameterDirection.Output;
objcmd.Parameters.Add(OprmRESULT);
objcmd.Transaction = objTrans;
objcmd.ExecuteNonQuery();
strResult = (string)OprmRESULT.Value.ToString();
}
}
else
{
return;
}
}
else if (strResult == "NOTEXIST")
{
using (SqlCommand objcmd = new SqlCommand("UP_INC_GROUP_DATA_CHECK_UPDATE", sqlcon))
{
objcmd.CommandType = CommandType.StoredProcedure;
objcmd.Parameters.AddWithValue("@MONTH", intMonth);
objcmd.Parameters.AddWithValue("@YEAR", intYear);
objcmd.Parameters.AddWithValue("@STATEMENT", "STATEMENT2");
objcmd.Parameters.AddWithValue("@IPADDRESS", strIpaddress);
SqlParameter OprmRESULT = new SqlParameter();
OprmRESULT.ParameterName = "@RESULT";
OprmRESULT.SqlDbType = SqlDbType.VarChar;
OprmRESULT.Size = 10;
OprmRESULT.Direction = ParameterDirection.Output;
objcmd.Parameters.Add(OprmRESULT);
objcmd.Transaction = objTrans;
objcmd.ExecuteNonQuery();
strResult = (string)OprmRESULT.Value.ToString();
}
}
}
using (SqlCommand objcmdDelete = new SqlCommand("DELETE FROM T_INC_NIDT_PRODUCTIVITY_GROUPDATA_DETAILS OUTPUT DELETED.[Month],DELETED.[Year],DELETED.[AirLineCode],DELETED.[Officeid],DELETED.[Productivity],DELETED.[Dom],DELETED.[Intl],DELETED.[FileName],DELETED.[FileUploadDTTI],DELETED.[IPADDRESS],'" + strIpaddress + "' INTO T_INC_NIDT_PRODUCTIVITY_GROUPDATA_DETAILS_LOG([Month],[Year],[AirLineCode],[Officeid],[Productivity],[Dom],[Intl],[FileName],[FileUploadDTTI],[IPADDRESS],[IPADDRESS_DEL]) where month=" + intMonth + " and year=" + intYear, sqlcon))
{
objcmdDelete.Transaction = objTrans;
int intDeletedRows = objcmdDelete.ExecuteNonQuery();
//MessageBox.Show(intDeletedRows + " : Rows Deleted");
}
using (SqlCommand objcmdDelete = new SqlCommand("delete from T_INC_NIDT_PRODUCTIVITY_GROUPDATA_DETAILS where month=" + intMonth + " and year=" + intYear, sqlcon))
{
objcmdDelete.Transaction = objTrans;
int intDeletedRows = objcmdDelete.ExecuteNonQuery();
//MessageBox.Show(intDeletedRows + " : Rows Deleted");
}
using (SqlCommand objcmd = new SqlCommand("UP_INC_GROUP_DATA_CHECK_UPDATE", sqlcon))
{
objcmd.CommandType = CommandType.StoredProcedure;
objcmd.Parameters.AddWithValue("@MONTH", intMonth);
objcmd.Parameters.AddWithValue("@YEAR", intYear);
objcmd.Parameters.AddWithValue("@STATEMENT", "STATEMENT3");
objcmd.Parameters.AddWithValue("@IPADDRESS", strIpaddress);
SqlParameter OprmRESULT = new SqlParameter();
OprmRESULT.ParameterName = "@RESULT";
OprmRESULT.SqlDbType = SqlDbType.VarChar;
OprmRESULT.Size = 10;
OprmRESULT.Direction = ParameterDirection.Output;
objcmd.Parameters.Add(OprmRESULT);
objcmd.Transaction = objTrans;
objcmd.ExecuteNonQuery();
strResult = (string)OprmRESULT.Value.ToString();
}
this.toolStripProgressBar1.Minimum = 1;
this.toolStripProgressBar1.Maximum = 5;
this.toolStripProgressBar1.Value = 1;
this.toolStripProgressBar1.Value = this.toolStripProgressBar1.Value + 1;
//////////////////////bulkcopy dataset to sql table/////////////////////////////
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlcon, SqlBulkCopyOptions.Default, objTrans))
{
sqlBulkCopy.DestinationTableName = "T_INC_NIDT_PRODUCTIVITY_GROUPDATA_DETAILS";
/////////////////////////////adding extra column in datatable//////////////
gblGrpds.Tables[0].Columns.Add("FileName", typeof(string));
gblGrpds.Tables[0].Columns.Add("FileUploadDTTI", typeof(DateTime));
gblGrpds.Tables[0].Columns.Add("IPADDRESS", typeof(string));
////////////////////////////end///////////////////////////////////////////
///////////////////////////////coloumn mapping////////////////////////////
sqlBulkCopy.ColumnMappings.Add(strgrpColumnarray[0], "Month");
sqlBulkCopy.ColumnMappings.Add(strgrpColumnarray[1], "Year");
sqlBulkCopy.ColumnMappings.Add(strgrpColumnarray[2], "AirLineCode");
sqlBulkCopy.ColumnMappings.Add(strgrpColumnarray[3], "Officeid");
sqlBulkCopy.ColumnMappings.Add(strgrpColumnarray[4], "Productivity");
sqlBulkCopy.ColumnMappings.Add(strgrpColumnarray[5], "Dom");
sqlBulkCopy.ColumnMappings.Add(strgrpColumnarray[6], "Intl");
sqlBulkCopy.ColumnMappings.Add("FileName", "FileName");
sqlBulkCopy.ColumnMappings.Add("FileUploadDTTI", "FileUploadDTTI");
sqlBulkCopy.ColumnMappings.Add("IPADDRESS", "IPADDRESS");
for (int i = 0; i <= gblGrpds.Tables[0].Rows.Count - 1; i++)
{
gblGrpds.Tables[0].Rows[i]["FileName"] = txtFilename.Text;
gblGrpds.Tables[0].Rows[i]["FileUploadDTTI"] = DateTime.Now;
gblGrpds.Tables[0].Rows[i]["IPADDRESS"] = strIpaddress;
}
gblGrpds.AcceptChanges();
sqlBulkCopy.WriteToServer(gblGrpds.Tables[0]);
}
objTrans.Commit();
this.toolStripProgressBar1.Value = 5;
DisplayStatus(gblGrpds.Tables[0].Rows.Count + ":Rows Added for Group data productivity..");
MessageBox.Show(gblGrpds.Tables[0].Rows.Count + " : Rows Added Sucessfully..", MessageBoxTitle);
gblGrpds = null;
}
else
{
MessageBox.Show("Dataset Cleaned.", MessageBoxTitle);
return;
}
}
catch (Exception ex)
{
MessageBox.Show("Problem in uploading.Try again may be the column is not matched with specified format." + "\n\n" + "Contact to Admin and send them error message : " + ex.Message + " \n\n" + ex.StackTrace, MessageBoxTitle, MessageBoxButtons.OK, MessageBoxIcon.Stop);
objTrans.Rollback();
}
finally
{
pbarHL.Maximum = 100;
pbarHL.Minimum = 1;
pbarHL.Value = 1;
if (sqlcon.State == ConnectionState.Open)
{
sqlcon.Close();
}
}
}
else
{
DisplayStatus("Error in excel file format...");
}
}
#endregion
}
private void frmQueryShedular_Load(object sender, EventArgs e)
{
try
{
ConfigureOutPutFolder();
ConnectToDB();
disableEnablepanel();
EnableDisableMenuItems();
fillControls();
clearControls();
grpQuery.BackgroundImage = Myresource.graybackground;
grpButton.BackgroundImage = Myresource.graybackground;
chkCODD.CheckedChanged += new EventHandler(chkCODD_CheckedChanged);
chkHX.CheckedChanged += new EventHandler(chkHX_CheckedChanged);
chkHL.CheckedChanged += new EventHandler(chkHL_CheckedChanged);
chkGrpData.CheckedChanged += new EventHandler(chkGrpData_CheckedChanged);
lblCbar.Visible = false;
lblHLbar.Visible = false;
lblHXbar.Visible = false;
pbarCODD.Visible = false;
pbarHL.Visible = false;
pbarHX.Visible = false;
grpStatusbar.Visible = false;
IPNetworking objIp = new IPNetworking();
objIp.GetIP4Address(out strIpaddress, out strHostname);
this.Text = "QUERY SHEDULAR NIDT SYSTEM IP/HOST [" + strIpaddress + "]" + "[" + strHostname + "]";
foreach (ToolStripMenuItem item in contextMenuStrip1.Items)
{
if (item.HasDropDownItems)
{
DoSubItems(item);
}
}
this.ContextMenuStrip = this.contextMenuStrip1;
foreach (Control ctrl in this.Controls)
{
ctrl.MouseDown += new MouseEventHandler(ctrl_MouseDown);
if (ctrl.GetType() == typeof(GroupBox))
{
foreach (Control ctrl1 in ((GroupBox)ctrl).Controls)
{
ctrl1.MouseDown += new MouseEventHandler(ctrl_MouseDown);
}
//((System.Windows.Forms.Button)ctrl).Enabled = false;
}
}
}
catch (Exception exep)
{
MessageBox.Show(exep.Message);
}
}