public DataTable getInvoiceDetails(BOPurchaseBillDetails objBOPurchaseBillDetails)
{
string strCmd = "";
try
{
//Initialization
objDB_Utility = new DB_Utility();
SqlConnection con = objDB_Utility.funcOpenConnection();
if (objBOPurchaseBillDetails.searchBy == "Invoice Number" && objBOPurchaseBillDetails.searchText != "")
{
strCmd = "SELECT sir.invoice_no, sm.supp_name, sir.total_mrp, sir.amount_ad, tr.debit_amount, sir.invoice_date FROM supplier_invoice_record sir, supplier_master sm, supplier_transaction_record tr ";
strCmd += "WHERE (sir.supp_id=sm.supp_id AND sir.transaction_id=tr.transaction_id) ";
strCmd += "AND sir.invoice_no LIKE '" + objBOPurchaseBillDetails.searchText + "%' ";
strCmd += "AND (sir.invoice_date BETWEEN '" + objBOPurchaseBillDetails.fromDate + "' AND '" + objBOPurchaseBillDetails.toDate + "')";
}
else if (objBOPurchaseBillDetails.searchBy == "Supplier Name" && objBOPurchaseBillDetails.searchText != "")
{
strCmd = "SELECT sir.invoice_no, sm.supp_name, sir.total_mrp, sir.amount_ad, tr.debit_amount, sir.invoice_date ";
strCmd += "FROM supplier_invoice_record sir, supplier_transaction_record tr, supplier_master sm ";
strCmd += "WHERE sir.supp_id = sm.supp_id AND sm.supp_id IN (SELECT supp_id FROM supplier_master WHERE supp_name LIKE '" + objBOPurchaseBillDetails.searchText + "%') ";
strCmd += "AND sir.transaction_id=tr.transaction_id AND (sir.invoice_date between '" + objBOPurchaseBillDetails.fromDate + "' AND '" + objBOPurchaseBillDetails.toDate + "')";
}
else if (objBOPurchaseBillDetails.searchText == "")
{
strCmd = "SELECT sir.invoice_no, sm.supp_name, sir.total_mrp, sir.amount_ad, tr.debit_amount, sir.invoice_date ";
strCmd += "FROM supplier_invoice_record sir, supplier_transaction_record tr, supplier_master sm ";
strCmd += "WHERE sir.supp_id=sm.supp_id AND sir.transaction_id=tr.transaction_id ";
strCmd += "AND (sir.invoice_date BETWEEN '" + objBOPurchaseBillDetails.fromDate + "' AND '" + objBOPurchaseBillDetails.toDate + "') ";
}
cmd = new SqlCommand(strCmd, con);
da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds, "Purchase_Invoice_Details");
return(ds.Tables[0]);
}
catch (Exception ex)
{
string filePath = @"..\ErrorLog.log";
using (System.IO.StreamWriter writer = new StreamWriter(filePath, true))
{
writer.WriteLine("Error Code : 00014" + Environment.NewLine + "Message :" + ex.Message + "<br/>" + Environment.NewLine + "StackTrace :" + ex.StackTrace +
"" + Environment.NewLine + "Date :" + DateTime.Now.ToString());
writer.WriteLine(Environment.NewLine + "-----------------------------------------------------------------------------" + Environment.NewLine);
writer.WriteLine(strCmd);
}
//Send datatable with error code
DataTable dt = new DataTable();
DataColumn dc1 = new DataColumn("error");
dt.Columns.Add(dc1);
dt.Rows.Add("00014");
return(dt);
}
finally
{
objDB_Utility.funcCloseConnection();
}
}
public string getInvoiceID()
{
int invoiceID_suffix;
string invoice_no = "RTL";
try
{
//Initialization
objDB_Utility = new DB_Utility();
SqlConnection con = objDB_Utility.funcOpenConnection();
SqlCommand cmd = new SqlCommand("SELECT COUNT(invoice_no)+1 FROM retail_invoice_record", con);
invoiceID_suffix = Convert.ToInt32(cmd.ExecuteScalar());
invoice_no += invoiceID_suffix.ToString();
return(invoice_no);
}
catch (Exception ex)
{
string filePath = @"..\ErrorLog.log";
using (System.IO.StreamWriter writer = new StreamWriter(filePath, true))
{
writer.WriteLine("Error Code : 00020" + Environment.NewLine + "Message :" + ex.Message + "<br/>" + Environment.NewLine + "StackTrace :" + ex.StackTrace +
"" + Environment.NewLine + "Date :" + DateTime.Now.ToString());
writer.WriteLine(Environment.NewLine + "-----------------------------------------------------------------------------" + Environment.NewLine);
}
return("error");
}
finally
{
objDB_Utility.funcCloseConnection();
}
}
public string funcCheckProductPacking(BOPurchaseBill objBOPurchaseBill)
{
DataTable dt = new DataTable();
try
{
//Initialization
objDB_Utility = new DB_Utility();
SqlConnection con = objDB_Utility.funcOpenConnection();
string cmdText = "SELECT packing FROM product_master WHERE product_id = ";
cmdText += objBOPurchaseBill.product_id;
SqlCommand cmd = new SqlCommand(cmdText, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
return(Convert.ToString(dt.Rows[0]["packing"]));
}
catch (Exception ex)
{
string filePath = @"..\ErrorLog.log";
using (System.IO.StreamWriter writer = new StreamWriter(filePath, true))
{
writer.WriteLine("Error Code : 00011" + Environment.NewLine + "Message :" + ex.Message + "<br/>" + Environment.NewLine + "StackTrace :" + ex.StackTrace +
"" + Environment.NewLine + "Date :" + DateTime.Now.ToString());
writer.WriteLine(Environment.NewLine + "-----------------------------------------------------------------------------" + Environment.NewLine);
}
return(null);
}
finally
{
objDB_Utility.funcCloseConnection();
}
}
public bool funcUpdateProdPacking(string id, string packing)
{
try
{
//Initialization
objDB_Utility = new DB_Utility();
SqlConnection con = objDB_Utility.funcOpenConnection();
string commandText = "Update product_master SET packing='" + packing + "' WHERE product_id = " + id + "";
SqlCommand cmd = new SqlCommand(commandText, con);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
string filePath = @"..\ErrorLog.log";
using (System.IO.StreamWriter writer = new StreamWriter(filePath, true))
{
writer.WriteLine("Error Code : 00012" + Environment.NewLine + "Message :" + ex.Message + "<br/>" + Environment.NewLine + "StackTrace :" + ex.StackTrace +
"" + Environment.NewLine + "Date :" + DateTime.Now.ToString());
writer.WriteLine(Environment.NewLine + "-----------------------------------------------------------------------------" + Environment.NewLine);
}
return(false);
}
finally
{
objDB_Utility.funcCloseConnection();
}
}
public DataTable getInvoiceDetails(BORetailBillDetails objBORetailBillDetails)
{
string strCmd = "";
try
{
//Initialization
objDB_Utility = new DB_Utility();
SqlConnection con = objDB_Utility.funcOpenConnection();
strCmd = "SELECT ric.invoice_no, ric.patient_name, ric.address, ric.contact_no, ric.doctor, ric.total_mrp, ric.discount,ric.net_amount ";
strCmd += "FROM retail_invoice_record ric ";
strCmd += "WHERE ric.invoice_date BETWEEN '" + objBORetailBillDetails.fromDate + "' AND '" + objBORetailBillDetails.toDate + "' ";
if (objBORetailBillDetails.searchBy == "Invoice Number" && objBORetailBillDetails.searchText != "")
{
strCmd += "AND ric.invoice_no like '" + objBORetailBillDetails.searchText + "%' ";
}
else if (objBORetailBillDetails.searchBy == "Patient Name" && objBORetailBillDetails.searchText != "")
{
strCmd += "AND ric.patient_name like '" + objBORetailBillDetails.searchText + "%' ";
}
else if (objBORetailBillDetails.searchBy == "Contact Number" && objBORetailBillDetails.searchText != "")
{
strCmd += "AND ric.contact_no like '" + objBORetailBillDetails.searchText + "%' ";
}
else if (objBORetailBillDetails.searchBy == "Doctor" && objBORetailBillDetails.searchText != "")
{
strCmd += "AND ric.doctor like '" + objBORetailBillDetails.searchText + "%' ";
}
cmd = new SqlCommand(strCmd, con);
da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds, "Retail_Invoice_Details");
return(ds.Tables[0]);
}
catch (Exception ex)
{
string filePath = @"..\ErrorLog.log";
using (System.IO.StreamWriter writer = new StreamWriter(filePath, true))
{
writer.WriteLine("Error Code : 00024" + Environment.NewLine + "Message :" + ex.Message + "<br/>" + Environment.NewLine + "StackTrace :" + ex.StackTrace +
"" + Environment.NewLine + "Date :" + DateTime.Now.ToString());
writer.WriteLine(Environment.NewLine + "-----------------------------------------------------------------------------" + Environment.NewLine);
writer.WriteLine(strCmd);
}
//Send datatable with error code
DataTable dt = new DataTable();
DataColumn dc1 = new DataColumn("error");
dt.Columns.Add(dc1);
dt.Rows.Add("00024");
return(dt);
}
finally
{
objDB_Utility.funcCloseConnection();
}
}
public int funcCheckDuplicateSup(BOSuppDetails objSupDetails)
{
objDB_Utility = new DB_Utility();
SqlConnection con = objDB_Utility.funcOpenConnection();
SqlCommand cmd = new SqlCommand("SELECT count(*) FROM supplier_master WHERE UPPER(supp_name)=UPPER(@supp_name)", con);
cmd.Parameters.AddWithValue("@supp_name", objSupDetails.suppName);
int retVal = Convert.ToInt32(cmd.ExecuteScalar());
return(retVal);
}
public DataTable getChallanDetails(BOChallanDetails objBOChallanDetails)
{
string strCmd = "";
try
{
//Initialization
objDB_Utility = new DB_Utility();
SqlConnection con = objDB_Utility.funcOpenConnection();
strCmd = "SELECT cr.challan_no, cm.cust_name, cr.challan_amount, cr.converted_to_bill_flag, cr.challan_date ";
strCmd += "FROM challan_record cr, customer_master cm ";
strCmd += "WHERE cr.challan_date BETWEEN '" + objBOChallanDetails.fromDate + "' AND '" + objBOChallanDetails.toDate + "' ";
strCmd += "AND cr.cust_id = cm.cust_id ";
if (objBOChallanDetails.searchBy == "Challan No." && objBOChallanDetails.searchText != "")
{
strCmd += "AND cr.challan_no like '" + objBOChallanDetails.searchText + "%' ";
}
else if (objBOChallanDetails.searchBy == "Customer Name" && objBOChallanDetails.searchText != "")
{
strCmd += "AND cr.cust_id IN (SELECT cust_id FROM customer_master WHERE cust_name like '" + objBOChallanDetails.searchText + "%') ";
}
cmd = new SqlCommand(strCmd, con);
da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds, "challan_details");
return(ds.Tables[0]);
}
catch (Exception ex)
{
string filePath = @"..\ErrorLog.log";
using (System.IO.StreamWriter writer = new System.IO.StreamWriter(filePath, true))
{
writer.WriteLine("Error Code : 00030" + Environment.NewLine + "Message :" + ex.Message + "<br/>" + Environment.NewLine + "StackTrace :" + ex.StackTrace +
"" + Environment.NewLine + "Date :" + DateTime.Now.ToString());
writer.WriteLine(Environment.NewLine + "-----------------------------------------------------------------------------" + Environment.NewLine);
writer.WriteLine(strCmd);
}
//Send datatable with error code
DataTable dt = new DataTable();
DataColumn dc1 = new DataColumn("error");
dt.Columns.Add(dc1);
dt.Rows.Add("00030");
return(dt);
}
finally
{
objDB_Utility.funcCloseConnection();
}
}
public DataTable getInvoiceProductDetails(string invoiceNumber)
{
string strCmd = "";
try
{
//Initialization
objDB_Utility = new DB_Utility();
SqlConnection con = objDB_Utility.funcOpenConnection();
strCmd = "SELECT supinv.batch_no, prm.product_name, supinv.packing, supinv.whole_quant, supinv.loose_quant, supinv.mrp_ps, supinv.discount, ";
strCmd += "supinv.cost_per_unit, supinv.total_cost ";
strCmd += "FROM supplier_invoice_details supinv,InStock ins,product_master prm ";
strCmd += "WHERE supinv.invoice_no = '" + invoiceNumber + "' AND supinv.batch_no=ins.batch_no ";
strCmd += "AND prm.product_id = ins.product_id ";
strCmd += "AND prm.product_id = (SELECT product_id FROM InStock WHERE batch_no = supinv.batch_no)";
cmd = new SqlCommand(strCmd, con);
da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds, "Invoice_Product_Details");
return(ds.Tables[0]);
}
catch (Exception ex)
{
string filePath = @"..\ErrorLog.log";
using (System.IO.StreamWriter writer = new StreamWriter(filePath, true))
{
writer.WriteLine("Error Code : 00015" + Environment.NewLine + "Message :" + ex.Message + "<br/>" + Environment.NewLine + "StackTrace :" + ex.StackTrace +
"" + Environment.NewLine + "Date :" + DateTime.Now.ToString());
writer.WriteLine(Environment.NewLine + "-----------------------------------------------------------------------------" + Environment.NewLine);
writer.WriteLine(strCmd);
}
//Send datatable with error code
DataTable dt = new DataTable();
DataColumn dc1 = new DataColumn("error");
dt.Columns.Add(dc1);
dt.Rows.Add("00015");
return(dt);
}
finally
{
objDB_Utility.funcCloseConnection();
}
}
public DataTable getChallanProductDetails(string challanNo)
{
string strCmd = "";
try
{
//Initialization
objDB_Utility = new DB_Utility();
SqlConnection con = objDB_Utility.funcOpenConnection();
strCmd = "SELECT ins.batch_no, pm.product_name, ins.packing, cd.whole_quant, cd.loose_quant, cd.mrp_per_strip, cd.total_amount ";
strCmd += "FROM challan_details cd, product_master pm, InStock ins ";
strCmd += "WHERE cd.challan_no='" + challanNo + "' AND cd.batch_no=ins.batch_no ";
strCmd += "AND pm.product_id = ins.product_id ";
strCmd += "AND pm.product_id = (SELECT product_id FROM InStock WHERE batch_no=cd.batch_no) ";
cmd = new SqlCommand(strCmd, con);
da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds, "Challan_Product_Details");
return(ds.Tables[0]);
}
catch (Exception ex)
{
string filePath = @"..\ErrorLog.log";
using (System.IO.StreamWriter writer = new System.IO.StreamWriter(filePath, true))
{
writer.WriteLine("Error Code : 00031" + Environment.NewLine + "Message :" + ex.Message + "<br/>" + Environment.NewLine + "StackTrace :" + ex.StackTrace +
"" + Environment.NewLine + "Date :" + DateTime.Now.ToString());
writer.WriteLine(Environment.NewLine + "-----------------------------------------------------------------------------" + Environment.NewLine);
writer.WriteLine(strCmd);
}
//Send datatable with error code
DataTable dt = new DataTable();
DataColumn dc1 = new DataColumn("error");
dt.Columns.Add(dc1);
dt.Rows.Add("00031");
return(dt);
}
finally
{
objDB_Utility.funcCloseConnection();
}
}
public int funcCheckDuplicateCust(BOBuyerDetails objBOBuyerDetails)
{
objDB_Utility = new DB_Utility();
try
{
SqlConnection con = objDB_Utility.funcOpenConnection();
SqlCommand cmd = new SqlCommand("SELECT count(*) FROM customer_master WHERE UPPER(cust_name)=UPPER(@cust_name)", con);
cmd.Parameters.AddWithValue("@cust_name", objBOBuyerDetails.buyerName);
int retVal = Convert.ToInt32(cmd.ExecuteScalar());
return(retVal);
}
catch (Exception ex)
{
return(-1);
}
finally
{
objDB_Utility.funcCloseConnection();
}
}
public DataTable funcPopulateProductGrid(string condition)
{
try
{
//Initialization
objDB_Utility = new DB_Utility();
SqlConnection con = objDB_Utility.funcOpenConnection();
string strCmd = "SELECT product_id as \"Product ID\", product_name as \"Product Name\", company as \"Brand/Company\", ";
strCmd += "contents as \"Contents\", Generic as \"Generic Name\", packing as \"Packing\", ";
strCmd += "unit as \"Unit\", schedule as \"Schedule\", ";
strCmd += "modified_date_time as \"Date\" ";
strCmd += "FROM product_master " + condition;
cmd = new SqlCommand(strCmd, con);
da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds, "Product_Details");
return(ds.Tables[0]);
}
catch (Exception ex)
{
string filePath = @"..\ErrorLog.log";
using (System.IO.StreamWriter writer = new StreamWriter(filePath, true))
{
writer.WriteLine("Error Code : 00005" + Environment.NewLine + "Message :" + ex.Message + "<br/>" + Environment.NewLine + "StackTrace :" + ex.StackTrace +
"" + Environment.NewLine + "Date :" + DateTime.Now.ToString());
writer.WriteLine(Environment.NewLine + "-----------------------------------------------------------------------------" + Environment.NewLine);
}
//Send datatable with error code
DataTable dt = new DataTable();
DataColumn dc1 = new DataColumn("error");
dt.Columns.Add(dc1);
dt.Rows.Add("00005");
return(dt);
}
finally
{
objDB_Utility.funcCloseConnection();
}
}
public DataTable funcPopulateCustGrid(string condition)
{
try
{
//Initialization
objDB_Utility = new DB_Utility();
SqlConnection con = objDB_Utility.funcOpenConnection();
string strCmd = "SELECT cust_id as \"Customer ID\", cust_name as \"Customer Name\", DL_no as \"DL Number\", ";
strCmd += "TIN_no as \"TIN Number\", address_line1 as \"Address Line1\", address_line2 as \"Address Line2\", ";
strCmd += "contact_no as \"Contact\", email as \"Email ID\", ";
strCmd += "modified_date_time as \"Date\" ";
strCmd += "FROM customer_master " + condition;
cmd = new SqlCommand(strCmd, con);
da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds, "Customer_Details");
return(ds.Tables[0]);
}
catch (Exception ex)
{
string filePath = @"..\ErrorLog.log";
using (System.IO.StreamWriter writer = new StreamWriter(filePath, true))
{
writer.WriteLine("Error Code : 00017" + Environment.NewLine + "Message :" + ex.Message + "<br/>" + Environment.NewLine + "StackTrace :" + ex.StackTrace +
"" + Environment.NewLine + "Date :" + DateTime.Now.ToString());
writer.WriteLine(Environment.NewLine + "-----------------------------------------------------------------------------" + Environment.NewLine);
}
//Send datatable with error code
DataTable dt = new DataTable();
DataColumn dc1 = new DataColumn("error");
dt.Columns.Add(dc1);
dt.Rows.Add("00017");
return(dt);
}
finally
{
objDB_Utility.funcCloseConnection();
}
}
public string funcSaveInvoiceDetails(BOPurchaseBill objBOPurchaseBill, DataTable dtSupplierInvoiceDetails)
{
try
{
//Initialization
objDB_Utility = new DB_Utility();
SqlConnection con = objDB_Utility.funcOpenConnection();
SqlCommand cmd = new SqlCommand("sp_saveSupInvoiceDetails", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//Code to Insert Supplier Details Data into Database using stored procedure
cmd.Parameters.AddWithValue("@invoice_no", objBOPurchaseBill.invoice_number);
cmd.Parameters.AddWithValue("@supp_id", objBOPurchaseBill.supplier_id);
cmd.Parameters.AddWithValue("@total_mrp", objBOPurchaseBill.totalMRP);
cmd.Parameters.AddWithValue("@discount", objBOPurchaseBill.discount);
cmd.Parameters.AddWithValue("@amount_ad", objBOPurchaseBill.totalCost);
cmd.Parameters.AddWithValue("@amount_paid", objBOPurchaseBill.amount_paid);
cmd.Parameters.AddWithValue("@payment_method", objBOPurchaseBill.payment_method);
cmd.Parameters.AddWithValue("@invoice_date", objBOPurchaseBill.invoice_date);
SqlParameter tvparam = cmd.Parameters.AddWithValue("@dt_supplier_invoice", dtSupplierInvoiceDetails);
//tvparam.SqlDbType = SqlDbType.Structured;
cmd.ExecuteNonQuery();
return("success");
}
catch (Exception ex)
{
string filePath = @"..\ErrorLog.log";
using (System.IO.StreamWriter writer = new StreamWriter(filePath, true))
{
writer.WriteLine("Error Code : 00013" + Environment.NewLine + "Message :" + ex.Message + "<br/>" + Environment.NewLine + "StackTrace :" + ex.StackTrace +
"" + Environment.NewLine + "Date :" + DateTime.Now.ToString());
writer.WriteLine(Environment.NewLine + "-----------------------------------------------------------------------------" + Environment.NewLine);
}
return(ex.Message);
}
finally
{
objDB_Utility.funcCloseConnection();
}
}
public bool funcInsertSupDetails(BOSuppDetails objBOSupDetails)
{
try
{
//Initialization
objDB_Utility = new DB_Utility();
SqlConnection con = objDB_Utility.funcOpenConnection();
SqlCommand cmd = new SqlCommand("sp_insertSupDetails", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//Code to Insert Supplier Details Data into Database using stored procedure
cmd.Parameters.AddWithValue("@sup_id", 0);
cmd.Parameters.AddWithValue("@sup_name", objBOSupDetails.suppName);
cmd.Parameters.AddWithValue("@dl_no", objBOSupDetails.dlNo);
cmd.Parameters.AddWithValue("@tin_no", objBOSupDetails.tinNo);
cmd.Parameters.AddWithValue("@addr1", objBOSupDetails.addr1);
cmd.Parameters.AddWithValue("@addr2", objBOSupDetails.addr2);
cmd.Parameters.AddWithValue("@contact_no", objBOSupDetails.contactNo);
cmd.Parameters.AddWithValue("@email_id", objBOSupDetails.emailId);
cmd.Parameters.AddWithValue("@modified_date_time", System.DateTime.Today);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
string filePath = @"..\ErrorLog.log";
using (System.IO.StreamWriter writer = new StreamWriter(filePath, true))
{
writer.WriteLine("Error Code : 00001" + Environment.NewLine + "Message :" + ex.Message + "<br/>" + Environment.NewLine + "StackTrace :" + ex.StackTrace +
"" + Environment.NewLine + "Date :" + DateTime.Now.ToString());
writer.WriteLine(Environment.NewLine + "-----------------------------------------------------------------------------" + Environment.NewLine);
}
return(false);
}
finally
{
objDB_Utility.funcCloseConnection();
}
}
public DataTable funcGetBatchDetails(string batchNo, string productID)
{
DataTable dt = new DataTable();
try
{
//Initialization
objDB_Utility = new DB_Utility();
SqlConnection con = objDB_Utility.funcOpenConnection();
string cmdStr = "SELECT inst.batch_no,pmas.product_name,CONCAT('1*',inst.packing), FORMAT(inst.expiry,'MM/yyyy'), pmas.schedule,inst.mrp, inst.whole_quant, inst.loose_quant ";
cmdStr += "FROM InStock inst, product_master pmas ";
cmdStr += "WHERE inst.product_id = pmas.product_id AND inst.batch_no = '" + batchNo + "' ";
cmdStr += "AND inst.product_id = '" + productID + "' ";
SqlCommand cmd = new SqlCommand(cmdStr, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
return(dt);
}
catch (Exception ex)
{
string filePath = @"..\ErrorLog.log";
using (System.IO.StreamWriter writer = new StreamWriter(filePath, true))
{
writer.WriteLine("Error Code : 00028" + Environment.NewLine + "Message :" + ex.Message + "<br/>" + Environment.NewLine + "StackTrace :" + ex.StackTrace +
"" + Environment.NewLine + "Date :" + DateTime.Now.ToString());
writer.WriteLine(Environment.NewLine + "-----------------------------------------------------------------------------" + Environment.NewLine);
}
//Send datatable with error code
DataColumn dc1 = new DataColumn("error");
dt.Columns.Add(dc1);
dt.Rows.Add("00028");
return(dt);
}
finally
{
objDB_Utility.funcCloseConnection();
}
}
public DataTable populateProductDetails(string productID)
{
DataTable dt = new DataTable();
try
{
//Initialization
objDB_Utility = new DB_Utility();
SqlConnection con = objDB_Utility.funcOpenConnection();
string cmdStr = "SELECT batch_no,FORMAT(expiry,'MM/yyyy'), whole_quant, ";
cmdStr += "loose_quant, CONCAT('1*',packing), mrp, rack_no FROM InStock ";
cmdStr += "WHERE product_id = '" + productID + "'";
SqlCommand cmd = new SqlCommand(cmdStr, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
return(dt);
}
catch (Exception ex)
{
string filePath = @"..\ErrorLog.log";
using (System.IO.StreamWriter writer = new System.IO.StreamWriter(filePath, true))
{
writer.WriteLine("Error Code : 00021" + Environment.NewLine + "Message :" + ex.Message + "<br/>" + Environment.NewLine + "StackTrace :" + ex.StackTrace +
"" + Environment.NewLine + "Date :" + DateTime.Now.ToString());
writer.WriteLine(Environment.NewLine + "-----------------------------------------------------------------------------" + Environment.NewLine);
}
//Send datatable with error code
DataColumn dc1 = new DataColumn("error");
dt.Columns.Add(dc1);
dt.Rows.Add("00021");
return(dt);
}
finally
{
objDB_Utility.funcCloseConnection();
}
}
public bool funcInsertProdDetails(BOProductDetails objBOProdDetails)
{
try
{
//Initialization
objDB_Utility = new DB_Utility();
SqlConnection con = objDB_Utility.funcOpenConnection();
SqlCommand cmd = new SqlCommand("sp_insertProductMaster", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//Code to Insert Supplier Details Data into Database using stored procedure
cmd.Parameters.AddWithValue("@product_id", 0);
cmd.Parameters.AddWithValue("@product_name", objBOProdDetails.medName);
cmd.Parameters.AddWithValue("@company", objBOProdDetails.company);
cmd.Parameters.AddWithValue("@contents", objBOProdDetails.contents);
cmd.Parameters.AddWithValue("@generic", objBOProdDetails.genericName);
cmd.Parameters.AddWithValue("@packing", objBOProdDetails.packing);
cmd.Parameters.AddWithValue("@unit", objBOProdDetails.unit);
cmd.Parameters.AddWithValue("@schedule", objBOProdDetails.schedule);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
string filePath = @"..\ErrorLog.log";
using (System.IO.StreamWriter writer = new StreamWriter(filePath, true))
{
writer.WriteLine("Error Code : 00003" + Environment.NewLine + "Message :" + ex.Message + "<br/>" + Environment.NewLine + "StackTrace :" + ex.StackTrace +
"" + Environment.NewLine + "Date :" + DateTime.Now.ToString());
writer.WriteLine(Environment.NewLine + "-----------------------------------------------------------------------------" + Environment.NewLine);
}
return(false);
}
finally
{
objDB_Utility.funcCloseConnection();
}
}
public int funcAuthenticate(BOlogin objBOlogin)
{
try
{
objDB_Util = new DB_Utility();
SqlConnection con = objDB_Util.funcOpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT count(*) FROM tblLogin WHERE username = '******' and password = '******'";
int retVal = (int)cmd.ExecuteScalar();
return(retVal);
}
catch
{
//exception handling for login
return(-1);
}
finally
{
objDB_Util.funcCloseConnection();
}
}
public string saveChallanDetails(BOChallan objBOChallan, DataTable productTable)
{
try
{
//Initialization
objDB_Utility = new DB_Utility();
SqlConnection con = objDB_Utility.funcOpenConnection();
SqlCommand cmd = new SqlCommand("sp_saveChallanDetails", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//Code to Insert Supplier Details Data into Database using stored procedure
cmd.Parameters.AddWithValue("@challan_no", objBOChallan.challanNumber);
cmd.Parameters.AddWithValue("@customer_id", objBOChallan.customerID);
cmd.Parameters.AddWithValue("@challan_amount", objBOChallan.challanAmount);
cmd.Parameters.AddWithValue("@payment_method", "cash");
SqlParameter tvparam = cmd.Parameters.AddWithValue("@dt_product_list", productTable);
//tvparam.SqlDbType = SqlDbType.Structured;
cmd.ExecuteNonQuery();
return("success");
}
catch (Exception ex)
{
string filePath = @"..\ErrorLog.log";
using (System.IO.StreamWriter writer = new StreamWriter(filePath, true))
{
writer.WriteLine("Error Code : 00029" + Environment.NewLine + "Message :" + ex.Message + "<br/>" + Environment.NewLine + "StackTrace :" + ex.StackTrace +
"" + Environment.NewLine + "Date :" + DateTime.Now.ToString());
writer.WriteLine(Environment.NewLine + "-----------------------------------------------------------------------------" + Environment.NewLine);
}
return(ex.Message);
}
finally
{
objDB_Utility.funcCloseConnection();
}
}
public bool funcDeleteCustomerRecord(string custID)
{
objDB_Utility = new DB_Utility();
try
{
//Set a flag inactive for that customer record && also populate only active customers
/*
* SqlConnection con = objDB_Utility.funcOpenConnection();
* SqlCommand cmd = new SqlCommand("DELETE FROM cust_amount_receivable WHERE cust_id=@cust_id", con);
* cmd.Parameters.AddWithValue("@cust_id", custID);
* cmd.ExecuteNonQuery();
*/
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
objDB_Utility.funcCloseConnection();
}
}
public DataTable funcPopulateCustomerList()
{
DataTable dt = new DataTable();
try
{
//Initialization
objDB_Utility = new DB_Utility();
SqlConnection con = objDB_Utility.funcOpenConnection();
SqlCommand cmd = new SqlCommand("SELECT cust_id, cust_name FROM customer_master", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
return(dt);
}
catch (Exception ex)
{
string filePath = @"..\ErrorLog.log";
using (System.IO.StreamWriter writer = new StreamWriter(filePath, true))
{
writer.WriteLine("Error Code : 00027" + Environment.NewLine + "Message :" + ex.Message + "<br/>" + Environment.NewLine + "StackTrace :" + ex.StackTrace +
"" + Environment.NewLine + "Date :" + DateTime.Now.ToString());
writer.WriteLine(Environment.NewLine + "-----------------------------------------------------------------------------" + Environment.NewLine);
}
//Send datatable with error code
DataColumn dc1 = new DataColumn("error");
dt.Columns.Add(dc1);
dt.Rows.Add("00027");
return(dt);
}
finally
{
objDB_Utility.funcCloseConnection();
}
}
public DataTable funcPopulateProductList()
{
objDB_Utility = new DB_Utility();
return(objDB_Utility.funcPopulateProductList());
}
