//Generic load data:
public static void load_data(string proc, DataGridView dv, ListBox lb)
{
try
{
SqlConnection sql_con = new SqlConnection(MainClass.connection());
SqlCommand command = new SqlCommand(proc, sql_con);
command.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable dat = new DataTable();
adapter.Fill(dat);
for (int i = 0; i < lb.Items.Count; i++)
{
string colName1 = ((DataGridViewColumn)lb.Items[i]).Name;
dv.Columns[colName1].DataPropertyName = dat.Columns[i].ToString();
}
dv.DataSource = dat;
}
catch (Exception ex)
{
MainClass.ShowMsg(ex.Message, "Error");
}
}
public static void LoadList(string proc, ComboBox cb, string valueMember, string displayMember)
{
try
{
var sql = new SqlConnection(MainClass.connection());
var cmd = new SqlCommand(proc, sql);
cmd.CommandType = CommandType.StoredProcedure;
var da = new SqlDataAdapter(cmd);
var dt = new DataTable();
da.Fill(dt);
cb.DisplayMember = displayMember;
cb.ValueMember = valueMember;
cb.DataSource = dt;
cb.SelectedIndex = -1;
}
catch (Exception ex)
{
MainClass.ShowMsg(ex.Message, "Error");
}
}
public static void ShowReportSaleReturn(CrystalReportViewer crv, string proc, string param = null, object val1 = null)
{
try
{
ReportDocument rd;
SqlConnection sql_con = new SqlConnection(MainClass.connection());
SqlCommand cmd = new SqlCommand(proc, sql_con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(param, val1);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dat = new DataTable();
adapter.Fill(dat);
rd = new ReportDocument();
rd.Load(Application.StartupPath + "\\Reports\\SalesReturnReport.rpt");
rd.SetDataSource(dat);
crv.ReportSource = rd;
crv.RefreshReport();
}
catch (Exception ex)
{
MainClass.ShowMsg(ex.Message, "Error");
}
}
public static int insert_update_delete(string proc, Hashtable ht)
{
int res = 0;
try
{
SqlConnection sql_con = new SqlConnection(MainClass.connection());
SqlCommand cmd = new SqlCommand(proc, sql_con);
cmd.CommandType = CommandType.StoredProcedure;
foreach (DictionaryEntry item in ht)
{
cmd.Parameters.AddWithValue(item.Key.ToString(), item.Value);
}
sql_con.Open();
//returns an integer which indicates how many rows have been affected
res = cmd.ExecuteNonQuery();
sql_con.Close();
}
catch (Exception ex)
{
MainClass.ShowMsg(ex.Message, "Error");
}
return(res);
}
public int insertPurchaseinvoicedetails(Int64 purchID, int prodID, int quan, float price)
{
SqlConnection sql_con = new SqlConnection(MainClass.connection());
try
{
SqlCommand cmd = new SqlCommand("st_insertPURCHASEINVOICEDETAILS", sql_con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@purchaseID", purchID);
cmd.Parameters.AddWithValue("@prodID", prodID);
cmd.Parameters.AddWithValue("@quant", quan);
cmd.Parameters.AddWithValue("@total", price);
sql_con.Open();
PIDcount = cmd.ExecuteNonQuery();
sql_con.Close();
}
catch (Exception ex)
{
MainClass.ShowMsg(ex.Message, "Error");
sql_con.Close();
}
return(PIDcount);
}
public static int getStockwrtProduct(int ProductID)
{
SqlConnection sql_con = new SqlConnection(MainClass.connection());
try
{
SqlCommand cmd = new SqlCommand("getProductStockwrtProdID", sql_con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@prodID", ProductID);
sql_con.Open();
ProductStockQuantity = Convert.ToInt32((cmd.ExecuteScalar()));
sql_con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error");
sql_con.Close();
}
return(ProductStockQuantity);
}
public static object getProductQuantityfromSTOCK(Int64 productID)
{
object stockcount = null;
var sql_con = new SqlConnection(MainClass.connection());
try
{
var cmd = new SqlCommand("st_getPRODUCTQUANTITYfromSTOCK", sql_con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@prodID", productID);
sql_con.Open();
stockcount = Convert.ToInt32(cmd.ExecuteScalar());
sql_con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error");
sql_con.Close();
}
return(stockcount);
}
public static object getProductPrice(int productID)
{
SqlConnection sql_con = new SqlConnection(MainClass.connection());
try
{
SqlCommand cmd = new SqlCommand("st_getPRODUCTPRICE", sql_con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@prodID", productID);
sql_con.Open();
ISproductEXISTPRICE = Convert.ToSingle((cmd.ExecuteScalar()));
sql_con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error");
sql_con.Close();
}
return(ISproductEXISTPRICE);
}
public static object getLASTID(string proc)
{
object o = null;
SqlConnection con = new SqlConnection(MainClass.connection());
try
{
SqlCommand cmd = new SqlCommand(proc, con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
o = cmd.ExecuteScalar();
con.Close();
}
catch (Exception ex)
{
MainClass.ShowMsg(ex.Message, "Error");
con.Close();
}
return(o);
}
private string[] loadProductswrtBarcode(string proc, Hashtable ht)
{
SqlConnection sql_con = new SqlConnection(MainClass.connection());
string[] productswrt = new string[6];
try
{
SqlCommand command = new SqlCommand(proc, sql_con);
command.CommandType = CommandType.StoredProcedure;
foreach (DictionaryEntry item in ht)
{
command.Parameters.AddWithValue(item.Key.ToString(), item.Value);
}
sql_con.Open();
SqlDataReader dr = command.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
productswrt[0] = dr[0].ToString(); //prod ID
productswrt[1] = dr[1].ToString(); //product
productswrt[2] = dr[2].ToString(); //Barcode
productswrt[3] = dr[3].ToString(); //Per Unit Price
productswrt[4] = dr[4].ToString(); //Per Unit Discount
productswrt[5] = dr[5].ToString(); //Total Amount
}
}
else
{
MainClass.ShowMsg("No products available....", "Error");
MainClass.disbale_reset(left_panel_sample2);
sql_con.Close();
}
sql_con.Close();
}
catch (Exception ex)
{
MainClass.ShowMsg(ex.Message, "Error");
MainClass.disbale_reset(left_panel_sample2);
sql_con.Close();
}
return(productswrt);
}
public static bool getlogindetails(string proc, Hashtable ht, bool admin)
{
bool check = false;
try
{
var sql_con = new SqlConnection(MainClass.connection());
var cmd = new SqlCommand(proc, sql_con);
cmd.CommandType = CommandType.StoredProcedure;
foreach (DictionaryEntry item in ht)
{
cmd.Parameters.AddWithValue(item.Key.ToString(), item.Value);
}
sql_con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
check = true;
while (dr.Read()) //reading data
{
if (admin == true) //fetch admin username & password
{
usernameOFadmin = dr["email"].ToString();
passwordOFadmin = dr["password"].ToString();
}
else
{
USERID = Convert.ToInt32(dr[0].ToString());
NAME = dr[1].ToString();
USERROLE = dr[2].ToString();
}
}
}
else
{
check = false;
MainClass.ShowMsg("Invalid username or password", "Error");
}
}
catch (Exception ex)
{
MainClass.ShowMsg(ex.Message, "Error");
check = false;
}
return(check);
}
public Int64 InsertPurchaseinvoice(DateTime date, int doneBy, int suppID)
{
SqlConnection sql_con = new SqlConnection(MainClass.connection());
try
{
using (TransactionScope sc = new TransactionScope())
{
var cmd = new SqlCommand("st_insertPURCHASEINVOICE", sql_con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@date", date);
cmd.Parameters.AddWithValue("@doneby", doneBy);
cmd.Parameters.AddWithValue("@suppID", suppID);
sql_con.Open();
cmd.ExecuteNonQuery();
sql_con.Close();
SqlCommand cmd1 = new SqlCommand("st_getLASTPURCHASEINVOICEID", sql_con);
cmd1.CommandType = CommandType.StoredProcedure;
sql_con.Open();
purchaseinvoiceIDSQLTASKS = Convert.ToInt64(cmd1.ExecuteScalar());
sql_con.Close();
sc.Complete();
}
}
catch (Exception ex)
{
MainClass.ShowMsg(ex.Message, "Error");
sql_con.Close();
}
return(purchaseinvoiceIDSQLTASKS);
}
public static void LoadListModifiedHashTable(string proc, ComboBox cb, string valueMember, string displayMember, Hashtable ht)
{
try
{
cb.DataSource = null;
cb.Items.Clear();
SqlConnection sql = new SqlConnection(MainClass.connection());
SqlCommand cmd = new SqlCommand(proc, sql);
cmd.CommandType = CommandType.StoredProcedure;
foreach (DictionaryEntry item in ht)
{
cmd.Parameters.AddWithValue(item.Key.ToString(), item.Value);
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
DataRow dr = dt.NewRow();
dr.ItemArray = new object[] { 0, "Select..." };
dt.Rows.InsertAt(dr, 0);
cb.DisplayMember = displayMember;
cb.ValueMember = valueMember;
cb.DataSource = dt;
cb.SelectedIndex = -1;
}
catch (Exception ex)
{
MainClass.ShowMsg(ex.Message, "Error");
}
}
public static double[] LoadLastBalance(string proc, Hashtable ht)
{
SqlConnection sql_con = new SqlConnection(MainClass.connection());
double[] amounts = new double[2];
try
{
SqlCommand cmd = new SqlCommand(proc, sql_con);
cmd.CommandType = CommandType.StoredProcedure;
foreach (DictionaryEntry item in ht)
{
cmd.Parameters.AddWithValue(item.Key.ToString(), item.Value);
}
sql_con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
amounts[0] = Convert.ToDouble(dr[0].ToString());
amounts[1] = Convert.ToDouble(dr[1].ToString());
}
}
sql_con.Close();
}
catch (Exception ex)
{
MainClass.ShowMsg(ex.Message, "Error");
sql_con.Close();
}
return(amounts);
}
public static double[] SaleInvoiceAmounts(string proc, Hashtable ht)
{
double[] arr = new double[3];
try
{
SqlConnection sql_con = new SqlConnection(MainClass.connection());
SqlCommand cmd = new SqlCommand(proc, sql_con);
cmd.CommandType = CommandType.StoredProcedure;
foreach (DictionaryEntry item in ht)
{
cmd.Parameters.AddWithValue(item.Key.ToString(), item.Value);
}
sql_con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
arr[0] = Convert.ToDouble(dr["Total Amount"].ToString());
arr[1] = Convert.ToDouble(dr["Amount Paid"].ToString());
arr[2] = Convert.ToDouble(dr["Remaining Amount"].ToString());
}
}
sql_con.Close();
}
catch (Exception ex)
{
MainClass.ShowMsg(ex.Message, "Error");
}
return(arr);
}
public static bool IfProductExist(long ProductID)
{
int x = 0;
SqlConnection sql_con = new SqlConnection(MainClass.connection());
try
{
SqlCommand cmd = new SqlCommand("st_ProductExist", sql_con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@prodID", ProductID);
sql_con.Open();
x = Convert.ToInt32(cmd.ExecuteScalar());
sql_con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error");
sql_con.Close();
}
if (x == 0)
{
return(false);
}
else
{
return(true);
}
}
public override void save_button_Click(object sender, EventArgs e)
{
if (purchase_dataGridView.Rows.Count == 0 || purchase_dataGridView.Rows.Count < 0)
{
MainClass.ShowMsg("Please enter all required information.", "error");
MainClass.disbale_reset(left_panel_sample2);
enable_crude_buttons();
}
else
{
MainClass.disbale_reset(left_panel_sample2); enable_crude_buttons();
try
{
using (TransactionScope sc = new TransactionScope())
{
int compare, compareTO;
here:
for (int i = 0; i < purchase_dataGridView.Rows.Count; ++i)
{
compare = Convert.ToInt32(purchase_dataGridView.Rows[i].Cells["productIDGV"].Value);
for (int j = i + 1; j < purchase_dataGridView.Rows.Count; ++j)
{
compareTO = Convert.ToInt32(purchase_dataGridView.Rows[j].Cells["productIDGV"].Value);
if (compare == compareTO)
{
if (correctIT == false)
{
DialogResult dr = MessageBox.Show("You added a duplicate product which is not allowed.\n\nDo you want to remove that duplicate product and add its quantity?", "Duplicate product(s) encountered!", MessageBoxButtons.YesNo, MessageBoxIcon.Error);
if (DialogResult.Yes == dr)
{
correctIT = true;
goto here;
}
else
{
correctIT = false;
throw new Exception("Duplicate products are not allowed!");
}
}
else
{
Int32 originalproductTOTAL, originalproductVALUE, dupliactedproductVALUE, dupliactedproductTOTAL;
originalproductVALUE = Convert.ToInt32(purchase_dataGridView.Rows[i].Cells["productquantityGV"].Value);
dupliactedproductVALUE = Convert.ToInt32(purchase_dataGridView.Rows[j].Cells["productquantityGV"].Value);
originalproductTOTAL = Convert.ToInt32(purchase_dataGridView.Rows[i].Cells["totalGV"].Value);
dupliactedproductTOTAL = Convert.ToInt32(purchase_dataGridView.Rows[j].Cells["totalGV"].Value);
originalproductVALUE += dupliactedproductVALUE;
originalproductTOTAL += dupliactedproductTOTAL;
purchase_dataGridView.Rows[i].Cells["productquantityGV"].Value = originalproductVALUE;
purchase_dataGridView.Rows[i].Cells["totalGV"].Value = originalproductTOTAL;
int beforeREMOVE = purchase_dataGridView.Rows.Count; int afterREMOVE;
purchase_dataGridView.Rows.Remove(purchase_dataGridView.Rows[j]);
afterREMOVE = purchase_dataGridView.Rows.Count;
if (afterREMOVE < beforeREMOVE)
{
MainClass.ShowMsg("Operation successfull.", "Success");
}
else
{
throw new Exception("An error occured during removing duplicated products!");
}
}
}
}
}
SQL_TASKS ss = new SQL_TASKS();
foreach (DataGridViewRow row in purchase_dataGridView.Rows)
{
//inserting purchase invoice
purchaseinvoiceID_FROM_SQL_TASKS = ss.InsertPurchaseinvoice(DateTime.Today, LoginCodeClass.USERID, Convert.ToInt32(row.Cells["supplierIDGV"].Value));
//inserting purchase invoice details
count += ss.insertPurchaseinvoicedetails(purchaseinvoiceID_FROM_SQL_TASKS, Convert.ToInt32(row.Cells["productIDGV"].Value.ToString()), Convert.ToInt32(row.Cells["productquantityGV"].Value.ToString()), Convert.ToSingle(row.Cells["totalGV"].Value.ToString()));
SqlConnection sql_con = new SqlConnection(MainClass.connection());
SqlCommand cmd = new SqlCommand();
cmd.Connection = sql_con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select pp_prodID from ProductPrice where pp_ProductBarcode='" + row.Cells["productbarcodeGV"].Value.ToString() + "' ";
sql_con.Open();
object check = cmd.ExecuteScalar();
sql_con.Close();
if (check == null) //product not present
{
Hashtable ht1 = new Hashtable();
ht1.Add("@prodID", row.Cells["productIDGV"].Value.ToString());
ht1.Add("@bp", Convert.ToSingle(row.Cells["totalGV"].Value.ToString()));
ht1.Add("@sp", DBNull.Value);
ht1.Add("@buyingdate", DateTime.Today);
ht1.Add("@profitper", DBNull.Value);
ht1.Add("@discount", DBNull.Value);
ht1.Add("@productbarcode", row.Cells["productbarcodeGV"].Value.ToString());
// inserting product price:
if (SQL_TASKS.insert_update_delete("st_insertPRODUCTPRICE", ht1) > 0)
{
success = true;
}
else
{
success = false;
}
}
else //product present
{
Hashtable ht2 = new Hashtable();
ht2.Add("@prodID", row.Cells["productIDGV"].Value.ToString());
cmd.CommandText = "select pp_buyingPrice from ProductPrice where pp_prodID='" + row.Cells["productIDGV"].Value.ToString() + "' ";
sql_con.Open();
Int64 ExistingProductBuyingPrice = Convert.ToInt64(cmd.ExecuteScalar());
sql_con.Close();
Int64 finalp = Convert.ToInt64(row.Cells["totalGV"].Value) + ExistingProductBuyingPrice;
Hashtable htx = new Hashtable();
htx.Add("@prodID", row.Cells["productIDGV"].Value.ToString());
htx.Add("@bp", finalp);
if (SQL_TASKS.insert_update_delete("st_updatePRODUCTPRICEforPURCHASEINVOICE", htx) > 0)
{
success = true;
}
else
{
success = false;
}
}
int q;
object stockcount;
bool DoesExist = SQL_TASKS.IfProductExist(Convert.ToInt64(row.Cells["productIDGV"].Value.ToString()));
if (DoesExist) //if product does exist
{
//update stock
stockcount = SQL_TASKS.getProductQuantityfromSTOCK(Convert.ToInt32(row.Cells["productIDGV"].Value.ToString()));
q = Convert.ToInt32(stockcount);
q += Convert.ToInt32(row.Cells["productquantityGV"].Value.ToString());
Hashtable hta = new Hashtable();
hta.Add("@prodID", Convert.ToInt32(row.Cells["productIDGV"].Value.ToString()));
hta.Add("@quan", q);
//updating stock:
if (SQL_TASKS.insert_update_delete("st_updateSTOCK", hta) > 0)
{
success = true;
}
}
else //if product does not exist
{
//insert
Hashtable htb = new Hashtable();
htb.Add("@prodID", Convert.ToInt32(row.Cells["productIDGV"].Value.ToString()));
htb.Add("@quan", Convert.ToInt32(row.Cells["productquantityGV"].Value.ToString()));
// inserting stock:
if (SQL_TASKS.insert_update_delete("st_insertSTOCK", htb) > 0)
{
success = true;
}
else
{
success = false;
}
}
}
if (count > 0 && success == true)
{
MainClass.ShowMsg("Purchase Invoice created successfully.", "Success");
MainClass.disbale_reset(left_panel_sample2);
enable_crude_buttons();
purchase_dataGridView.Rows.Clear(); gross_total_price_label.Text = "";
}
else
{
MainClass.ShowMsg("Unable to create Purchase Invoice", "Error");
MainClass.disbale_reset(left_panel_sample2);
enable_crude_buttons();
purchase_dataGridView.Rows.Clear(); gross_total_price_label.Text = "";
}
sc.Complete();
}
}
catch (Exception ex)
{
MainClass.ShowMsg(ex.Message, "Error");
MainClass.disbale_reset(left_panel_sample2);
enable_crude_buttons();
}
}
}