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();
}
}
