public OfficeViewModel()
{
//So it can load in all the products from the database.
productRepository = new ProductRepository();
departmentRepository = new DeparmentRepository();
employeeRepository = new EmployeeRepository();
using (var db = new Model.AalborgZooContainer())
{
OrderList = db.OrderLineSet.Include("ProductVersion.Product").ToList();
}
readFromExcelFile = new ReadFromExcelFile();
//To load in the products from the database.
AllProducts = productRepository.GetAllProducts();
//To load in the departments from the database.
AllDepartments = departmentRepository.GetDepartments();
//To load in the zookeepers from the database.
AllZookeepers = employeeRepository.GetAllZookeepers();
}
public static void InserOrder(DataGridView dataGridView)
{
if (dataGridView.SelectedRows.Count > 0)
{
connection = new SqlConnection();
try
{
DialogResult result = MessageBox.Show("Do you want to save selected record?", "Save Record To Database", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
connection.ConnectionString = ConfigurationManager.ConnectionStrings["MagentoAPIConnect"].ConnectionString;
string query = "INSERT INTO [dbo].[M2SalesOrder] ([BillNo],[BillDate],[VoucherType],[Godown],[CustomerName],[Address1],[Address2],[Address3],[Address4]" +
",[Country],[State],[GSTRegistrationType],[GSTNo],[SalesLedger],[ProductName],SKU,[BatchNo],[TaxRate],[ManufacturingDate],[ExpiryDate],[Quantity],[Unit]" +
",[RateInclusiveOfTax],[RateBasic],[DiscPercent],[Amount],[DeliveryCharges],[CGST],[SGST],[IGST],[RoundOff],[TotalAmount],[CreditDebitCardLedgerName]" +
",[CashLedgerName],[Narration],[HSN])" +
"VALUES(@BillNo,@BillDate,@VoucherType,@Godown,@CustomerName,@Address1,@Address2,@Address3,@Address4,@Country,@State,@GSTRegistrationType,@GSTNo,@SalesLedger" +
",@ProductName,@SKU,@BatchNo,@TaxRate,@ManufacturingDate,@ExpiryDate,@Quantity,@Unit,@RateInclusiveOfTax,@RateBasic,@DiscPercent,@Amount,@DeliveryCharges" +
",@CGST,@SGST,@IGST,@RoundOff,@TotalAmount,@CreditDebitCardLedgerName,@CashLedgerName,@Narration,@HSN)";
command = new SqlCommand(query, connection);
SqlParameter parmBillNo = new SqlParameter("@BillNo", SqlDbType.VarChar, 50);
SqlParameter parmBillDate = new SqlParameter("@BillDate", SqlDbType.DateTime);
SqlParameter parmVoucherType = new SqlParameter("@VoucherType", SqlDbType.VarChar, 50);
SqlParameter parmGodown = new SqlParameter("@Godown", SqlDbType.VarChar, 50);
SqlParameter parmCustomerName = new SqlParameter("@CustomerName", SqlDbType.VarChar, 250);
SqlParameter parmAddress1 = new SqlParameter("@Address1", SqlDbType.Text);
SqlParameter parmAddress2 = new SqlParameter("@Address2", SqlDbType.Text);
SqlParameter parmAddress3 = new SqlParameter("@Address3", SqlDbType.Text);
SqlParameter parmAddress4 = new SqlParameter("@Address4", SqlDbType.Text);
SqlParameter parmCountry = new SqlParameter("@Country", SqlDbType.VarChar, 50);
SqlParameter parmState = new SqlParameter("@State", SqlDbType.VarChar, 50);
SqlParameter parmGSTRegistrationType = new SqlParameter("@GSTRegistrationType", SqlDbType.VarChar, 50);
SqlParameter parmGSTNo = new SqlParameter("@GSTNo", SqlDbType.VarChar, 50);
SqlParameter parmSalesLedger = new SqlParameter("@SalesLedger", SqlDbType.VarChar, 50);
SqlParameter parmProductName = new SqlParameter("@ProductName", SqlDbType.VarChar, 250);
SqlParameter parmSku = new SqlParameter("@SKU", SqlDbType.VarChar, 50);
SqlParameter parmBatchNo = new SqlParameter("@BatchNo", SqlDbType.VarChar, 50);
SqlParameter parmTaxRate = new SqlParameter("@TaxRate", SqlDbType.VarChar, 50);
SqlParameter parmManufacturingDate = new SqlParameter("@ManufacturingDate", SqlDbType.DateTime);
SqlParameter parmExpiryDate = new SqlParameter("@ExpiryDate", SqlDbType.DateTime);
SqlParameter parmQuantity = new SqlParameter("@Quantity", SqlDbType.SmallInt);
SqlParameter parmUnit = new SqlParameter("@Unit", SqlDbType.VarChar, 50);
SqlParameter parmRateInclusiveOfTax = new SqlParameter("@RateInclusiveOfTax", SqlDbType.Money);
SqlParameter parmRateBasic = new SqlParameter("@RateBasic", SqlDbType.Money);
SqlParameter parmDiscPercent = new SqlParameter("@DiscPercent", SqlDbType.VarChar, 50);
SqlParameter parmAmount = new SqlParameter("@Amount", SqlDbType.Money);
SqlParameter parmDeliveryCharges = new SqlParameter("@DeliveryCharges", SqlDbType.Money);
SqlParameter parmCGST = new SqlParameter("@CGST", SqlDbType.Money);
SqlParameter parmSGST = new SqlParameter("@SGST", SqlDbType.Money);
SqlParameter parmIGST = new SqlParameter("@IGST", SqlDbType.Money);
SqlParameter parmRoundOff = new SqlParameter("@RoundOff", SqlDbType.Money);
SqlParameter parmTotalAmount = new SqlParameter("@TotalAmount", SqlDbType.Money);
SqlParameter parmCreditDebitCardLedgerName = new SqlParameter("@CreditDebitCardLedgerName", SqlDbType.VarChar, 250);
SqlParameter parmCashLedgerName = new SqlParameter("@CashLedgerName", SqlDbType.VarChar, 250);
SqlParameter parmNarration = new SqlParameter("@Narration", SqlDbType.VarChar, 250);
SqlParameter parmHSN = new SqlParameter("@HSN", SqlDbType.VarChar, 50);
command.Parameters.Add(parmBillNo);
command.Parameters.Add(parmBillDate);
command.Parameters.Add(parmVoucherType);
command.Parameters.Add(parmGodown);
command.Parameters.Add(parmCustomerName);
command.Parameters.Add(parmAddress1);
command.Parameters.Add(parmAddress2);
command.Parameters.Add(parmAddress3);
command.Parameters.Add(parmAddress4);
command.Parameters.Add(parmCountry);
command.Parameters.Add(parmState);
command.Parameters.Add(parmGSTRegistrationType);
command.Parameters.Add(parmGSTNo);
command.Parameters.Add(parmSalesLedger);
command.Parameters.Add(parmProductName);
command.Parameters.Add(parmSku);
command.Parameters.Add(parmBatchNo);
command.Parameters.Add(parmTaxRate);
command.Parameters.Add(parmManufacturingDate);
command.Parameters.Add(parmExpiryDate);
command.Parameters.Add(parmQuantity);
command.Parameters.Add(parmUnit);
command.Parameters.Add(parmRateInclusiveOfTax);
command.Parameters.Add(parmRateBasic);
command.Parameters.Add(parmDiscPercent);
command.Parameters.Add(parmAmount);
command.Parameters.Add(parmDeliveryCharges);
command.Parameters.Add(parmCGST);
command.Parameters.Add(parmSGST);
command.Parameters.Add(parmIGST);
command.Parameters.Add(parmRoundOff);
command.Parameters.Add(parmTotalAmount);
command.Parameters.Add(parmCreditDebitCardLedgerName);
command.Parameters.Add(parmCashLedgerName);
command.Parameters.Add(parmNarration);
command.Parameters.Add(parmHSN);
foreach (DataGridViewRow row in dataGridView.SelectedRows)
{
parmBillNo.Value = row.Cells[0].Value.ToString();
parmBillDate.Value = Convert.ToDateTime(row.Cells[1].Value.ToString());
parmVoucherType.Value = row.Cells[2].Value;
parmGodown.Value = row.Cells[3].Value;
parmCustomerName.Value = row.Cells[4].Value;
parmAddress1.Value = row.Cells[5].Value;
parmAddress2.Value = row.Cells[6].Value;
parmAddress3.Value = row.Cells[7].Value;
parmAddress4.Value = row.Cells[8].Value;
parmCountry.Value = row.Cells[9].Value;
parmState.Value = row.Cells[10].Value;
parmGSTRegistrationType.Value = row.Cells[11].Value;
parmGSTNo.Value = row.Cells[12].Value;
parmSalesLedger.Value = row.Cells[13].Value;
parmProductName.Value = row.Cells[14].Value;
parmBatchNo.Value = row.Cells[15].Value;
parmTaxRate.Value = row.Cells[16].Value.ToString();
string ManufacturingDate = row.Cells[17].Value.ToString();
if (ManufacturingDate.Equals(""))
{
parmManufacturingDate.Value = DBNull.Value;
}
else
{
parmManufacturingDate.Value = DateTime.Parse(ManufacturingDate);
}
string ExpiryDate = row.Cells[18].Value.ToString();
if (ExpiryDate.Equals(""))
{
parmExpiryDate.Value = DBNull.Value;
}
else
{
parmExpiryDate.Value = DateTime.Parse(ExpiryDate);
}
parmQuantity.Value = row.Cells[19].Value;
parmUnit.Value = row.Cells[20].Value;
string RateInclusiveOfTax = row.Cells[21].Value.ToString();
if (RateInclusiveOfTax.Equals(""))
{
RateInclusiveOfTax = "0";
}
parmRateInclusiveOfTax.Value = Convert.ToDouble(RateInclusiveOfTax);
string RateBasic = row.Cells[22].Value.ToString();
if (RateBasic.Equals("0"))
{
RateBasic = "0";
}
parmRateBasic.Value = Convert.ToDouble(RateBasic);
string DiscPercent = row.Cells[23].Value.ToString();
if (DiscPercent.Equals(""))
{
DiscPercent = "0";
}
parmDiscPercent.Value = Convert.ToDouble(DiscPercent);
string Amount = row.Cells[24].Value.ToString();
if (Amount.Equals(""))
{
Amount = "0";
}
parmAmount.Value = Convert.ToDouble(Amount);
string DeliveryCharges = row.Cells[25].Value.ToString();
if (DeliveryCharges.Equals(""))
{
DeliveryCharges = "0";
}
parmDeliveryCharges.Value = Convert.ToDouble(DeliveryCharges);
string CGST = row.Cells[26].Value.ToString();
if (CGST.Equals(""))
{
CGST = "0";
}
parmCGST.Value = Convert.ToDouble(CGST);
string SGST = row.Cells[27].Value.ToString();
if (SGST.Equals(""))
{
SGST = "0";
}
parmSGST.Value = Convert.ToDouble(SGST);
string IGST = row.Cells[28].Value.ToString();
if (IGST.Equals(""))
{
IGST = "0";
}
parmIGST.Value = Convert.ToDouble(IGST);
string RoundOff = row.Cells[29].Value.ToString();
if (RoundOff.Equals(""))
{
RoundOff = "0";
}
parmRoundOff.Value = Convert.ToDouble(RoundOff);
string TotalAmount = row.Cells[30].Value.ToString();
if (TotalAmount.Equals(""))
{
TotalAmount = "0";
}
parmTotalAmount.Value = Convert.ToDouble(TotalAmount);
parmCreditDebitCardLedgerName.Value = row.Cells[31].Value;
parmCashLedgerName.Value = row.Cells[32].Value;
parmNarration.Value = row.Cells[33].Value;
string sku = row.Cells[34].Value.ToString();
string hsn = ReadFromExcelFile.ReadExcelFile(sku);
parmHSN.Value = hsn;
parmSku.Value = sku;
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
command.ExecuteNonQuery();
}
MessageBox.Show("Data saved successfully");
}
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
finally
{
if (connection != null)
{
connection.Close();
}
if (command != null)
{
command.Dispose();
}
}
}
else
{
if (dataGridView.Rows.Count > 0)
{
connection = new SqlConnection();
try
{
DialogResult result = MessageBox.Show("Do you want to save all the record?", "Save Record To Database", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
connection.ConnectionString = ConfigurationManager.ConnectionStrings["MagentoAPIConnect"].ConnectionString;
string query = "INSERT INTO [dbo].[M2SalesOrder] ([BillNo],[BillDate],[VoucherType],[Godown],[CustomerName],[Address1],[Address2],[Address3],[Address4]" +
",[Country],[State],[GSTRegistrationType],[GSTNo],[SalesLedger],[ProductName],SKU,[BatchNo],[TaxRate],[ManufacturingDate],[ExpiryDate],[Quantity],[Unit]" +
",[RateInclusiveOfTax],[RateBasic],[DiscPercent],[Amount],[DeliveryCharges],[CGST],[SGST],[IGST],[RoundOff],[TotalAmount],[CreditDebitCardLedgerName]" +
",[CashLedgerName],[Narration],[HSN])" +
"VALUES(@BillNo,@BillDate,@VoucherType,@Godown,@CustomerName,@Address1,@Address2,@Address3,@Address4,@Country,@State,@GSTRegistrationType,@GSTNo,@SalesLedger" +
",@ProductName,@SKU,@BatchNo,@TaxRate,@ManufacturingDate,@ExpiryDate,@Quantity,@Unit,@RateInclusiveOfTax,@RateBasic,@DiscPercent,@Amount,@DeliveryCharges" +
",@CGST,@SGST,@IGST,@RoundOff,@TotalAmount,@CreditDebitCardLedgerName,@CashLedgerName,@Narration,@HSN)";
command = new SqlCommand(query, connection);
SqlParameter parmBillNo = new SqlParameter("@BillNo", SqlDbType.VarChar, 50);
SqlParameter parmBillDate = new SqlParameter("@BillDate", SqlDbType.DateTime);
SqlParameter parmVoucherType = new SqlParameter("@VoucherType", SqlDbType.VarChar, 50);
SqlParameter parmGodown = new SqlParameter("@Godown", SqlDbType.VarChar, 50);
SqlParameter parmCustomerName = new SqlParameter("@CustomerName", SqlDbType.VarChar, 250);
SqlParameter parmAddress1 = new SqlParameter("@Address1", SqlDbType.Text);
SqlParameter parmAddress2 = new SqlParameter("@Address2", SqlDbType.Text);
SqlParameter parmAddress3 = new SqlParameter("@Address3", SqlDbType.Text);
SqlParameter parmAddress4 = new SqlParameter("@Address4", SqlDbType.Text);
SqlParameter parmCountry = new SqlParameter("@Country", SqlDbType.VarChar, 50);
SqlParameter parmState = new SqlParameter("@State", SqlDbType.VarChar, 50);
SqlParameter parmGSTRegistrationType = new SqlParameter("@GSTRegistrationType", SqlDbType.VarChar, 50);
SqlParameter parmGSTNo = new SqlParameter("@GSTNo", SqlDbType.VarChar, 50);
SqlParameter parmSalesLedger = new SqlParameter("@SalesLedger", SqlDbType.VarChar, 50);
SqlParameter parmProductName = new SqlParameter("@ProductName", SqlDbType.VarChar, 250);
SqlParameter parmSku = new SqlParameter("@SKU", SqlDbType.VarChar, 50);
SqlParameter parmBatchNo = new SqlParameter("@BatchNo", SqlDbType.VarChar, 50);
SqlParameter parmTaxRate = new SqlParameter("@TaxRate", SqlDbType.VarChar, 50);
SqlParameter parmManufacturingDate = new SqlParameter("@ManufacturingDate", SqlDbType.DateTime);
SqlParameter parmExpiryDate = new SqlParameter("@ExpiryDate", SqlDbType.DateTime);
SqlParameter parmQuantity = new SqlParameter("@Quantity", SqlDbType.SmallInt);
SqlParameter parmUnit = new SqlParameter("@Unit", SqlDbType.VarChar, 50);
SqlParameter parmRateInclusiveOfTax = new SqlParameter("@RateInclusiveOfTax", SqlDbType.Money);
SqlParameter parmRateBasic = new SqlParameter("@RateBasic", SqlDbType.Money);
SqlParameter parmDiscPercent = new SqlParameter("@DiscPercent", SqlDbType.VarChar, 50);
SqlParameter parmAmount = new SqlParameter("@Amount", SqlDbType.Money);
SqlParameter parmDeliveryCharges = new SqlParameter("@DeliveryCharges", SqlDbType.Money);
SqlParameter parmCGST = new SqlParameter("@CGST", SqlDbType.Money);
SqlParameter parmSGST = new SqlParameter("@SGST", SqlDbType.Money);
SqlParameter parmIGST = new SqlParameter("@IGST", SqlDbType.Money);
SqlParameter parmRoundOff = new SqlParameter("@RoundOff", SqlDbType.Money);
SqlParameter parmTotalAmount = new SqlParameter("@TotalAmount", SqlDbType.Money);
SqlParameter parmCreditDebitCardLedgerName = new SqlParameter("@CreditDebitCardLedgerName", SqlDbType.VarChar, 250);
SqlParameter parmCashLedgerName = new SqlParameter("@CashLedgerName", SqlDbType.VarChar, 250);
SqlParameter parmNarration = new SqlParameter("@Narration", SqlDbType.VarChar, 250);
SqlParameter parmHSN = new SqlParameter("@HSN", SqlDbType.VarChar, 50);
command.Parameters.Add(parmBillNo);
command.Parameters.Add(parmBillDate);
command.Parameters.Add(parmVoucherType);
command.Parameters.Add(parmGodown);
command.Parameters.Add(parmCustomerName);
command.Parameters.Add(parmAddress1);
command.Parameters.Add(parmAddress2);
command.Parameters.Add(parmAddress3);
command.Parameters.Add(parmAddress4);
command.Parameters.Add(parmCountry);
command.Parameters.Add(parmState);
command.Parameters.Add(parmGSTRegistrationType);
command.Parameters.Add(parmGSTNo);
command.Parameters.Add(parmSalesLedger);
command.Parameters.Add(parmProductName);
command.Parameters.Add(parmSku);
command.Parameters.Add(parmBatchNo);
command.Parameters.Add(parmTaxRate);
command.Parameters.Add(parmManufacturingDate);
command.Parameters.Add(parmExpiryDate);
command.Parameters.Add(parmQuantity);
command.Parameters.Add(parmUnit);
command.Parameters.Add(parmRateInclusiveOfTax);
command.Parameters.Add(parmRateBasic);
command.Parameters.Add(parmDiscPercent);
command.Parameters.Add(parmAmount);
command.Parameters.Add(parmDeliveryCharges);
command.Parameters.Add(parmCGST);
command.Parameters.Add(parmSGST);
command.Parameters.Add(parmIGST);
command.Parameters.Add(parmRoundOff);
command.Parameters.Add(parmTotalAmount);
command.Parameters.Add(parmCreditDebitCardLedgerName);
command.Parameters.Add(parmCashLedgerName);
command.Parameters.Add(parmNarration);
command.Parameters.Add(parmHSN);
foreach (DataGridViewRow row in dataGridView.Rows)
{
parmBillNo.Value = row.Cells[0].Value.ToString();
parmBillDate.Value = Convert.ToDateTime(row.Cells[1].Value.ToString());
parmVoucherType.Value = row.Cells[2].Value;
parmGodown.Value = row.Cells[3].Value;
parmCustomerName.Value = row.Cells[4].Value;
parmAddress1.Value = row.Cells[5].Value;
parmAddress2.Value = row.Cells[6].Value;
parmAddress3.Value = row.Cells[7].Value;
parmAddress4.Value = row.Cells[8].Value;
parmCountry.Value = row.Cells[9].Value;
parmState.Value = row.Cells[10].Value;
parmGSTRegistrationType.Value = row.Cells[11].Value;
parmGSTNo.Value = row.Cells[12].Value;
parmSalesLedger.Value = row.Cells[13].Value;
parmProductName.Value = row.Cells[14].Value;
parmBatchNo.Value = row.Cells[15].Value;
parmTaxRate.Value = row.Cells[16].Value.ToString();
string ManufacturingDate = row.Cells[17].Value.ToString();
if (ManufacturingDate.Equals(""))
{
parmManufacturingDate.Value = DBNull.Value;
}
else
{
parmManufacturingDate.Value = DateTime.Parse(ManufacturingDate);
}
string ExpiryDate = row.Cells[18].Value.ToString();
if (ExpiryDate.Equals(""))
{
parmExpiryDate.Value = DBNull.Value;
}
else
{
parmExpiryDate.Value = DateTime.Parse(ExpiryDate);
}
parmQuantity.Value = row.Cells[19].Value;
parmUnit.Value = row.Cells[20].Value;
string RateInclusiveOfTax = row.Cells[21].Value.ToString();
if (RateInclusiveOfTax.Equals(""))
{
RateInclusiveOfTax = "0";
}
parmRateInclusiveOfTax.Value = Convert.ToDouble(RateInclusiveOfTax);
string RateBasic = row.Cells[22].Value.ToString();
if (RateBasic.Equals("0"))
{
RateBasic = "0";
}
parmRateBasic.Value = Convert.ToDouble(RateBasic);
string DiscPercent = row.Cells[23].Value.ToString();
if (DiscPercent.Equals(""))
{
DiscPercent = "0";
}
parmDiscPercent.Value = Convert.ToDouble(DiscPercent);
string Amount = row.Cells[24].Value.ToString();
if (Amount.Equals(""))
{
Amount = "0";
}
parmAmount.Value = Convert.ToDouble(Amount);
string DeliveryCharges = row.Cells[25].Value.ToString();
if (DeliveryCharges.Equals(""))
{
DeliveryCharges = "0";
}
parmDeliveryCharges.Value = Convert.ToDouble(DeliveryCharges);
string CGST = row.Cells[26].Value.ToString();
if (CGST.Equals(""))
{
CGST = "0";
}
parmCGST.Value = Convert.ToDouble(CGST);
string SGST = row.Cells[27].Value.ToString();
if (SGST.Equals(""))
{
SGST = "0";
}
parmSGST.Value = Convert.ToDouble(SGST);
string IGST = row.Cells[28].Value.ToString();
if (IGST.Equals(""))
{
IGST = "0";
}
parmIGST.Value = Convert.ToDouble(IGST);
string RoundOff = row.Cells[29].Value.ToString();
if (RoundOff.Equals(""))
{
RoundOff = "0";
}
parmRoundOff.Value = Convert.ToDouble(RoundOff);
string TotalAmount = row.Cells[30].Value.ToString();
if (TotalAmount.Equals(""))
{
TotalAmount = "0";
}
parmTotalAmount.Value = Convert.ToDouble(TotalAmount);
parmCreditDebitCardLedgerName.Value = row.Cells[31].Value;
parmCashLedgerName.Value = row.Cells[32].Value;
parmNarration.Value = row.Cells[33].Value;
string sku = row.Cells[34].Value.ToString();
string hsn = ReadFromExcelFile.ReadExcelFile(sku);
parmHSN.Value = hsn;
parmSku.Value = sku;
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
command.ExecuteNonQuery();
}
MessageBox.Show("Data saved successfully");
}
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
finally
{
if (connection != null)
{
connection.Close();
}
if (command != null)
{
command.Dispose();
}
}
}
else
{
MessageBox.Show("There is no record.");
}
}
}
