private void btnUpdate_Click(object sender, EventArgs e)
{
if (Validation())
{
try
{
dataAcess.BeginTransaction();
if (dataAcess.getDBCommand().Transaction == null)
{
dataAcess.SetDBTransaction();
}
foreach (int id in DeletedIds)
{
manageProduct.DeleteFormulaConfigByConfigId(id, dataAcess);
}
foreach (DataRow dr in dsMain.Tables["FormulaConfig"].Rows)
{
if (dr.RowState != DataRowState.Deleted)
{
manageProduct.InsertUpdateFormulaConfig(Convert.ToInt32(dr["FormulaConfigId"]), Convert.ToInt32(dr["WarehouseId"]), Convert.ToInt32(dr["ProductId"]), Convert.ToInt32(dr["UnitId"]), Convert.ToInt32(dr["WarehouseSequance"]), Convert.ToInt32(dr["ProductSequance"]), dataAcess);
}
}
dataAcess.TransCommit();
MessageBox.Show("Product Formula Config Update Successfull", "Record Updated", MessageBoxButtons.OK, MessageBoxIcon.Information);
DeletedIds = new List <int>();
setupGrip();
}
catch (SqlException sqlex)
{
MessageBox.Show(sqlex.Message);
dataAcess.TransRollback();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataAcess.ConnectionClose();
}
}
}
private void PostVoucher()
{
try
{
dataAcess.BeginTransaction();
int count = dtVocherDetail.Select("IsSelect = 1").Length;
int current = 0;
foreach (DataRow dr in dtVocherDetail.Rows)
{
if (Convert.ToBoolean(dr["IsSelect"]))
{
if (!manageVoucher.PostVoucher(dr["VoucherNumber"].ToString(), dataAcess))
{
throw new Exception("Data Not Post on Voucher No :" + dr["VoucherNumber"].ToString());
}
current++;
pgBar.Value = current / count * 100;
}
}
dataAcess.TransCommit();
MessageBox.Show("Record Posting Sucessfully.", "Record Posted", MessageBoxButtons.OK, MessageBoxIcon.Information);
button1_Click(null, null);
}
catch (SqlException sqlex)
{
dataAcess.TransRollback();
MessageBox.Show(sqlex.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataAcess.ConnectionClose();
pgBar.Value = 0;
}
}
private bool ImportExcelData(string strFilePath)
{
bool result = true;
string sSheetName = null;
string sConnection = null;
DataTable dtTablesList = new DataTable();
OleDbCommand oleExcelCommand = new OleDbCommand();
OleDbDataReader oleExcelReader;
OleDbConnection oleExcelConnection = new OleDbConnection();
DataTable dtExcelData = new DataTable();
DataTable dtExcelDataFinal = new DataTable();
Smartworks.DAL dataAccess = new Smartworks.DAL();
string extension = Path.GetExtension(strFilePath);
switch (extension)
{
case ".xls":
//sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFilePath + ";Extended Properties=Excel 12.0;";
break;
case ".xlsx":
sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFilePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
break;
default:
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
}
//String constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended Properties='Excel 8.0;HDR=YES;';";
// sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended Properties='Excel 8.0;HDR=YES;';";
oleExcelConnection = new OleDbConnection(sConnection);
try
{
oleExcelConnection.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "File does not read.", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
dtTablesList = oleExcelConnection.GetSchema("Tables");
int SheetIndex = 0;
foreach (DataRow drSheet in dtTablesList.Rows)
{
SheetIndex = SheetIndex + 1;
sSheetName = drSheet["TABLE_NAME"].ToString();
if (!sSheetName.Contains("$"))
{
continue;
}
//dtTablesList.Clear();
//dtTablesList.Dispose();
if (!string.IsNullOrEmpty(sSheetName))
{
oleExcelCommand = oleExcelConnection.CreateCommand();
oleExcelCommand.CommandText = "Select * From [" + sSheetName + "]";
oleExcelCommand.CommandType = CommandType.Text;
oleExcelReader = oleExcelCommand.ExecuteReader();
dtExcelData.Load(oleExcelReader, LoadOption.PreserveChanges);
oleExcelReader.Close();
}
if (SheetIndex == dtTablesList.Rows.Count)
{
oleExcelConnection.Close();
}
if (dtExcelData.Rows.Count > 0)
{
//if (dtExcelData.Columns.Count > 10 || dtExcelData.Columns.Count < 10)
//{
// MessageBox.Show("File Format does not match. Make sure your format is as per Sample.", "Format does not match", MessageBoxButtons.OK, MessageBoxIcon.Error);
// oleExcelConnection.Close();
// result = false;
// return result;
//}
//dtExcelData.Rows.RemoveAt(0);
/* yeh New format file hai */
dtExcelData.Columns[0].ColumnName = "ItemName";
dtExcelData.Columns[1].ColumnName = "CategoryName";
if (dtExcelDataFinal.Columns.Count == 0)
{
dtExcelDataFinal = dtExcelData.Clone();
}
foreach (DataRow dtRow in dtExcelData.Rows)
{
dtExcelDataFinal.ImportRow(dtRow);
}
}
}
if (dtExcelDataFinal.Columns.Count > 0)
{
if (dtExcelDataFinal.Rows.Count > 0)
{
int index = 0;
try
{
dataAccess.BeginTransaction();
for (int i = 0; i <= dtExcelDataFinal.Rows.Count - 1; i++)
{
index = i;
DataRow drImportRow = dtExcelDataFinal.Rows[i];
if (!string.IsNullOrEmpty(drImportRow["ItemName"].ToString()))
{
Smartworks.ColumnField[] importCustomer = new Smartworks.ColumnField[2];
importCustomer[0] = new Smartworks.ColumnField("@ItemName", drImportRow["ItemName"].ToString().Trim());
importCustomer[1] = new Smartworks.ColumnField("@CategoryName", drImportRow["CategoryName"].ToString().Trim());
dataAccess.ExecuteStoredProcedure("sp_ImportProduct", importCustomer);
}
}
dataAccess.TransCommit();
// dataAccess.TransRollback();
}
catch (SqlException sqlex)
{
dataAccess.TransRollback();
dataAccess.ConnectionClose();
//Page.ClientScript.RegisterStartupScript(GetType(), "msgbox", "alert('Error!!.. File Date not Import.');", true);
MessageBox.Show("Error!!.. File Data not Import", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
result = false;
return(result);
}
catch (Exception ex)
{
dataAccess.ConnectionClose();
//Page.ClientScript.RegisterStartupScript(GetType(), "msgbox", "alert('Error!!.. File Date not Import.');", true);
MessageBox.Show("Error!!.. File Data not Import", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
result = false;
return(result);
}
}
}
return(result);
}
private void btnAdd_Click(object sender, EventArgs e)
{
if (Validation())
{
try
{
ProcessingManager manageProcessing = new ProcessingManager();
DataTable dtWTMaster = new DataTable();
int EmployeeId = manageEmployee.GetEmployeeIdByCode(txtEmployeeCode.Text);
if (EmployeeId < 0)
{
MessageBox.Show("Employee does not found.", "Employee Identity not Found", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
int ProductId = -1;
if (!string.IsNullOrEmpty(txtPCode.Text))
{
ProductId = manageProduct.GetProductIdByCode(txtPCode.Text);
}
dataAcess.BeginTransaction();
if (dataAcess.getDBCommand().Transaction == null)
{
dataAcess.SetDBTransaction();
}
dtWTMaster = managewarehouse.InsertWTMaster(EmployeeId, Convert.ToInt32(cmbFWarehouse.SelectedValue), Convert.ToInt32(cmbTWarehouse.SelectedValue), dtpDate.Value, txtSONo.Text, ProductId, txtRemarks.Text, false, false, "H", MainForm.User_Id, DateTime.Now, "", chkProcess.Checked, (string.IsNullOrEmpty(txtProcessQty.Text) ? 0 : Convert.ToDecimal(txtProcessQty.Text)), txtBatchNo.Text, dataAcess);
if (dtWTMaster.Rows.Count == 0)
{
MessageBox.Show("Department Transfer Insert Error", "Can not Insert", MessageBoxButtons.OK, MessageBoxIcon.Error);
throw new Exception();
}
else
{
WTMasterId = Convert.ToInt32(dtWTMaster.Rows[0]["Id"]);
}
foreach (DataRow dr in dsMain.Tables["WTDetail"].Rows)
{
int DetailId = managewarehouse.InsertUpdateWTDetail(Convert.ToInt32(dr["WTDetailId"]), WTMasterId, Convert.ToInt32(dr["ProductId"]), "H", "Stock", "", "Stock", Convert.ToDecimal(dr["QtyToTransfer"]), MainForm.User_Id, DateTime.Now, "", dataAcess);
manageProduct.InsertProductLedger(Convert.ToInt32(dr["ProductId"]), dtpDate.Value, dtWTMaster.Rows[0]["TransferNo"].ToString(), DetailId, "O", Convert.ToDecimal(dr["QtyToTransfer"]), 0, 0, 0, 0, 0, MainForm.User_Id, DateTime.Now.Date, "", "", null, "Transfer Out from Warehouse Transfer", Convert.ToInt32(cmbFWarehouse.SelectedValue), -1, "", dataAcess);
manageProduct.InsertProductLedger(Convert.ToInt32(dr["ProductId"]), dtpDate.Value, dtWTMaster.Rows[0]["TransferNo"].ToString(), DetailId, "I", Convert.ToDecimal(dr["QtyToTransfer"]), 0, 0, 0, 0, 0, MainForm.User_Id, DateTime.Now.Date, "", "", null, " Transfer Inn from Warehouse Transfer ", Convert.ToInt32(cmbTWarehouse.SelectedValue), -1, "", dataAcess);
}
if (chkProcess.Checked)
{
manageProcessing.InsertUpdateProductionLedger(Convert.ToInt32(cmbTWarehouse.SelectedValue), ProductId, -1, txtSONo.Text, dtpDate.Value, txtWTCode.Text,
WTMasterId, "I", (string.IsNullOrEmpty(txtProcessQty.Text.ToString()) ? 0 : Convert.ToDecimal(txtProcessQty.Text)), -1, "Entry From Department Transfer", false, dataAcess);
}
dataAcess.TransCommit();
MessageBox.Show("Warehouse Transfer Insert Successfull", "Record Inserted", MessageBoxButtons.OK, MessageBoxIcon.Information);
ButtonRights(false);
ClearFeilds();
}
catch (SqlException sqlex)
{
MessageBox.Show(sqlex.Message);
dataAcess.TransRollback();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataAcess.ConnectionClose();
}
}
}