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");
            }
        }
예제 #2
0
        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);
                        }
                    }
                }
            }
        }