public DataSet GetDayClosure() { DataSet dsDayClosure = new DataSet(); try { using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = SQLCon.Sqlconn(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "[POS_USP_R_DAYCLOSURE]"; using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { da.Fill(dsDayClosure); } if (dsDayClosure != null && dsDayClosure.Tables.Count > 0) { if (dsDayClosure.Tables[0].Rows.Count == 1) { throw new Exception(Convert.ToString(dsDayClosure.Tables[0].Rows[0][0])); } dsDayClosure.Tables[0].TableName = "DENOMINATION"; dsDayClosure.Tables[1].TableName = "MOP"; } } } catch (Exception ex) { if (ex.Message.Contains("No Bills Available!")) { throw ex; } else { throw new Exception("Error While getting Day Closure", ex); } } finally { SQLCon.Sqlconn().Close(); } return(dsDayClosure); }
public DataSet FinishBill(object userID, int daySequenceID, Bill billObj) { DataSet dsNextBill = new DataSet(); try { using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = SQLCon.Sqlconn(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "[POS_USP_FINISH_BILL]"; cmd.Parameters.AddWithValue("@UserID", userID); cmd.Parameters.AddWithValue("@BillID", billObj.BillID); cmd.Parameters.AddWithValue("@DaySequenceID", daySequenceID); cmd.Parameters.AddWithValue("@CustomerName", billObj.CustomerName); cmd.Parameters.AddWithValue("@CustomerNumber", billObj.CustomerNumber); cmd.Parameters.AddWithValue("@Rounding", billObj.Rounding); DataTable dtTemp = billObj.dtMopValues.Copy(); if (dtTemp.Columns.Contains("MOPNAME")) { dtTemp.Columns.Remove("MOPNAME"); } cmd.Parameters.AddWithValue("@MopValues", dtTemp); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { da.Fill(dsNextBill); } dsNextBill.Tables[0].TableName = "BILL"; dsNextBill.Tables[1].TableName = "BILLDETAILS"; } } catch (Exception ex) { throw new Exception("Error While finishing and getting next bill", ex); } finally { SQLCon.Sqlconn().Close(); } return(dsNextBill); }
public int SaveDayClosure(object BranchCounterID, DataTable dtDenomination , DataTable dtMOP, object UserID, object RefundAmount, int DaySequenceID) { int DayClosureID = 0; try { using (SqlCommand cmd = new SqlCommand()) { dtDenomination.Columns.Remove("DISPLAYVALUE"); dtDenomination.Columns.Remove("MULTIPLIER"); dtDenomination.Columns.Remove("QUANTITY"); dtMOP.Columns.Remove("MOPNAME"); cmd.Connection = SQLCon.Sqlconn(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "[POS_USP_CU_DAYCLOSURE]"; cmd.Parameters.AddWithValue("@BRANCHCOUNTERID", BranchCounterID); cmd.Parameters.AddWithValue("@dtDenomination", dtDenomination); cmd.Parameters.AddWithValue("@dtMOP", dtMOP); cmd.Parameters.AddWithValue("@USERID", UserID); cmd.Parameters.AddWithValue("@RefundAmount", RefundAmount); cmd.Parameters.AddWithValue("@DaySequenceID", DaySequenceID); object objReturn = cmd.ExecuteScalar(); if (!int.TryParse(Convert.ToString(objReturn), out DayClosureID)) { throw new Exception("Error while saving day cllosure"); } } } catch (Exception ex) { throw new Exception("Error While saving Day Closure", ex); } finally { SQLCon.Sqlconn().Close(); } return(DayClosureID); }
public void InsertCRefund(DataTable dtRefund, object UserID) { try { using (SqlCommand cmd = new SqlCommand()) { DataTable dtCloned = dtRefund.Copy(); foreach (DataColumn dc in dtRefund.Columns) { if (dc.ColumnName == "BILLDETAILID" || dc.ColumnName == "REFUNDQUANTITY" || dc.ColumnName == "REFUNDWEIGHTINKGS" || dc.ColumnName == "REFUNDAMOUNT") { continue; } dtCloned.Columns.Remove(dc.ColumnName); } cmd.Connection = SQLCon.Sqlconn(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "[POS_USP_CU_CREFUND]"; cmd.Parameters.AddWithValue("@dtRefund", dtCloned); cmd.Parameters.AddWithValue("@UserID", UserID); int ivalue = cmd.ExecuteNonQuery(); if (ivalue <= 0) { throw new Exception("Error while saving Refund Details!"); } } } catch (Exception ex) { throw new Exception("Error While saving Refund Details", ex); } finally { SQLCon.Sqlconn().Close(); } }
public DataTable SaveBillDetail(object billID, object ItemPriceID, object quantity, object weightInKGS, object userID, object billDetailID) { DataTable dtBillDetails = new DataTable(); SqlTransaction transaction = null; try { using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = SQLCon.Sqlconn(); transaction = SQLCon.Sqlconn().BeginTransaction(); cmd.Transaction = transaction; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "[POS_USP_CU_BILLDETAIL]"; cmd.Parameters.AddWithValue("@BillID", billID); cmd.Parameters.AddWithValue("@ItemPriceID", ItemPriceID); cmd.Parameters.AddWithValue("@Quantity", quantity); cmd.Parameters.AddWithValue("@WeightInKgs", weightInKGS); cmd.Parameters.AddWithValue("@UserID", userID); cmd.Parameters.AddWithValue("@BillDetailID", billDetailID); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { da.Fill(dtBillDetails); transaction.Commit(); } } } catch (Exception ex) { transaction?.Rollback(); throw ex; } finally { SQLCon.Sqlconn().Close(); } return(dtBillDetails); }
public void SaveHDDSNo(string HDDSNo) { try { using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = SQLCon.Sqlconn(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "USP_U_POS_HDDSNO"; cmd.Parameters.AddWithValue("@BranchCounterID", Utility.branchinfo.BranchCounterID); cmd.Parameters.AddWithValue("@HDDSNO", HDDSNo); cmd.ExecuteNonQuery(); } } catch (Exception ex) { throw new Exception($"Error While saving machine identifier", ex); } finally { SQLCon.Sqlconn().Close(); } }
public DataTable GetBillByNumber(object BillNumber) { DataTable dtBillDetails = new DataTable(); try { using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = SQLCon.Sqlconn(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "[POS_USP_R_BILLBYNUMBER]"; cmd.Parameters.AddWithValue("@BILLNUMBER", BillNumber); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { da.Fill(dtBillDetails); } if (dtBillDetails != null && dtBillDetails.Rows.Count > 0 && int.TryParse(Convert.ToString(dtBillDetails.Rows[0][0]), out int ivalue)) { dtBillDetails.TableName = "BILLDETAILS"; } else { throw new Exception("Bill Does Not Exists!"); } } } catch (Exception ex) { throw new Exception("Error While Retrieving Bill Details", ex); } finally { SQLCon.Sqlconn().Close(); } return(dtBillDetails); }
public void ChangePassword(object UserID, object PasswordString) { try { using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = SQLCon.Sqlconn(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "[POS_USP_U_PASSWORD]"; cmd.Parameters.AddWithValue("@USERID", UserID); cmd.Parameters.AddWithValue("@PASSWORDSTRING", PasswordString); int IValue = cmd.ExecuteNonQuery(); if (IValue == 0) { throw new Exception("Error While Changing the Password!"); } } } catch (Exception ex) { throw ex; } finally { SQLCon.Sqlconn().Close(); } }
public DataSet GetInitialLoad(object userID, object branchCounterID) { DataSet dsInitialLoad = new DataSet(); try { using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = SQLCon.Sqlconn(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "[POS_USP_R_LOAD]"; cmd.Parameters.AddWithValue("@USerID", userID); cmd.Parameters.AddWithValue("@BranchCounterID", branchCounterID); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { da.Fill(dsInitialLoad); } dsInitialLoad.Tables[0].TableName = "DAYSEQUENCE"; if (dsInitialLoad.Tables.Count > 1) { dsInitialLoad.Tables[1].TableName = "BILL"; dsInitialLoad.Tables[2].TableName = "BILLDETAILS"; } } } catch (Exception ex) { throw new Exception("Error While Retrieving initial load details", ex); } finally { SQLCon.Sqlconn().Close(); } return(dsInitialLoad); }
public void InsertCRefundWOBill(DataTable dtRefund, object UserID, object CustomerName, object CustomerMobile) { List <string> columnsToRemove = new List <string> { "ITEMCODE", "ITEMNAME", "MRP", "SALEPRICE", "ISOPENITEM" }; try { DataTable dtTemp = dtRefund.Copy(); columnsToRemove.Where(dtTemp.Columns.Contains).ToList().ForEach(x => dtTemp.Columns.Remove(x)); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = SQLCon.Sqlconn(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "[POS_USP_C_CR_WO_BILL]"; cmd.Parameters.AddWithValue("@CR_WO_Bills", dtTemp); cmd.Parameters.AddWithValue("@UserID", UserID); cmd.Parameters.AddWithValue("@CustomerName", CustomerName); cmd.Parameters.AddWithValue("@CustomerMobile", CustomerMobile); int ivalue = cmd.ExecuteNonQuery(); if (ivalue <= 0) { throw new Exception("Error while saving Refund Details!"); } } } catch (Exception ex) { throw new Exception("Error While saving Refund Details", ex); } finally { SQLCon.Sqlconn().Close(); } }
public void DeleteBRefundDetail(object BRefundDetailID, object userID, DataTable dtSnos) { try { using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = SQLCon.Sqlconn(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "[POS_USP_D_BREFUNDDETAIL]"; cmd.Parameters.AddWithValue("@BREFUNDDETAILID", BRefundDetailID); cmd.Parameters.AddWithValue("@USERID", userID); cmd.Parameters.AddWithValue("@SNos", dtSnos); cmd.ExecuteNonQuery(); } } catch (Exception ex) { throw new Exception("Error While deleting bill refund detail", ex); } finally { SQLCon.Sqlconn().Close(); } }
public int SaveBRefundDetail(DataRow drDetail) { int BRefundDetailID = 0; try { using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = SQLCon.Sqlconn(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "[POS_USP_CU_BREFUNDDETAIL]"; cmd.Parameters.AddWithValue("@BREFUNDDETAILID", drDetail["BREFUNDDETAILID"]); cmd.Parameters.AddWithValue("@BREFUNDID", drDetail["BREFUNDID"]); cmd.Parameters.AddWithValue("@ITEMPRICEID", drDetail["ITEMPRICEID"]); cmd.Parameters.AddWithValue("@QUANTITY", drDetail["QUANTITY"]); cmd.Parameters.AddWithValue("@WEIGHTINKGS", drDetail["WEIGHTINKGS"]); cmd.Parameters.AddWithValue("@SNO", drDetail["SNO"]); cmd.Parameters.AddWithValue("@TRAYNUMBER", drDetail["TRAYNUMBER"]); object objReturn = cmd.ExecuteScalar(); if (!int.TryParse(objReturn.ToString(), out BRefundDetailID)) { throw new Exception(objReturn.ToString()); } } } catch (Exception ex) { throw ex; } finally { SQLCon.Sqlconn().Close(); } return(BRefundDetailID); }
public void AcceptDispatch(object stockDispatchID, DataTable dtDispatchDetail) { try { using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = SQLCon.Sqlconn(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "[POS_USP_FINISH_STOCKIN]"; cmd.Parameters.AddWithValue("@UserID", Utility.logininfo.UserID); cmd.Parameters.AddWithValue("@StockDispatchID", stockDispatchID); cmd.Parameters.AddWithValue("@DispatchDetails", dtDispatchDetail); cmd.ExecuteNonQuery(); } } catch (Exception ex) { throw new Exception("Error While closing stock in", ex); } finally { SQLCon.Sqlconn().Close(); } }