public Int64 Insert(SalesTransactionDetails Details) { Data.SalesTransactions clsSalesTransactions = new Data.SalesTransactions(); Int64 iRetValue = clsSalesTransactions.Insert(Details); clsSalesTransactions.CommitAndDispose(); return iRetValue; }
public Int64 Insert(SalesTransactionDetails Details) { // April 30, 2007 : Added "ChargeCode, ChargeRemarks" try { MySqlCommand cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; string SQL = "INSERT INTO tblTransactions (" + "TransactionType, " + "TransactionNo, " + "BranchID, " + "BranchCode, " + "RewardsCustomerID, " + "RewardsCustomerName, " + "CustomerID, " + "CustomerName, " + "CustomerGroupName, " + "ModeOfTerms, " + "Terms, " + "AgentID, " + "AgentName, " + "CreatedByID, " + "CreatedByName, " + "CashierID, " + "CashierName, " + "TerminalNo, " + "TransactionDate, " + "DateSuspended, " + "TransactionStatus," + "DiscountCode, " + "DiscountRemarks, " + "WaiterID, " + "WaiterName," + "ChargeCode, ChargeRemarks,OrderType, " + "AgentPositionName, AgentDepartmentName,DataSource, " + "ContactCheckInDate," + "CreatedOn, LastModified " + ")VALUES(" + "@TransactionType, " + "@TransactionNo, " + "@BranchID, " + "@BranchCode, " + "@RewardsCustomerID, " + "@RewardsCustomerName, " + "@CustomerID, " + "@CustomerName, " + "@CustomerGroupName, " + "@ModeOfTerms, " + "@Terms, " + "@AgentID, " + "@AgentName, " + "@CreatedByID, " + "@CreatedByName, " + "@CashierID, " + "@CashierName, " + "@TerminalNo, " + "@TransactionDate, " + "@DateSuspended, " + "@TransactionStatus," + "@DiscCode, " + "@DiscRemarks, " + "@WaiterID, " + "@WaiterName," + "@ChargeCode, @ChargeRemarks,@OrderType," + "@AgentPositionName, @AgentDepartmentName, @DataSource," + "@ContactCheckInDate, NOW(), NOW());"; cmd.Parameters.AddWithValue("TransactionType", Details.TransactionType.ToString("d")); cmd.Parameters.AddWithValue("TransactionNo", Details.TransactionNo); cmd.Parameters.AddWithValue("BranchID", Details.BranchID); cmd.Parameters.AddWithValue("BranchCode", Details.BranchCode); cmd.Parameters.AddWithValue("CustomerID", Details.CustomerID); cmd.Parameters.AddWithValue("RewardsCustomerID", Details.RewardsCustomerID); cmd.Parameters.AddWithValue("RewardsCustomerName", Details.RewardsCustomerName); cmd.Parameters.AddWithValue("CustomerName", Details.CustomerName); cmd.Parameters.AddWithValue("CustomerGroupName", Details.CustomerGroupName); cmd.Parameters.AddWithValue("ModeOfTerms", Details.CustomerDetails.ModeOfTerms.ToString("d")); cmd.Parameters.AddWithValue("Terms", Details.CustomerDetails.Terms); cmd.Parameters.AddWithValue("AgentID", Details.AgentID); cmd.Parameters.AddWithValue("AgentName", Details.AgentName); cmd.Parameters.AddWithValue("CreatedByID", Details.CreatedByID); cmd.Parameters.AddWithValue("CreatedByName", Details.CreatedByName); cmd.Parameters.AddWithValue("CashierID", Details.CashierID); cmd.Parameters.AddWithValue("CashierName", Details.CashierName); cmd.Parameters.AddWithValue("TerminalNo", Details.TerminalNo); cmd.Parameters.AddWithValue("TransactionDate", Details.TransactionDate.ToString("yyyy-MM-dd HH:mm:ss")); cmd.Parameters.AddWithValue("DateSuspended", Details.DateSuspended.ToString("yyyy-MM-dd HH:mm:ss")); cmd.Parameters.AddWithValue("TransactionStatus", Details.TransactionStatus.ToString("d")); cmd.Parameters.AddWithValue("DiscCode", Details.DiscountCode); if (Details.DiscountRemarks == null) Details.DiscountRemarks = ""; cmd.Parameters.AddWithValue("DiscRemarks", Details.DiscountRemarks); cmd.Parameters.AddWithValue("WaiterID", Details.WaiterID); cmd.Parameters.AddWithValue("WaiterName", Details.WaiterName); cmd.Parameters.AddWithValue("ChargeCode", Details.ChargeCode); if (Details.ChargeRemarks == null) Details.ChargeRemarks = ""; cmd.Parameters.AddWithValue("ChargeRemarks", Details.ChargeRemarks); cmd.Parameters.AddWithValue("OrderType", Details.OrderType.ToString("d")); cmd.Parameters.AddWithValue("AgentPositionName", Details.AgentPositionName); cmd.Parameters.AddWithValue("AgentDepartmentName", Details.AgentDepartmentName); cmd.Parameters.AddWithValue("DataSource", Details.DataSource); cmd.Parameters.AddWithValue("ContactCheckInDate", Details.CustomerDetails.LastCheckInDate); cmd.CommandText = SQL; base.ExecuteNonQuery(cmd); Int64 intID = Int64.Parse(base.getLAST_INSERT_ID(this)); SQL = "UPDATE tblTransactions SET SyncID = TransactionID WHERE TransactionID = @TransactionID AND SyncID = 0;"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("TransactionID", intID); cmd.CommandText = SQL; base.ExecuteNonQuery(cmd); return intID; } catch (Exception ex) { throw base.ThrowException(ex); } }
public static SalesTransactionDetails setDetails(System.Data.DataTable dt) { SalesTransactionDetails Details = new SalesTransactionDetails(); foreach (System.Data.DataRow dr in dt.Rows) { Details.TransactionID = Int64.Parse(dr["TransactionID"].ToString()); Details.TransactionType = (TransactionTypes)Enum.Parse(typeof(TransactionTypes), dr["TransactionType"].ToString()); Details.TransactionNo = "" + dr["TransactionNo"].ToString(); Details.ORNo = "" + dr["ORNo"].ToString(); Details.BranchID = Int32.Parse(dr["BranchID"].ToString()); Details.BranchCode = "" + dr["BranchCode"].ToString(); Details.PaxNo = Int32.Parse(dr["PaxNo"].ToString()); Details.ModeOfTerms = (ModeOfTerms)Enum.Parse(typeof(ModeOfTerms), dr["ModeOfTerms"].ToString()); Details.Terms = Int32.Parse(dr["Terms"].ToString()); Details.CRNo = Int64.Parse(dr["CRNo"].ToString()); Details.RewardsCustomerID = Int32.Parse(dr["RewardsCustomerID"].ToString()); Details.RewardsCustomerName = dr["RewardsCustomerName"].ToString(); Details.CustomerID = Int32.Parse(dr["CustomerID"].ToString()); Details.CustomerName = "" + dr["CustomerName"].ToString(); Details.CustomerGroupName = "" + dr["CustomerGroupName"].ToString(); Details.AgentID = Int32.Parse(dr["AgentID"].ToString()); Details.AgentName = "" + dr["AgentName"].ToString(); Details.CreatedByID = Int64.Parse(dr["CreatedByID"].ToString()); Details.CreatedByName = "" + dr["CreatedByName"].ToString(); Details.CashierID = Int64.Parse(dr["CashierID"].ToString()); Details.CashierName = "" + dr["CashierName"].ToString(); Details.TerminalNo = "" + dr["TerminalNo"].ToString(); Details.TransactionDate = DateTime.Parse(dr["TransactionDate"].ToString()); Details.DateSuspended = DateTime.Parse(dr["DateSuspended"].ToString()); Details.DateResumed = DateTime.Parse(dr["DateResumed"].ToString()); Details.TransactionStatus = (TransactionStatus)Enum.Parse(typeof(TransactionStatus), dr["TransactionStatus"].ToString()); Details.GrossSales = decimal.Parse(dr["GrossSales"].ToString()); Details.SubTotal = decimal.Parse(dr["SubTotal"].ToString()); Details.NetSales = decimal.Parse(dr["NetSales"].ToString()); Details.ItemsDiscount = decimal.Parse(dr["ItemsDiscount"].ToString()); Details.SNRItemsDiscount = decimal.Parse(dr["SNRItemsDiscount"].ToString()); Details.PWDItemsDiscount = decimal.Parse(dr["PWDItemsDiscount"].ToString()); Details.OtherItemsDiscount = decimal.Parse(dr["OtherItemsDiscount"].ToString()); Details.Discount = decimal.Parse(dr["Discount"].ToString()); // Sep 14, 2014 Separate the discounts for VAT computation Details.SNRDiscount = decimal.Parse(dr["SNRDiscount"].ToString()); Details.PWDDiscount = decimal.Parse(dr["PWDDiscount"].ToString()); Details.OtherDiscount = decimal.Parse(dr["OtherDiscount"].ToString()); // Aug 6, 2011 : Include in loading DiscountCode Details.DiscountCode = "" + dr["DiscountCode"].ToString(); // Aug 6, 2011 : Include in loading DiscountRemarks Details.DiscountRemarks = "" + dr["DiscountRemarks"].ToString(); Details.TransDiscount = decimal.Parse(dr["TransDiscount"].ToString()); Details.TransDiscountType = (DiscountTypes)Enum.Parse(typeof(DiscountTypes), dr["TransDiscountType"].ToString()); Details.VAT = decimal.Parse(dr["VAT"].ToString()); Details.VATableAmount = decimal.Parse(dr["VATableAmount"].ToString()); Details.ZeroRatedSales = decimal.Parse(dr["ZeroRatedSales"].ToString()); Details.NonVATableAmount = decimal.Parse(dr["NonVATableAmount"].ToString()); Details.VATExempt = decimal.Parse(dr["VATExempt"].ToString()); Details.EVAT = decimal.Parse(dr["EVAT"].ToString()); Details.EVATableAmount = decimal.Parse(dr["EVATableAmount"].ToString()); Details.NonEVATableAmount = decimal.Parse(dr["NonEVATableAmount"].ToString()); Details.LocalTax = decimal.Parse(dr["LocalTax"].ToString()); Details.AmountPaid = decimal.Parse(dr["AmountPaid"].ToString()); Details.CashPayment = decimal.Parse(dr["CashPayment"].ToString()); Details.ChequePayment = decimal.Parse(dr["ChequePayment"].ToString()); Details.CreditCardPayment = decimal.Parse(dr["CreditCardPayment"].ToString()); Details.CreditPayment = decimal.Parse(dr["CreditPayment"].ToString()); Details.DebitPayment = decimal.Parse(dr["DebitPayment"].ToString()); Details.RewardPointsPayment = decimal.Parse(dr["RewardPointsPayment"].ToString()); Details.RewardConvertedPayment = decimal.Parse(dr["RewardConvertedPayment"].ToString()); Details.BalanceAmount = decimal.Parse(dr["BalanceAmount"].ToString()); Details.ChangeAmount = decimal.Parse(dr["ChangeAmount"].ToString()); Details.DateClosed = DateTime.Parse(dr["DateClosed"].ToString()); Details.PaymentType = (PaymentTypes)Enum.Parse(typeof(PaymentTypes), dr["PaymentType"].ToString()); Details.WaiterID = Int64.Parse(dr["WaiterID"].ToString()); Details.WaiterName = "" + dr["WaiterName"].ToString(); Details.Charge = decimal.Parse(dr["Charge"].ToString()); Details.ChargeAmount = decimal.Parse(dr["ChargeAmount"].ToString()); Details.ChargeCode = "" + dr["ChargeCode"].ToString(); Details.ChargeRemarks = "" + dr["ChargeRemarks"].ToString(); Details.ChargeType = (ChargeTypes)Enum.Parse(typeof(ChargeTypes), dr["ChargeType"].ToString()); Details.CreditChargeAmount = decimal.Parse(dr["CreditChargeAmount"].ToString()); Details.OrderType = (OrderTypes)Enum.Parse(typeof(OrderTypes), dr["OrderType"].ToString()); Details.AgentPositionName = "" + dr["AgentPositionName"].ToString(); Details.AgentDepartmentName = "" + dr["AgentDepartmentName"].ToString(); Details.isExist = true; Details.isConsignment = Convert.ToBoolean(dr["isConsignment"]); Details.isZeroRated = Convert.ToBoolean(dr["isZeroRated"]); Details.ItemSold = decimal.Parse(dr["ItemSold"].ToString()); Details.QuantitySold = decimal.Parse(dr["QuantitySold"].ToString()); Details.TrustFund = decimal.Parse(dr["TrustFund"].ToString()); break; } return Details; }
public System.Data.DataTable Cash_Cheque_CreditCard_Credit_Sales(SalesTransactionsColumns clsSalesTransactionsColumns, SalesTransactionDetails clsSearchKeys, System.Data.SqlClient.SortOrder SequenceSortOrder, int Limit, string SortField, System.Data.SqlClient.SortOrder SortOrder) { try { MySqlCommand cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; string SQL = SQLSelect(clsSalesTransactionsColumns) + "WHERE TransactionStatus <> 0 "; if (clsSearchKeys.BranchID != 0) { SQL += "AND tblTransactions.BranchID = @BranchID "; cmd.Parameters.AddWithValue("@BranchID", clsSearchKeys.BranchID); } if (!string.IsNullOrEmpty(clsSearchKeys.BranchCode)) { SQL += "AND tblTransactions.BranchCode = @BranchCode "; cmd.Parameters.AddWithValue("@BranchCode", clsSearchKeys.BranchCode); } if (clsSearchKeys.TransactionID != 0) { SQL += "AND tblTransactions.TransactionID = @TransactionID "; cmd.Parameters.AddWithValue("@TransactionID", clsSearchKeys.TransactionID); } if (!string.IsNullOrEmpty(clsSearchKeys.TransactionNo)) { SQL += "AND tblTransactions.TransactionNo = @TransactionNo "; cmd.Parameters.AddWithValue("@TransactionNo", clsSearchKeys.TransactionNo); } if (!string.IsNullOrEmpty(clsSearchKeys.CustomerName)) { SQL += "AND tblTransactions.CustomerName = @CustomerName "; cmd.Parameters.AddWithValue("@CustomerName", clsSearchKeys.CustomerName); } if (!string.IsNullOrEmpty(clsSearchKeys.CashierName)) { SQL += "AND tblTransactions.CashierName = @CashierName "; cmd.Parameters.AddWithValue("@CashierName", clsSearchKeys.CashierName); } if (!string.IsNullOrEmpty(clsSearchKeys.TerminalNo)) { SQL += "AND tblTransactions.TerminalNo = @TerminalNo "; cmd.Parameters.AddWithValue("@TerminalNo", clsSearchKeys.TerminalNo); } if (clsSearchKeys.TransactionDateFrom != DateTime.MinValue) { SQL += "AND DATE_FORMAT(TransactionDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(@TransactionDateFrom, '%Y-%m-%d %H:%i') "; cmd.Parameters.AddWithValue("@TransactionDateFrom", clsSearchKeys.TransactionDateFrom); } if (clsSearchKeys.TransactionDateTo != DateTime.MinValue) { SQL += "AND DATE_FORMAT(TransactionDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(@TransactionDateTo, '%Y-%m-%d %H:%i') "; cmd.Parameters.AddWithValue("@TransactionDateTo", clsSearchKeys.TransactionDateTo); } if (clsSearchKeys.TransactionStatus != TransactionStatus.NotYetApplied) { SQL += "AND TransactionStatus = @TransactionStatus "; cmd.Parameters.AddWithValue("@TransactionStatus", clsSearchKeys.TransactionStatus.ToString("d")); } if (clsSearchKeys.PaymentType != PaymentTypes.NotYetAssigned) { SQL += "AND PaymentType = @PaymentType "; cmd.Parameters.AddWithValue("@PaymentType", clsSearchKeys.PaymentType.ToString("d")); } if (SortField != string.Empty && SortField != null) { SQL += "ORDER BY " + SortField + " "; if (SortOrder != System.Data.SqlClient.SortOrder.Descending) SQL += "ASC "; else SQL += "DESC "; } if (Limit != 0) SQL += "LIMIT " + Limit + " "; cmd.CommandText = SQL; string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); return dt; } catch (Exception ex) { throw base.ThrowException(ex); } }
public Int64 AddItem(SalesTransactionDetails SalesTransactionDetails, SalesTransactionItemDetails SalesTransItemDetails) { try { UpdateSubTotal(SalesTransactionDetails.TransactionID, SalesTransactionDetails.ItemSold, SalesTransactionDetails.QuantitySold, SalesTransactionDetails.GrossSales, SalesTransactionDetails.SubTotal, SalesTransactionDetails.NetSales, SalesTransactionDetails.ItemsDiscount, SalesTransactionDetails.SNRItemsDiscount, SalesTransactionDetails.PWDItemsDiscount, SalesTransactionDetails.OtherItemsDiscount, SalesTransactionDetails.Discount, SalesTransactionDetails.SNRDiscount, SalesTransactionDetails.PWDDiscount, SalesTransactionDetails.OtherDiscount, SalesTransactionDetails.TransDiscount, SalesTransactionDetails.TransDiscountType, SalesTransactionDetails.VAT, SalesTransactionDetails.VATableAmount, SalesTransactionDetails.ZeroRatedSales, SalesTransactionDetails.NonVATableAmount, SalesTransactionDetails.VATExempt, SalesTransactionDetails.EVAT, SalesTransactionDetails.EVATableAmount, SalesTransactionDetails.NonEVATableAmount, SalesTransactionDetails.LocalTax, SalesTransactionDetails.DiscountCode, SalesTransactionDetails.DiscountRemarks, SalesTransactionDetails.Charge, SalesTransactionDetails.ChargeAmount, SalesTransactionDetails.ChargeCode, SalesTransactionDetails.ChargeRemarks, SalesTransactionDetails.ChargeType); SalesTransactionItems clsSalesTransactionItems = new SalesTransactionItems(base.Connection, base.Transaction); Int64 TransactionItemID = clsSalesTransactionItems.Insert(SalesTransItemDetails); return TransactionItemID; } catch (Exception ex) { throw base.ThrowException(ex); } }
public System.Data.DataTable ListAsDataTable(SalesTransactionDetails clsSearchKey, bool WithTF = false, string SortField = "", SortOption SortOption = SortOption.Ascending, Int32 limit = 0) { try { return ListAsDataTable(clsSearchKey.BranchID, clsSearchKey.TerminalNo, clsSearchKey.TransactionID, clsSearchKey.TransactionNo, clsSearchKey.TransactionDateFrom, clsSearchKey.TransactionDateTo, clsSearchKey.TransactionStatus, clsSearchKey.PaymentType, clsSearchKey.isConsignment, false, clsSearchKey.CustomerName, clsSearchKey.CustomerGroupName, clsSearchKey.CashierID, clsSearchKey.CashierName, clsSearchKey.AgentName, WithTF, false, SortField, SortOption, limit); ; } catch (Exception ex) { throw base.ThrowException(ex); } }
public System.Data.DataTable List(SalesTransactionsColumns clsSalesTransactionsColumns, SalesTransactionDetails clsSearchKeys, System.Data.SqlClient.SortOrder SequenceSortOrder, int Limit, string SortField, System.Data.SqlClient.SortOrder SortOrder) { try { MySqlCommand cmd = new MySqlCommand(); string SQL = SQLSelect(clsSalesTransactionsColumns) + "WHERE 1=1 "; if (clsSearchKeys.BranchID != 0) { SQL += "AND tblTransactions.BranchID = @BranchID "; MySqlParameter prmBranchID = new MySqlParameter("@BranchID",MySqlDbType.Int32); prmBranchID.Value = clsSearchKeys.BranchID; cmd.Parameters.Add(prmBranchID); } if (clsSearchKeys.BranchCode != string.Empty && clsSearchKeys.BranchCode != null) { SQL += "AND tblTransactions.BranchCode = @BranchCode "; MySqlParameter prmBranchCode = new MySqlParameter("@BranchCode",MySqlDbType.String); prmBranchCode.Value = clsSearchKeys.BranchCode; cmd.Parameters.Add(prmBranchCode); } if (clsSearchKeys.TransactionID != 0) { SQL += "AND tblTransactions.TransactionID = @TransactionID "; MySqlParameter prmTransactionID = new MySqlParameter("@TransactionID",MySqlDbType.Int64); prmTransactionID.Value = clsSearchKeys.TransactionID; cmd.Parameters.Add(prmTransactionID); } if (!string.IsNullOrEmpty(clsSearchKeys.TransactionNo)) { SQL += "AND tblTransactions.TransactionNo = @TransactionNo "; MySqlParameter prmTransactionNo = new MySqlParameter("@TransactionNo",MySqlDbType.String); prmTransactionNo.Value = clsSearchKeys.TransactionNo; cmd.Parameters.Add(prmTransactionNo); } if (!string.IsNullOrEmpty(clsSearchKeys.CustomerName)) { SQL += "AND tblTransactions.CustomerName LIKE @CustomerName "; cmd.Parameters.AddWithValue("@CustomerName", "%" + clsSearchKeys.CustomerName + "%"); } if (!string.IsNullOrEmpty(clsSearchKeys.CustomerGroupName)) { SQL += "AND tblTransactions.CustomerGroupName LIKE @CustomerGroupName "; cmd.Parameters.AddWithValue("@CustomerGroupName", "%" + clsSearchKeys.CustomerGroupName + "%"); } if (!string.IsNullOrEmpty(clsSearchKeys.CashierName)) { SQL += "AND tblTransactions.CashierName LIKE @CashierName "; cmd.Parameters.AddWithValue("@CashierName", "%" + clsSearchKeys.CashierName + "%"); } if (!string.IsNullOrEmpty(clsSearchKeys.AgentName)) { SQL += "AND tblTransactions.AgentName LIKE @AgentName "; cmd.Parameters.AddWithValue("@AgentName", "%" + clsSearchKeys.AgentName + "%"); } if (clsSearchKeys.TerminalNo != string.Empty && clsSearchKeys.TerminalNo != null) { SQL += "AND tblTransactions.TerminalNo = @TerminalNo "; MySqlParameter prmTerminalNo = new MySqlParameter("@TerminalNo",MySqlDbType.String); prmTerminalNo.Value = clsSearchKeys.TerminalNo; cmd.Parameters.Add(prmTerminalNo); } if (clsSearchKeys.TransactionDateFrom != DateTime.MinValue) { SQL += "AND DATE_FORMAT(TransactionDate, '%Y-%m-%d %H:%i') >= DATE_FORMAT(@TransactionDateFrom, '%Y-%m-%d %H:%i') "; MySqlParameter prmTransactionDateFrom = new MySqlParameter("@TransactionDateFrom",MySqlDbType.DateTime); prmTransactionDateFrom.Value = clsSearchKeys.TransactionDateFrom.ToString("yyyy-MM-dd HH:mm:ss"); cmd.Parameters.Add(prmTransactionDateFrom); } if (clsSearchKeys.TransactionDateTo != DateTime.MinValue) { SQL += "AND DATE_FORMAT(TransactionDate, '%Y-%m-%d %H:%i') <= DATE_FORMAT(@TransactionDateTo, '%Y-%m-%d %H:%i') "; MySqlParameter prmTransactionDateTo = new MySqlParameter("@TransactionDateTo",MySqlDbType.DateTime); prmTransactionDateTo.Value = clsSearchKeys.TransactionDateTo.ToString("yyyy-MM-dd HH:mm:ss"); cmd.Parameters.Add(prmTransactionDateTo); } if (clsSearchKeys.TransactionStatus != TransactionStatus.NotYetApplied) { SQL += "AND TransactionStatus = @TransactionStatus "; MySqlParameter prmTransactionStatus = new MySqlParameter("@TransactionStatus",MySqlDbType.Int16); prmTransactionStatus.Value = clsSearchKeys.TransactionStatus.ToString("d"); cmd.Parameters.Add(prmTransactionStatus); } if (clsSearchKeys.PaymentType != PaymentTypes.NotYetAssigned) { SQL += "AND PaymentType = @PaymentType "; MySqlParameter prmPaymentType = new MySqlParameter("@PaymentType",MySqlDbType.Int16); prmPaymentType.Value = clsSearchKeys.PaymentType.ToString("d"); cmd.Parameters.Add(prmPaymentType); } if (clsSearchKeys.isConsignmentSearch != "-1") { SQL += "AND isConsignment = @isConsignment "; cmd.Parameters.AddWithValue("@isConsignment", clsSearchKeys.isConsignment ? 1 : 0); } if (SortField != string.Empty && SortField != null) { SQL += "ORDER BY " + SortField + " "; if (SortOrder != System.Data.SqlClient.SortOrder.Descending) SQL += "ASC "; else SQL += "DESC "; } if (Limit != 0) SQL += "LIMIT " + Limit + " "; cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = SQL; string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); return dt; } catch (Exception ex) { throw base.ThrowException(ex); } }
private SalesTransactionDetails setSalesTransactionDetails(GLATransactionDetails glaDetails) { SalesTransactionDetails mclsSalesTransactionDetails = new SalesTransactionDetails(); // for insert mclsSalesTransactionDetails.CustomerID = glaDetails.fk_emp_def; mclsSalesTransactionDetails.AgentID = Constants.C_RETAILPLUS_AGENTID; mclsSalesTransactionDetails.AgentName = Constants.C_RETAILPLUS_AGENT; mclsSalesTransactionDetails.AgentPositionName = Constants.C_RETAILPLUS_AGENT_POSITIONNAME; mclsSalesTransactionDetails.AgentDepartmentName = Constants.C_RETAILPLUS_AGENT_DEPARTMENT_NAME; mclsSalesTransactionDetails.WaiterID = Constants.C_RETAILPLUS_WAITERID; mclsSalesTransactionDetails.WaiterName = Constants.C_RETAILPLUS_WAITER; mclsSalesTransactionDetails.CreatedByID = glaDetails.fk_emp_def; mclsSalesTransactionDetails.CreatedByName = Constants.C_RETAILPLUS_WAITER; mclsSalesTransactionDetails.CashierID = glaDetails.fk_emp_def; mclsSalesTransactionDetails.CashierName = glaDetails.Filename; mclsSalesTransactionDetails.CustomerID = Constants.C_RETAILPLUS_CUSTOMERID; mclsSalesTransactionDetails.CustomerName = Constants.C_RETAILPLUS_CUSTOMER; mclsSalesTransactionDetails.TransactionDate = glaDetails.Chk_Open_Date_Time; mclsSalesTransactionDetails.DateSuspended = Constants.C_DATE_MIN_VALUE; mclsSalesTransactionDetails.TerminalNo = Constants.C_DEFAULT_TERMINAL_01; mclsSalesTransactionDetails.BranchID = Constants.BRANCH_ID_MAIN; mclsSalesTransactionDetails.BranchCode = Constants.BRANCH_MAIN; mclsSalesTransactionDetails.TransactionStatus = TransactionStatus.Closed; mclsSalesTransactionDetails.TransactionType = TransactionTypes.POSNormal; mclsSalesTransactionDetails.TransactionNo = glaDetails.chk_headers_seq_number.ToString(); //for update mclsSalesTransactionDetails.Charge = glaDetails.Auto_Svc_Ttl + glaDetails.Other_Svc_Ttl + glaDetails.Tip_ttl; mclsSalesTransactionDetails.Discount = -glaDetails.Dsc_Ttl; mclsSalesTransactionDetails.AmountDue = glaDetails.Sub_Ttl + glaDetails.Tax_Ttl + mclsSalesTransactionDetails.Charge - mclsSalesTransactionDetails.Discount; mclsSalesTransactionDetails.SubTotal = glaDetails.Sub_Ttl + glaDetails.Tax_Ttl + mclsSalesTransactionDetails.Charge; mclsSalesTransactionDetails.DiscountableAmount = mclsSalesTransactionDetails.Discount <= 0 ? 0 : glaDetails.Dsc_Ttl; mclsSalesTransactionDetails.ItemsDiscount = 0; mclsSalesTransactionDetails.VAT = glaDetails.Tax_Ttl; mclsSalesTransactionDetails.VATableAmount = glaDetails.Sub_Ttl + glaDetails.Tax_Ttl; mclsSalesTransactionDetails.NonVATableAmount = 0; mclsSalesTransactionDetails.EVAT = 0; mclsSalesTransactionDetails.EVATableAmount = 0; mclsSalesTransactionDetails.NonEVATableAmount = glaDetails.Sub_Ttl + glaDetails.Tax_Ttl; mclsSalesTransactionDetails.LocalTax = 0; mclsSalesTransactionDetails.ItemSold = glaDetails.Cov_Cnt; mclsSalesTransactionDetails.QuantitySold = glaDetails.Num_Dtl; mclsSalesTransactionDetails.AmountPaid = glaDetails.Pymnt_Ttl; mclsSalesTransactionDetails.CashPayment = mclsSalesTransactionDetails.SubTotal; mclsSalesTransactionDetails.ChangeAmount = glaDetails.Pymnt_Ttl; mclsSalesTransactionDetails.ChequePayment = 0; mclsSalesTransactionDetails.CreditCardPayment = 0; mclsSalesTransactionDetails.CreditPayment = 0; mclsSalesTransactionDetails.CreditChargeAmount = 0; mclsSalesTransactionDetails.DebitPayment = 0; mclsSalesTransactionDetails.RewardPointsPayment = 0; mclsSalesTransactionDetails.DateClosed = glaDetails.Chk_Closed_Date_Time; mclsSalesTransactionDetails.DateResumed = glaDetails.DateCreated; mclsSalesTransactionDetails.DataSource = glaDetails.BatchID; return mclsSalesTransactionDetails; }
private void SetDataSource(ReportDocument Report) { string strProductGroup = cboProductGroup.SelectedItem.Text == Constants.ALL ? string.Empty : cboProductGroup.SelectedItem.Text; string TransactionNo = txtTransactionNo.Text; string CustomerName = cboContactName.SelectedItem.Text.Substring(0, 3).Trim() == Constants.ALL ? cboContactName.SelectedItem.Text.Replace("ALL", "").Replace("LIKE","").Trim() : cboContactName.SelectedItem.Text; string AgentName = cboAgent.SelectedItem.Text.Substring(0, 3).Trim() == Constants.ALL ? cboAgent.SelectedItem.Text.Replace("ALL", "").Replace("LIKE", "").Trim() : cboAgent.SelectedItem.Text; string CashierName = cboCashierName.SelectedItem.Text.Substring(0, 3).Trim() == Constants.ALL ? cboCashierName.SelectedItem.Text.Replace("ALL", "").Replace("LIKE", "").Trim() : cboCashierName.SelectedItem.Text; string TerminalNo = cboTerminalNo.SelectedItem.Text == Constants.ALL ? string.Empty : cboTerminalNo.SelectedItem.Text; DateTime StartTransactionDate = DateTime.MinValue; try { StartTransactionDate = Convert.ToDateTime(txtStartTransactionDate.Text + " " + txtStartTime.Text); } catch { } DateTime EndTransactionDate = DateTime.MinValue; try { EndTransactionDate = Convert.ToDateTime(txtEndTransactionDate.Text + " " + txtEndTime.Text); } catch { } TransactionStatus Status = (TransactionStatus)Enum.Parse(typeof(TransactionStatus), cboTransactionStatus.SelectedItem.Value); PaymentTypes PaymentType = (PaymentTypes)Enum.Parse(typeof(PaymentTypes), cboPaymentType.SelectedItem.Value); DataTable dt = new DataTable(); ReportDataset rptds = new ReportDataset(); SalesTransactions clsSalesTransactions; SalesTransactionsColumns clsSalesTransactionsColumns = new SalesTransactionsColumns(); #region clsSalesTransactionsColumns clsSalesTransactionsColumns.BranchCode = true; clsSalesTransactionsColumns.TransactionNo = true; clsSalesTransactionsColumns.CustomerName = true; clsSalesTransactionsColumns.CustomerGroupName = true; clsSalesTransactionsColumns.CashierName = true; clsSalesTransactionsColumns.TerminalNo = true; clsSalesTransactionsColumns.TransactionDate = true; clsSalesTransactionsColumns.DateSuspended = true; clsSalesTransactionsColumns.DateResumed = true; clsSalesTransactionsColumns.TransactionStatus = true; clsSalesTransactionsColumns.SubTotal = true; clsSalesTransactionsColumns.Discount = true; clsSalesTransactionsColumns.VAT = true; clsSalesTransactionsColumns.VATableAmount = true; clsSalesTransactionsColumns.LocalTax = true; clsSalesTransactionsColumns.AmountPaid = true; clsSalesTransactionsColumns.CashPayment = true; clsSalesTransactionsColumns.ChequePayment = true; clsSalesTransactionsColumns.CreditCardPayment = true; clsSalesTransactionsColumns.BalanceAmount = true; clsSalesTransactionsColumns.ChangeAmount = true; clsSalesTransactionsColumns.DateClosed = true; clsSalesTransactionsColumns.PaymentType = true; clsSalesTransactionsColumns.ItemsDiscount = true; clsSalesTransactionsColumns.Charge = true; clsSalesTransactionsColumns.CreditPayment = true; clsSalesTransactionsColumns.CreatedByName = true; clsSalesTransactionsColumns.AgentName = true; clsSalesTransactionsColumns.PaxNo = true; #endregion SalesTransactionDetails clsSearchKey = new SalesTransactionDetails(); clsSearchKey = new SalesTransactionDetails(); clsSearchKey.TransactionNo = TransactionNo; clsSearchKey.CustomerName = CustomerName; clsSearchKey.CashierName = CashierName; clsSearchKey.TerminalNo = TerminalNo; clsSearchKey.BranchID = int.Parse(cboBranch.SelectedItem.Value); clsSearchKey.TransactionDateFrom = StartTransactionDate; clsSearchKey.TransactionDateTo = EndTransactionDate; clsSearchKey.TransactionStatus = Status; clsSearchKey.PaymentType = PaymentType; clsSearchKey.AgentName = AgentName; clsSearchKey.isConsignmentSearch = cboConsignment.SelectedItem.Value; if (clsSearchKey.isConsignmentSearch != "-1") { clsSearchKey.isConsignment = bool.Parse(cboConsignment.SelectedItem.Value); } bool boWithTrustFund = true; string strReportType = cboReportType.SelectedValue; switch (strReportType) { case ReportTypes.SalesPerDay: #region Sales Per Day clsSalesTransactions = new SalesTransactions(); dt = clsSalesTransactions.ListAsDataTable(clsSearchKey.BranchID, clsSearchKey.TerminalNo, TransactionStatus: clsSearchKey.TransactionStatus, TransactionDateFrom: StartTransactionDate, TransactionDateTo: EndTransactionDate, WithTF: boWithTrustFund); //dt = clsSalesTransactions.SalesPerDay(StartTransactionDate, EndTransactionDate, boWithTrustFund); clsSalesTransactions.CommitAndDispose(); foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.Transactions.NewRow(); foreach (DataColumn dc in rptds.Transactions.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.Transactions.Rows.Add(drNew); } break; #endregion case ReportTypes.SummarizeDailySales: #region SummarizeDailySales Data.TerminalReportHistory clsTerminalReportHistory = new Data.TerminalReportHistory(); dt = clsTerminalReportHistory.SummarizedDailySalesReport(Int32.Parse(cboBranch.SelectedItem.Value), TerminalNo, true, StartTransactionDate, EndTransactionDate, boWithTrustFund); clsTerminalReportHistory.CommitAndDispose(); foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.SalesReport.NewRow(); foreach (DataColumn dc in rptds.SalesReport.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.SalesReport.Rows.Add(drNew); } break; #endregion default: break; } Report.SetDataSource(rptds); SetParameters(Report); }
public SalesTransactionDetails[] EJournalReport(Int32 BranchID, string CashierName, string TerminalNo) { try { MySqlCommand cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; string SQL = "SELECT TransactionNo FROM tblTransactions " + "WHERE TerminalNo = @TerminalNo " + "AND BranchID = @BranchID " + "AND CashierName = @CashierName " + "AND (TransactionStatus = @TransactionStatusClosed " + " OR TransactionStatus = @TransactionStatusVoid " + " OR TransactionStatus = @TransactionStatusReprinted " + " OR TransactionStatus = @TransactionStatusRefund) " + "AND TransactionDate >= (SELECT DateLastInitialized FROM tblTerminalReport WHERE TerminalNo = @TerminalNo AND BranchID = @BranchID)"; cmd.Parameters.AddWithValue("@BranchID", BranchID); cmd.Parameters.AddWithValue("@TerminalNo", TerminalNo); cmd.Parameters.AddWithValue("@CashierName", CashierName); cmd.Parameters.AddWithValue("@TransactionStatusClosed", TransactionStatus.Closed.ToString("d")); cmd.Parameters.AddWithValue("@TransactionStatusVoid", TransactionStatus.Void.ToString("d")); cmd.Parameters.AddWithValue("@TransactionStatusReprinted", TransactionStatus.Reprinted.ToString("d")); cmd.Parameters.AddWithValue("@TransactionStatusRefund", TransactionStatus.Refund.ToString("d")); cmd.CommandText = SQL; string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); ArrayList items = new ArrayList(); SalesTransactionDetails Details = new SalesTransactionDetails(); foreach (System.Data.DataRow dr in dt.Rows) { Details = new Data.SalesTransactions(base.Connection, base.Transaction).Details(dr["TransactionNo"].ToString(), TerminalNo, BranchID); Details.TransactionItems = new SalesTransactionItems(base.Connection, base.Transaction).Details(Details.TransactionID, Details.TransactionDate); items.Add(Details); } SalesTransactionDetails[] arrclsSalesTransactionDetails = new SalesTransactionDetails[0]; if (items != null) { arrclsSalesTransactionDetails = new SalesTransactionDetails[items.Count]; items.CopyTo(arrclsSalesTransactionDetails); } return arrclsSalesTransactionDetails; } catch (Exception ex) { throw base.ThrowException(ex); } }
private void SetDataSource(ReportDocument Report) { string strProductGroup = cboProductGroup.SelectedItem.Text == Constants.ALL ? string.Empty : cboProductGroup.SelectedItem.Text; string TransactionNo = txtTransactionNo.Text; string CustomerGroupName = cboContactGroupName.SelectedItem.Text.Substring(0, 3).Trim() == Constants.ALL ? cboContactGroupName.SelectedItem.Text.Replace("ALL", "").Replace("LIKE", "").Trim() : cboContactGroupName.SelectedItem.Text; string CustomerName = cboContactName.SelectedItem.Text.Substring(0, 3).Trim() == Constants.ALL ? cboContactName.SelectedItem.Text.Replace("ALL", "").Replace("LIKE","").Trim() : cboContactName.SelectedItem.Text; string AgentName = cboAgent.SelectedItem.Text.Substring(0, 3).Trim() == Constants.ALL ? cboAgent.SelectedItem.Text.Replace("ALL", "").Replace("LIKE", "").Trim() : cboAgent.SelectedItem.Text; string CashierName = cboCashierName.SelectedItem.Text.Substring(0, 3).Trim() == Constants.ALL ? cboCashierName.SelectedItem.Text.Replace("ALL", "").Replace("LIKE", "").Trim() : cboCashierName.SelectedItem.Text; string TerminalNo = cboTerminalNo.SelectedItem.Text == Constants.ALL ? string.Empty : cboTerminalNo.SelectedItem.Text; DateTime StartValidityDate = DateTime.TryParse(txtStartValidityDate.Text + " 00:00:00" + txtStartTime.Text, out StartValidityDate) ? StartValidityDate : DateTime.MinValue; DateTime EndValidityDate = DateTime.TryParse(txtEndValidityDate.Text + " 23:59:59", out EndValidityDate) ? EndValidityDate : DateTime.MinValue; DateTime StartTransactionDate = DateTime.TryParse(txtStartTransactionDate.Text + " " + txtStartTime.Text, out StartTransactionDate) ? StartTransactionDate : DateTime.MinValue; DateTime EndTransactionDate = DateTime.TryParse(txtEndTransactionDate.Text + " " + txtEndTime.Text, out EndTransactionDate) ? EndTransactionDate : DateTime.MinValue; TransactionStatus Status = (TransactionStatus)Enum.Parse(typeof(TransactionStatus), cboTransactionStatus.SelectedItem.Value); PaymentTypes PaymentType = (PaymentTypes)Enum.Parse(typeof(PaymentTypes), cboPaymentType.SelectedItem.Value); DataTable dt = new DataTable(); ReportDataset rptds = new ReportDataset(); SalesTransactions clsSalesTransactions; SalesTransactionItems clsSalesTransactionItems; SalesTransactionsColumns clsSalesTransactionsColumns = new SalesTransactionsColumns(); #region clsSalesTransactionsColumns clsSalesTransactionsColumns.BranchCode = true; clsSalesTransactionsColumns.TransactionNo = true; clsSalesTransactionsColumns.CustomerName = true; clsSalesTransactionsColumns.CustomerGroupName = true; clsSalesTransactionsColumns.CashierName = true; clsSalesTransactionsColumns.TerminalNo = true; clsSalesTransactionsColumns.TransactionDate = true; clsSalesTransactionsColumns.DateSuspended = true; clsSalesTransactionsColumns.DateResumed = true; clsSalesTransactionsColumns.TransactionStatus = true; clsSalesTransactionsColumns.SubTotal = true; clsSalesTransactionsColumns.Discount = true; clsSalesTransactionsColumns.VAT = true; clsSalesTransactionsColumns.VATableAmount = true; clsSalesTransactionsColumns.LocalTax = true; clsSalesTransactionsColumns.AmountPaid = true; clsSalesTransactionsColumns.CashPayment = true; clsSalesTransactionsColumns.ChequePayment = true; clsSalesTransactionsColumns.CreditCardPayment = true; clsSalesTransactionsColumns.BalanceAmount = true; clsSalesTransactionsColumns.ChangeAmount = true; clsSalesTransactionsColumns.DateClosed = true; clsSalesTransactionsColumns.PaymentType = true; clsSalesTransactionsColumns.ItemsDiscount = true; clsSalesTransactionsColumns.Charge = true; clsSalesTransactionsColumns.CreditPayment = true; clsSalesTransactionsColumns.CreatedByName = true; clsSalesTransactionsColumns.AgentName = true; clsSalesTransactionsColumns.PaxNo = true; #endregion SalesTransactionDetails clsSearchKey = new SalesTransactionDetails(); clsSearchKey = new SalesTransactionDetails(); clsSearchKey.TransactionNo = TransactionNo; clsSearchKey.CustomerGroupName = CustomerGroupName; clsSearchKey.CustomerName = CustomerName; clsSearchKey.CashierName = CashierName; clsSearchKey.TerminalNo = TerminalNo; clsSearchKey.BranchID = int.Parse(cboBranch.SelectedItem.Value); clsSearchKey.TransactionDateFrom = StartTransactionDate; clsSearchKey.TransactionDateTo = EndTransactionDate; clsSearchKey.TransactionStatus = Status; clsSearchKey.PaymentType = PaymentType; clsSearchKey.AgentName = AgentName; clsSearchKey.isConsignmentSearch = cboConsignment.SelectedItem.Value; if (clsSearchKey.isConsignmentSearch != "-1") { clsSearchKey.isConsignment = bool.Parse(cboConsignment.SelectedItem.Value); } bool boWithTrustFund = true; string strReportType = cboReportType.SelectedValue; switch (strReportType) { case ReportTypes.SalesPerHour: #region Sales Per Hour clsSalesTransactions = new SalesTransactions(); dt = clsSalesTransactions.SalesPerHour(string.Empty, string.Empty, StartTransactionDate, EndTransactionDate,0, cboTerminalNo.SelectedItem.Text); clsSalesTransactions.CommitAndDispose(); foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.SalesPerHour.NewRow(); foreach (DataColumn dc in rptds.SalesPerHour.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.SalesPerHour.Rows.Add(drNew); } break; #endregion case ReportTypes.SalesPerDay: //case ReportTypes.SalesPerDayWithTF: #region Sales Per Day if (strReportType == ReportTypes.SalesPerDay) boWithTrustFund = false; clsSalesTransactions = new SalesTransactions(); dt = clsSalesTransactions.ListAsDataTable(clsSearchKey.BranchID, clsSearchKey.TerminalNo, TransactionStatus: clsSearchKey.TransactionStatus, TransactionDateFrom: StartTransactionDate, TransactionDateTo: EndTransactionDate, WithTF: boWithTrustFund); //dt = clsSalesTransactions.SalesPerDay(StartTransactionDate, EndTransactionDate, boWithTrustFund); clsSalesTransactions.CommitAndDispose(); foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.Transactions.NewRow(); foreach (DataColumn dc in rptds.Transactions.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.Transactions.Rows.Add(drNew); } break; #endregion case ReportTypes.SummarizeDailySales: //case ReportTypes.SummarizeDailySalesWithTF: #region SummarizeDailySales if (strReportType == ReportTypes.SummarizeDailySales) boWithTrustFund = false; Data.TerminalReportHistory clsTerminalReportHistory = new Data.TerminalReportHistory(); dt = clsTerminalReportHistory.SummarizedDailySalesReport(Int32.Parse(cboBranch.SelectedItem.Value), TerminalNo, false, StartTransactionDate, EndTransactionDate, boWithTrustFund); clsTerminalReportHistory.CommitAndDispose(); foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.SalesReport.NewRow(); foreach (DataColumn dc in rptds.SalesReport.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.SalesReport.Rows.Add(drNew); } break; #endregion case ReportTypes.SalesTransactions: #region Sales Transactions clsSalesTransactions = new SalesTransactions(); //dt = clsSalesTransactions.List(clsSalesTransactionsColumns, clsSearchKey, System.Data.SqlClient.SortOrder.Ascending, 0, "TransactionNo", System.Data.SqlClient.SortOrder.Ascending); dt = clsSalesTransactions.ListAsDataTable(clsSearchKey, false, "TransactionNo", SortOption.Ascending, 0); clsSalesTransactions.CommitAndDispose(); foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.Transactions.NewRow(); foreach (DataColumn dc in rptds.Transactions.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.Transactions.Rows.Add(drNew); } break; #endregion case ReportTypes.SalesTransactionPerCustomer: case ReportTypes.SalesTransactionPerCustomerPerGroup: case ReportTypes.SalesTransactionPerCustomerPerGroupSummarized: case ReportTypes.SalesTransactionPerCustomerWithCheque: case ReportTypes.SalesTransactionPerCashierPerCustomer: #region Sales Transaction Per Customer clsSalesTransactions = new SalesTransactions(); //dt = clsSalesTransactions.List(clsSalesTransactionsColumns, clsSearchKey, System.Data.SqlClient.SortOrder.Ascending, 0, "CustomerName", System.Data.SqlClient.SortOrder.Ascending); dt = clsSalesTransactions.ListAsDataTable(clsSearchKey, false, "CustomerName", SortOption.Ascending, 0); clsSalesTransactions.CommitAndDispose(); foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.Transactions.NewRow(); foreach (DataColumn dc in rptds.Transactions.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.Transactions.Rows.Add(drNew); } break; #endregion case ReportTypes.SalesTransactionPerCustomerPerItem: #region Sales Transaction Per Customer Per Item clsSalesTransactions = new SalesTransactions(); //dt = clsSalesTransactions.List(clsSalesTransactionsColumns, clsSearchKey, System.Data.SqlClient.SortOrder.Ascending, 0, "CustomerName", System.Data.SqlClient.SortOrder.Ascending); dt = clsSalesTransactions.ListAsDataTable(clsSearchKey, false, "CustomerName", SortOption.Ascending, 0); clsSalesTransactions.CommitAndDispose(); string stIDs = ""; foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.Transactions.NewRow(); foreach (DataColumn dc in rptds.Transactions.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.Transactions.Rows.Add(drNew); stIDs += "," + dr["TransactionID"].ToString(); } if (dt.Rows.Count > 0) { clsSalesTransactionItems = new SalesTransactionItems(); dt = clsSalesTransactionItems.List(stIDs.Remove(0,1)); foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.TransactionItems.NewRow(); foreach (DataColumn dc in rptds.TransactionItems.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.TransactionItems.Rows.Add(drNew); } } break; #endregion case ReportTypes.SalesTransactionPerCashier: #region Sales Transaction Per Cashier/Customer & Per Cashier clsSalesTransactions = new SalesTransactions(); //dt = clsSalesTransactions.List(clsSalesTransactionsColumns, clsSearchKey, System.Data.SqlClient.SortOrder.Ascending, 0, "CashierName", System.Data.SqlClient.SortOrder.Ascending); dt = clsSalesTransactions.ListAsDataTable(clsSearchKey, false, "CashierName", SortOption.Ascending, 0); clsSalesTransactions.CommitAndDispose(); foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.Transactions.NewRow(); foreach (DataColumn dc in rptds.Transactions.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.Transactions.Rows.Add(drNew); } break; #endregion case ReportTypes.DailySalesTransaction: case ReportTypes.WeeklySalesTransaction: case ReportTypes.MonthlySalesTransaction: #region Daily, Weekely, Monthly Sales Transaction clsSalesTransactions = new SalesTransactions(); //dt = clsSalesTransactions.List(clsSalesTransactionsColumns, clsSearchKey, System.Data.SqlClient.SortOrder.Ascending, 0, "TransactionDate", System.Data.SqlClient.SortOrder.Ascending); dt = clsSalesTransactions.ListAsDataTable(clsSearchKey, false, "TransactionDate", SortOption.Ascending, 0); clsSalesTransactions.CommitAndDispose(); foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.Transactions.NewRow(); foreach (DataColumn dc in rptds.Transactions.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.Transactions.Rows.Add(drNew); } break; #endregion case ReportTypes.SalesTransactionPerTerminal: #region Sales Transaction Per Terminal clsSalesTransactions = new SalesTransactions(); //dt = clsSalesTransactions.List(clsSalesTransactionsColumns, clsSearchKey, System.Data.SqlClient.SortOrder.Ascending, 0, "tblTransactions.TerminalNo", System.Data.SqlClient.SortOrder.Ascending); dt = clsSalesTransactions.ListAsDataTable(clsSearchKey, false, "TerminalNo", SortOption.Ascending, 0); clsSalesTransactions.CommitAndDispose(); foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.Transactions.NewRow(); foreach (DataColumn dc in rptds.Transactions.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.Transactions.Rows.Add(drNew); } break; #endregion case ReportTypes.SalesTransactionPerItem: case ReportTypes.SalesTransactionPerItemWoutPurchaseDetails: #region Sales Transaction Per Item SaleperItemFilterType enumSaleperItemFilterType = SaleperItemFilterType.ShowBothPositiveAndNegative; if (rdoShowPositiveOnly.Checked) enumSaleperItemFilterType = SaleperItemFilterType.ShowPositiveOnly; if (rdoShowNegativeOnly.Checked) enumSaleperItemFilterType = SaleperItemFilterType.ShowNegativeOnly; clsSalesTransactionItems = new SalesTransactionItems(); if (cboProductGroup.Text == Constants.ALL) dt = clsSalesTransactionItems.SalesPerItem(TransactionNo + "%", CustomerName + "%", CashierName + "%", TerminalNo + "%", StartTransactionDate, EndTransactionDate, Status, PaymentType, enumSaleperItemFilterType); else dt = clsSalesTransactionItems.SalesPerItemByGroup(strProductGroup + "%", TransactionNo + "%", CustomerName + "%", CashierName + "%", TerminalNo + "%", StartTransactionDate, EndTransactionDate, Status, PaymentType, enumSaleperItemFilterType); clsSalesTransactionItems.CommitAndDispose(); foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.SalesTransactionPerItem.NewRow(); foreach (DataColumn dc in rptds.SalesTransactionPerItem.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.SalesTransactionPerItem.Rows.Add(drNew); } break; #endregion case ReportTypes.CashSalesDaily: case ReportTypes.CashSalesMonthly: #region Cash-Sales Daily & Cash-Sales Monthly clsSalesTransactionsColumns = new SalesTransactionsColumns(); #region clsSalesTransactionsColumns clsSalesTransactionsColumns.TransactionDate = true; clsSalesTransactionsColumns.TransactionStatus = true; clsSalesTransactionsColumns.TransactionStatusName = true; clsSalesTransactionsColumns.CashPayment = true; #endregion clsSalesTransactions = new SalesTransactions(); dt = clsSalesTransactions.Cash_Cheque_CreditCard_Credit_Sales(clsSalesTransactionsColumns, clsSearchKey, System.Data.SqlClient.SortOrder.Ascending, 0, "tblTransactions.TerminalNo", System.Data.SqlClient.SortOrder.Ascending); clsSalesTransactions.CommitAndDispose(); foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.CashSales.NewRow(); foreach (DataColumn dc in rptds.CashSales.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.CashSales.Rows.Add(drNew); } break; #endregion case ReportTypes.ChequePaymentList: #region ChequePaymentList ChequePaymentDetails clsChequeSearchKeys = new ChequePaymentDetails(); clsChequeSearchKeys.TransactionNo = TransactionNo; clsChequeSearchKeys.CustomerGroupName = CustomerGroupName; clsChequeSearchKeys.CustomerName = CustomerName; clsChequeSearchKeys.CashierName = CashierName; clsChequeSearchKeys.TerminalNo = TerminalNo; clsChequeSearchKeys.BranchDetails.BranchID = int.Parse(cboBranch.SelectedItem.Value); clsChequeSearchKeys.TransactionDateFrom = StartTransactionDate; clsChequeSearchKeys.TransactionDateTo = EndTransactionDate; clsChequeSearchKeys.ValidityDateFrom = StartValidityDate; clsChequeSearchKeys.ValidityDateTo = StartValidityDate; clsChequeSearchKeys.TransactionStatus = Status; clsChequeSearchKeys.PaymentType = PaymentType; clsChequeSearchKeys.AgentName = AgentName; ChequePayments clsChequePayments = new ChequePayments(); dt = clsChequePayments.ListAsReport(clsChequeSearchKeys); clsChequePayments.CommitAndDispose(); foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.ChequePayments.NewRow(); foreach (DataColumn dc in rptds.ChequePayments.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.ChequePayments.Rows.Add(drNew); } break; #endregion case ReportTypes.ChequeSalesDaily: case ReportTypes.ChequeSalesMonthly: #region Cheque-Sales Daily & Cheque-Sales Monthly clsSalesTransactionsColumns = new SalesTransactionsColumns(); #region clsSalesTransactionsColumns clsSalesTransactionsColumns.TransactionDate = true; clsSalesTransactionsColumns.TransactionStatus = true; clsSalesTransactionsColumns.TransactionStatusName = true; clsSalesTransactionsColumns.ChequePayment = true; #endregion clsSalesTransactions = new SalesTransactions(); dt = clsSalesTransactions.Cash_Cheque_CreditCard_Credit_Sales(clsSalesTransactionsColumns, clsSearchKey, System.Data.SqlClient.SortOrder.Ascending, 0, "tblTransactions.TerminalNo", System.Data.SqlClient.SortOrder.Ascending); clsSalesTransactions.CommitAndDispose(); foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.ChequeSales.NewRow(); foreach (DataColumn dc in rptds.ChequeSales.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.ChequeSales.Rows.Add(drNew); } break; #endregion case ReportTypes.CreditCardSalesDaily: case ReportTypes.CreditCardSalesMonthly: #region Card-Sales Daily Card-Sales Monthly clsSalesTransactionsColumns = new SalesTransactionsColumns(); #region clsSalesTransactionsColumns clsSalesTransactionsColumns.TransactionDate = true; clsSalesTransactionsColumns.TransactionStatus = true; clsSalesTransactionsColumns.TransactionStatusName = true; clsSalesTransactionsColumns.CreditCardPayment = true; #endregion clsSalesTransactions = new SalesTransactions(); dt = clsSalesTransactions.Cash_Cheque_CreditCard_Credit_Sales(clsSalesTransactionsColumns, clsSearchKey, System.Data.SqlClient.SortOrder.Ascending, 0, "tblTransactions.TerminalNo", System.Data.SqlClient.SortOrder.Ascending); clsSalesTransactions.CommitAndDispose(); foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.CreditCardSales.NewRow(); foreach (DataColumn dc in rptds.CreditCardSales.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.CreditCardSales.Rows.Add(drNew); } break; #endregion case ReportTypes.PaidOut: #region PaidOut Report PaidOutColumns clsPaidOutColumns = new PaidOutColumns(); clsPaidOutColumns.BranchDetails = true; clsPaidOutColumns.TerminalNo = true; clsPaidOutColumns.Amount = true; clsPaidOutColumns.PaymentType = true; clsPaidOutColumns.DateCreated = true; clsPaidOutColumns.TerminalNo = true; clsPaidOutColumns.CashierID = true; clsPaidOutColumns.CashierName = true; clsPaidOutColumns.Remarks = true; PaidOutDetails clsPaidOutSeachKey = new PaidOutDetails(); clsPaidOutSeachKey.StartTransactionDate = StartTransactionDate; clsPaidOutSeachKey.EndTransactionDate = EndTransactionDate; PaidOut clsPaidOut = new PaidOut(); dt = clsPaidOut.ListAsDataTable(clsPaidOutColumns, clsPaidOutSeachKey); clsPaidOut.CommitAndDispose(); foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.PaidOut.NewRow(); foreach (DataColumn dc in rptds.PaidOut.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.PaidOut.Rows.Add(drNew); } break; #endregion case ReportTypes.Disburse: #region Disburse Report DisburseColumns clsDisburseColumns = new DisburseColumns(); clsDisburseColumns.BranchDetails = true; clsDisburseColumns.TerminalNo = true; clsDisburseColumns.Amount = true; clsDisburseColumns.PaymentType = true; clsDisburseColumns.DateCreated = true; clsDisburseColumns.TerminalNo = true; clsDisburseColumns.CashierID = true; clsDisburseColumns.CashierName = true; clsDisburseColumns.Remarks = true; DisburseDetails clsDisburseSeachKey = new DisburseDetails(); clsDisburseSeachKey.StartTransactionDate = StartTransactionDate; clsDisburseSeachKey.EndTransactionDate = EndTransactionDate; Disburses clsDisburse = new Disburses(); dt = clsDisburse.ListAsDataTable(clsDisburseColumns, clsDisburseSeachKey); clsDisburse.CommitAndDispose(); foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.Disburse.NewRow(); foreach (DataColumn dc in rptds.Disburse.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.Disburse.Rows.Add(drNew); } break; #endregion case ReportTypes.RecieveOnAccount: #region WithHold Report WithholdColumns clsWithHoldColumns = new WithholdColumns(); clsWithHoldColumns.BranchDetails = true; clsWithHoldColumns.TerminalNo = true; clsWithHoldColumns.Amount = true; clsWithHoldColumns.PaymentType = true; clsWithHoldColumns.DateCreated = true; clsWithHoldColumns.TerminalNo = true; clsWithHoldColumns.CashierID = true; clsWithHoldColumns.CashierName = true; clsWithHoldColumns.Remarks = true; WithholdDetails clsWithHoldSeachKey = new WithholdDetails(); clsWithHoldSeachKey.StartTransactionDate = StartTransactionDate; clsWithHoldSeachKey.EndTransactionDate = EndTransactionDate; Withhold clsWithHold = new Withhold(); dt = clsWithHold.ListAsDataTable(clsWithHoldColumns, clsWithHoldSeachKey); clsWithHold.CommitAndDispose(); foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.Withhold.NewRow(); foreach (DataColumn dc in rptds.Withhold.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.Withhold.Rows.Add(drNew); } break; #endregion default: break; } Report.SetDataSource(rptds); SetParameters(Report); }
private void SetDataSource(ReportDocument Report) { string strProductGroup = cboProductGroup.SelectedItem.Text == Constants.ALL ? string.Empty : cboProductGroup.SelectedItem.Text; string TransactionNo = txtTransactionNo.Text; string CustomerGroupName = cboContactGroupName.SelectedItem.Text.Substring(0, 3).Trim() == Constants.ALL ? cboContactGroupName.SelectedItem.Text.Replace("ALL", "").Replace("LIKE", "").Trim() : cboContactGroupName.SelectedItem.Text; string CustomerName = cboContactName.SelectedItem.Text.Substring(0, 3).Trim() == Constants.ALL ? cboContactName.SelectedItem.Text.Replace("ALL", "").Replace("LIKE","").Trim() : cboContactName.SelectedItem.Text; string AgentName = cboAgent.SelectedItem.Text.Substring(0, 3).Trim() == Constants.ALL ? cboAgent.SelectedItem.Text.Replace("ALL", "").Replace("LIKE", "").Trim() : cboAgent.SelectedItem.Text; string CashierName = cboCashierName.SelectedItem.Text.Substring(0, 3).Trim() == Constants.ALL ? cboCashierName.SelectedItem.Text.Replace("ALL", "").Replace("LIKE", "").Trim() : cboCashierName.SelectedItem.Text; string TerminalNo = cboTerminalNo.SelectedItem.Text == Constants.ALL ? string.Empty : cboTerminalNo.SelectedItem.Text; DateTime StartTransactionDate = DateTime.MinValue; StartTransactionDate = DateTime.TryParse(txtStartTransactionDate.Text + " " + txtStartTime.Text, out StartTransactionDate) ? StartTransactionDate : DateTime.MinValue; DateTime EndTransactionDate = DateTime.MinValue; EndTransactionDate = DateTime.TryParse(txtEndTransactionDate.Text + " " + txtEndTime.Text, out EndTransactionDate) ? EndTransactionDate : DateTime.MinValue; TransactionStatus Status = (TransactionStatus)Enum.Parse(typeof(TransactionStatus), cboTransactionStatus.SelectedItem.Value); PaymentTypes PaymentType = (PaymentTypes)Enum.Parse(typeof(PaymentTypes), cboPaymentType.SelectedItem.Value); DataTable dt = new DataTable(); ReportDataset rptds = new ReportDataset(); SalesTransactions clsSalesTransactions; SalesTransactionItems clsSalesTransactionItems; SalesTransactionsColumns clsSalesTransactionsColumns = new SalesTransactionsColumns(); #region clsSalesTransactionsColumns clsSalesTransactionsColumns.BranchCode = true; clsSalesTransactionsColumns.TransactionNo = true; clsSalesTransactionsColumns.CustomerName = true; clsSalesTransactionsColumns.CustomerGroupName = true; clsSalesTransactionsColumns.CashierName = true; clsSalesTransactionsColumns.TerminalNo = true; clsSalesTransactionsColumns.TransactionDate = true; clsSalesTransactionsColumns.DateSuspended = true; clsSalesTransactionsColumns.DateResumed = true; clsSalesTransactionsColumns.TransactionStatus = true; clsSalesTransactionsColumns.SubTotal = true; clsSalesTransactionsColumns.Discount = true; clsSalesTransactionsColumns.VAT = true; clsSalesTransactionsColumns.VATableAmount = true; clsSalesTransactionsColumns.LocalTax = true; clsSalesTransactionsColumns.AmountPaid = true; clsSalesTransactionsColumns.CashPayment = true; clsSalesTransactionsColumns.ChequePayment = true; clsSalesTransactionsColumns.CreditCardPayment = true; clsSalesTransactionsColumns.BalanceAmount = true; clsSalesTransactionsColumns.ChangeAmount = true; clsSalesTransactionsColumns.DateClosed = true; clsSalesTransactionsColumns.PaymentType = true; clsSalesTransactionsColumns.ItemsDiscount = true; clsSalesTransactionsColumns.Charge = true; clsSalesTransactionsColumns.CreditPayment = true; clsSalesTransactionsColumns.CreatedByName = true; clsSalesTransactionsColumns.AgentName = true; clsSalesTransactionsColumns.PaxNo = true; #endregion SalesTransactionDetails clsSearchKey = new SalesTransactionDetails(); clsSearchKey = new SalesTransactionDetails(); clsSearchKey.TransactionNo = TransactionNo; clsSearchKey.CustomerGroupName = CustomerGroupName; clsSearchKey.CustomerName = CustomerName; clsSearchKey.CashierName = CashierName; clsSearchKey.TerminalNo = TerminalNo; clsSearchKey.BranchID = int.Parse(cboBranch.SelectedItem.Value); clsSearchKey.TransactionDateFrom = StartTransactionDate; clsSearchKey.TransactionDateTo = EndTransactionDate; clsSearchKey.TransactionStatus = Status; clsSearchKey.PaymentType = PaymentType; clsSearchKey.AgentName = AgentName; clsSearchKey.isConsignmentSearch = cboConsignment.SelectedItem.Value; if (clsSearchKey.isConsignmentSearch != "-1") { clsSearchKey.isConsignment = bool.Parse(cboConsignment.SelectedItem.Value); } string strReportType = cboReportType.SelectedValue; switch (strReportType) { case ReportTypes.ANALYTICS_All: case ReportTypes.ANALYTICS_All_Covers: #region Daily, Weekely, Monthly Sales Transaction clsSalesTransactions = new SalesTransactions(); dt = clsSalesTransactions.List(clsSalesTransactionsColumns, clsSearchKey, System.Data.SqlClient.SortOrder.Ascending, 0, "TransactionDate", System.Data.SqlClient.SortOrder.Ascending); clsSalesTransactions.CommitAndDispose(); foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.Transactions.NewRow(); foreach (DataColumn dc in rptds.Transactions.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.Transactions.Rows.Add(drNew); } break; #endregion case ReportTypes.ANALYTICS_ItemsForPOBasedOnSales: #region Items For PO Based On Sales SaleperItemFilterType enumSaleperItemFilterType = SaleperItemFilterType.ShowBothPositiveAndNegative; if (rdoShowPositiveOnly.Checked) enumSaleperItemFilterType = SaleperItemFilterType.ShowPositiveOnly; if (rdoShowNegativeOnly.Checked) enumSaleperItemFilterType = SaleperItemFilterType.ShowNegativeOnly; clsSalesTransactionItems = new SalesTransactionItems(); //if (cboProductGroup.Text == Constants.ALL) // dt = clsSalesTransactionItems.SalesPerItem(TransactionNo + "%", CustomerName + "%", CashierName + "%", // TerminalNo + "%", StartTransactionDate, EndTransactionDate, Status, PaymentType, enumSaleperItemFilterType); //else // dt = clsSalesTransactionItems.SalesPerItemByGroup(strProductGroup + "%", TransactionNo + "%", CustomerName + "%", CashierName + "%", // TerminalNo + "%", StartTransactionDate, EndTransactionDate, Status, PaymentType, enumSaleperItemFilterType); if (cboProductGroup.Text == Constants.ALL) dt = clsSalesTransactionItems.SalesPerItem(TransactionNo, CustomerName, CashierName, TerminalNo, StartTransactionDate, EndTransactionDate, Status, PaymentType, enumSaleperItemFilterType); else dt = clsSalesTransactionItems.SalesPerItemByGroupProc(strProductGroup, TransactionNo, CustomerName, CashierName, TerminalNo, StartTransactionDate, EndTransactionDate, Status, PaymentType, enumSaleperItemFilterType); clsSalesTransactionItems.CommitAndDispose(); foreach (DataRow dr in dt.Rows) { DataRow drNew = rptds.SalesTransactionPerItem.NewRow(); foreach (DataColumn dc in rptds.SalesTransactionPerItem.Columns) drNew[dc] = dr[dc.ColumnName]; rptds.SalesTransactionPerItem.Rows.Add(drNew); } break; #endregion default: break; } Report.SetDataSource(rptds); SetParameters(Report); }
private void cmdTable_Click(object sender, EventArgs e) { try { ProductButton cmdTable = (ProductButton)sender; Data.Contacts clsContact = new Contacts(); mDetails = clsContact.Details(long.Parse(cmdTable.Tag.ToString())); Data.SalesTransactions clsSalesTransactions = new Data.SalesTransactions(clsContact.Connection, clsContact.Transaction); string stTransactionNo = clsSalesTransactions.getSuspendedTransactionNo(mDetails.ContactID, mclsTerminalDetails.TerminalNo, mclsTerminalDetails.BranchID); Data.SalesTransactionDetails clsSalesTransactionDetails = new SalesTransactionDetails(); if (!string.IsNullOrEmpty(stTransactionNo)) { clsSalesTransactionDetails = clsSalesTransactions.Details(stTransactionNo, mclsTerminalDetails.TerminalNo, mclsTerminalDetails.BranchID); } clsContact.CommitAndDispose(); if (!string.IsNullOrEmpty(stTransactionNo) && clsSalesTransactionDetails.TransactionStatus == TransactionStatus.SuspendedOpen) { if (MessageBox.Show("This transaction is already open in another terminal. Please suspend in the other terminal first before opening." + Environment.NewLine + "Would you like to force open this transaction?", "RetailPlus", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.No) { //LoadContactData(System.Data.SqlClient.SortOrder.Ascending); return; } else { DialogResult resResumeSuspendedOpenTransaction = GetWriteAccessAndLogin(CashierID, AccessTypes.ResumeSuspendedOpenTransaction); if (resResumeSuspendedOpenTransaction != System.Windows.Forms.DialogResult.OK) { //LoadContactData(System.Data.SqlClient.SortOrder.Ascending); return; } } } dialog = DialogResult.OK; this.Hide(); } catch { } }