private void LoadSheet()
{
dt.Rows.Clear();
DevExpress.Spreadsheet.Workbook workbook = new DevExpress.Spreadsheet.Workbook();
workbook.LoadDocument(openFileDialog1.FileName);
DevExpress.Spreadsheet.Worksheet worksheet = workbook.Worksheets[i];
if (i <= workbook.Sheets.Count)
{
txtStoreCode.Text = worksheet.Name;
DataTable TempTable = new DataTable();
string xlConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtAddress.Text + ";Extended Properties=\"Excel 12.0;\";";
using (var myCommand = new OleDbDataAdapter("SELECT [EAN Code],[PI Qty] as [PI Qty] FROM [" + txtStoreCode.Text + "$]", xlConn))
{
myCommand.Fill(TempTable);
if (TempTable.Rows.Count > 0)
{
foreach (DataRow dr in TempTable.Rows)
{
DataSet ds = ProjectFunctions.GetDataSet("sp_LoadEANDataNew '" + dr["EAN Code"].ToString() + "'");
if (ds.Tables[0].Rows.Count > 0)
{
DataRow dataRow = dt.NewRow();
dataRow["ARTNO"] = ds.Tables[0].Rows[0]["ARTNO"].ToString();
dataRow["ARTALIAS"] = ds.Tables[0].Rows[0]["ARTALIAS"].ToString();
dataRow["VARIANTART"] = ds.Tables[0].Rows[0]["VARIANT ART"].ToString();
dataRow["ARTMRP"] = ds.Tables[0].Rows[0]["ARTMRP"].ToString();
dataRow["SIZE"] = ds.Tables[0].Rows[0]["SIZE"].ToString();
dataRow["COLOR"] = ds.Tables[0].Rows[0]["COLOR"].ToString();
dataRow["COLSYSID"] = ds.Tables[0].Rows[0]["COLSYSID"].ToString();
dataRow["SZSYSID"] = ds.Tables[0].Rows[0]["SZSYSID"].ToString();
dataRow["ARTSYSID"] = ds.Tables[0].Rows[0]["ARTSYSID"].ToString();
dataRow["GrpDesc"] = ds.Tables[0].Rows[0]["GrpDesc"].ToString();
dataRow["GrpSubDesc"] = ds.Tables[0].Rows[0]["GrpSubDesc"].ToString();
if (dr["PI Qty"].ToString().Trim().Length == 0)
{
dr["PI Qty"] = 0;
}
dataRow["PIQty"] = Convert.ToDecimal(dr["PI Qty"]);
dataRow["EANNo"] = Convert.ToDecimal(dr["EAN Code"]);
dataRow["GrpHSNCode"] = ds.Tables[0].Rows[0]["GrpHSNCode"].ToString();
dt.Rows.Add(dataRow);
}
}
InfoGrid.DataSource = dt;
InfoGridView.BestFitColumns();
}
}
}
else
{
XtraMessageBox.Show("PIS Created Successfully");
}
}
private void Form1_Load(object sender, EventArgs e)
{
#region #datasource
DevExpress.Spreadsheet.Workbook wb =
new DevExpress.Spreadsheet.Workbook();
wb.LoadDocument("Employees.xlsx");
DevExpress.Spreadsheet.RangeDataSourceOptions options =
new DevExpress.Spreadsheet.RangeDataSourceOptions();
options.UseFirstRowAsHeader = true;
options.CellValueConverter = new MyPictureProvider(wb.Worksheets[0]);
options.DataSourceColumnTypeDetector = new MyColumnDetector();
string dsName = wb.Worksheets[0].Tables[0].Name;
object ds = wb.Worksheets[0].Tables[0].GetDataSource(options);
snapControl1.DataSources.Add(dsName, ds);
#endregion #datasource
snapControl1.CreateNewDocument();
SnapList list = snapControl1.Document.CreateSnList(snapControl1.Document.Range.End, "EmployeeList");
list.BeginUpdate();
list.DataSourceName = dsName;
SnapDocument listHeader = list.ListHeader;
Table listHeaderTable = listHeader.Tables.Create(listHeader.Range.End, 1, 3);
TableCellCollection listHeaderCells = listHeaderTable.FirstRow.Cells;
listHeader.InsertText(listHeaderCells[0].ContentRange.End, "First Name");
listHeader.InsertText(listHeaderCells[1].ContentRange.End, "Last Name");
listHeader.InsertText(listHeaderCells[2].ContentRange.End, "Photo");
SnapDocument listRow = list.RowTemplate;
Table listRowTable = listRow.Tables.Create(listRow.Range.End, 1, 3);
TableCellCollection listRowCells = listRowTable.FirstRow.Cells;
listRow.CreateSnText(listRowCells[0].ContentRange.End, "\"First Name\"");
listRow.CreateSnText(listRowCells[1].ContentRange.End, "\"Last Name\"");
listRow.CreateSnImage(listRowCells[2].ContentRange.End, "Photo");
list.EndUpdate();
list.Field.Update();
}
private void BtnSave_Click(object sender, EventArgs e)
{
SplashScreenManager.ShowForm(this, typeof(WaitForm1), true, true, false);
SplashScreenManager.Default.SetWaitFormDescription("Loading Data ");
if (ValidateData())
{
InfoGridView.CloseEditor();
InfoGridView.UpdateCurrentRow();
using (var sqlcon = new SqlConnection(ProjectFunctions.GetConnection()))
{
sqlcon.Open();
var sqlcom = sqlcon.CreateCommand();
var transaction = sqlcon.BeginTransaction("SaveTransaction");
sqlcom.Connection = sqlcon;
sqlcom.Transaction = transaction;
sqlcom.CommandType = CommandType.Text;
try
{
if (S1 == "&Add")
{
txtPINo.Text = GetNewInvoiceDocumentNo().PadLeft(6, '0');
sqlcom.CommandText = "Insert into PIMstNew(PINo,PIDate,PIStoreCode)values(@PINo,@PIDate,@PIStoreCode)";
sqlcom.Parameters.AddWithValue("@PINo", txtPINo.Text.Trim());
sqlcom.Parameters.AddWithValue("@PIDate", Convert.ToDateTime(txtPIDate.Text));
sqlcom.Parameters.AddWithValue("@PIStoreCode", txtStoreCode.Text.Trim());
sqlcom.ExecuteNonQuery();
sqlcom.Parameters.Clear();
}
if (S1 == "Edit")
{
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = "Update PIMstNew Set PIStoreCode=@PIStoreCode Where PINo = @PINo And PIDate=@PIDate ";
sqlcom.Parameters.AddWithValue("@PINo", txtPINo.Text.Trim());
sqlcom.Parameters.AddWithValue("@PIDate", Convert.ToDateTime(txtPIDate.Text));
sqlcom.Parameters.AddWithValue("@PIStoreCode", txtStoreCode.Text.Trim());
sqlcom.Parameters.Clear();
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = " Delete from PIDataNew Where PINo = @PINo And PIDate=@PIDate ";
sqlcom.Parameters.AddWithValue("@PINo", txtPINo.Text.Trim());
sqlcom.Parameters.AddWithValue("@PIDate", Convert.ToDateTime(txtPIDate.Text));
sqlcom.ExecuteNonQuery();
sqlcom.Parameters.Clear();
}
SplashScreenManager.Default.SetWaitFormDescription("Saving Data ");
//foreach (DataRow dr in dt.Rows)
//{
// sqlcom.CommandType = CommandType.Text;
// sqlcom.CommandText = " Insert into PIDataNew"
// + " (PINo,PIDate,PIQty,EANNo)"
// + " values(@PINo,@PIDate,@PIQty,@EANNo)";
// sqlcom.Parameters.AddWithValue("@PINo", txtPINo.Text.Trim());
// sqlcom.Parameters.AddWithValue("@PIDate", Convert.ToDateTime(txtPIDate.Text));
// sqlcom.Parameters.AddWithValue("@PIQty", Convert.ToDecimal(dr["PIQty"]));
// sqlcom.Parameters.AddWithValue("@EANNo", dr["EANNo"].ToString());
// sqlcom.ExecuteNonQuery();
// sqlcom.Parameters.Clear();
//}
DataTable dtFinal = new DataTable();
dtFinal.Columns.Add("PINo", typeof(string));
dtFinal.Columns.Add("PIDate", typeof(string));
dtFinal.Columns.Add("PIQty", typeof(string));
dtFinal.Columns.Add("EANNo", typeof(string));
dtFinal.Rows.Clear();
foreach (DataRow dr in (InfoGrid.DataSource as DataTable).Rows)
{
DataRow drRow = dtFinal.NewRow();
drRow["PINo"] = txtPINo.Text.Trim();
drRow["PIDate"] = Convert.ToDateTime(txtPIDate.Text).ToString("yyyy-MM-dd");
drRow["PIQty"] = dr["PIQty"].ToString();
drRow["EANNo"] = dr["EANNo"].ToString();
dtFinal.Rows.Add(drRow);
}
dtFinal.AcceptChanges();
sqlcom.CommandType = CommandType.StoredProcedure;
sqlcom.CommandText = "sp_InsertPINew";
sqlcom.CommandTimeout = 600000;
SqlParameter param = new SqlParameter
{
ParameterName = "@PerformaInv",
Value = dtFinal
};
sqlcom.Parameters.Add(param);
sqlcom.ExecuteNonQuery();
sqlcom.Parameters.Clear();
transaction.Commit();
//ProjectFunctions.SpeakError("PI Data Saved Successfully");
sqlcon.Close();
if (FromExcel == "Y")
{
// SplashScreenManager.Default.SetWaitFormDescription("Saving PI " + i.ToString());
i++;
LoadSheet();
DevExpress.Spreadsheet.Workbook workbook = new DevExpress.Spreadsheet.Workbook();
workbook.LoadDocument(openFileDialog1.FileName);
DevExpress.Spreadsheet.Worksheet worksheet = workbook.Worksheets[i];
if (i <= workbook.Sheets.Count)
{
BtnSave_Click(null, e);
}
else
{
SplashScreenManager.CloseForm(false);
Close();
}
}
else
{
SplashScreenManager.CloseForm(false);
}
Close();
FromExcel = "N";
}
catch (Exception ex)
{
ProjectFunctions.SpeakError("Something Wrong. \n I am going to Roll Back." + ex.Message);
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
ProjectFunctions.SpeakError("Something Wrong. \n Roll Back Failed." + ex2.Message);
}
}
}
}
}