public new IEntity GetById(IEntity en) { try { OpenConnection(); GeneralSetup e = (GeneralSetup)en; MySql.Data.MySqlClient.MySqlCommand aCommand = new MySql.Data.MySqlClient.MySqlCommand(e.GetByIDSQL(e.GetID()), m_connection); MySql.Data.MySqlClient.MySqlDataReader aReader = aCommand.ExecuteReader(); GeneralSetup a = (GeneralSetup)e.Get(aReader); aReader.Close(); a.START_ENTRY_PERIOD = PeriodRepository.FindPeriod(aCommand, a.START_ENTRY_PERIOD.ID); aCommand.CommandText = AutoNumberSetup.GetAllSQLStatic(); aReader = aCommand.ExecuteReader(); IList lst = AutoNumberSetup.GetAllStatic(aReader); aReader.Close(); foreach (AutoNumberSetup s in lst) { a.AUTONUMBER_LIST.Add(s.FORM_CODE, s); } return(a); } catch (Exception x) { throw new Exception(getErrorMessage(x)); } finally { m_connection.Close(); } }
protected Period AssertValidPeriod(DateTime transactionDate) { Period transactionPeriod = PeriodRepository.FindPeriodByDate(m_command, transactionDate); if (transactionPeriod == null) { throw new Exception("Period not set"); } if (transactionPeriod.PERIOD_STATUS.Equals(PeriodStatus.Close) || transactionPeriod.PERIOD_STATUS.Equals(PeriodStatus.Open)) { throw new Exception("Transaction is not in Current Period"); } return(transactionPeriod); }
public static VendorBalance FindVendorBalanceHeader(MySql.Data.MySqlClient.MySqlCommand cmd, long vendor, long currency, long periodId, VendorBalanceType type) { cmd.CommandText = String.Format("select * from table_vendorbalance where vendor_id = {0} and ccy_id = {1} and period_id = {2} and vb_vendorbalancetype = '{3}'", vendor, currency, periodId, type.ToString()); MySql.Data.MySqlClient.MySqlDataReader r = cmd.ExecuteReader(); VendorBalance sc = VendorBalance.TransformReader(r); r.Close(); if (sc != null) { sc.PERIOD = PeriodRepository.FindPeriod(cmd, sc.PERIOD.ID); } return(sc); }
public static StockCard FindStockCard(MySql.Data.MySqlClient.MySqlCommand cmd, long partId, long locationId, long periodId) { cmd.CommandText = String.Format("select * from table_stockcard where part_id = {0} and warehouse_id = {1} and period_id = {2}", partId, locationId, periodId); MySql.Data.MySqlClient.MySqlDataReader r = cmd.ExecuteReader(); StockCard sc = StockCard.TransformReader(r); r.Close(); if (sc != null) { sc.PERIOD = PeriodRepository.FindPeriod(cmd, sc.PERIOD.ID); cmd.CommandText = StockCardEntry.FindByStockCard(sc.ID); MySql.Data.MySqlClient.MySqlDataReader rx = cmd.ExecuteReader(); sc.STOCK_CARD_ENTRIES = StockCardEntry.TransformReaderList(rx); rx.Close(); } return(sc); }
public static Period FindNextPeriod(MySql.Data.MySqlClient.MySqlCommand cmd) { Period crnt = PeriodRepository.FindCurrentPeriod(cmd); cmd.CommandText = "select * from table_period"; MySql.Data.MySqlClient.MySqlDataReader r = cmd.ExecuteReader(); IList periods = Period.TransformReaderList(r); r.Close(); int crntdx = periods.IndexOf(crnt); int next = crntdx + 1; if (next > periods.Count) { return(null); } return(periods[next] as Period); }
private Period GetNextPeriod(Period crnt) { if (crnt == null) { throw new Exception("Period Not Found"); } IList periods = PeriodRepository.LoadAll(m_command); ArrayList periodss = (ArrayList)periods; periodss.Sort(new PeriodComparer()); int crntdx = periods.IndexOf(crnt); int next = crntdx - 1; if (next > periods.Count) { return(null); } return(periods[next] as Period); }
public IList GetStockCardInfoList(int partID) { OpenConnection(); MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(); cmd.Connection = m_connection; Period p = PeriodRepository.FindCurrentPeriod(cmd); cmd.CommandText = StockCard.FindByPartPeriod(partID, p.ID); MySql.Data.MySqlClient.MySqlDataReader r = cmd.ExecuteReader(); IList stockcards = StockCard.TransforReaderList(r); r.Close(); IList stockInfoList = new ArrayList(); foreach (StockCard sc in stockcards) { StockCardInfo sci = new StockCardInfo(sc.BALANCE, sc.BOOKED, sc.BACK_ORDER, sc.WAREHOUSE); sci.WAREHOUSE = StockCardRepository.FindWarehouse(cmd, sc.WAREHOUSE.ID); stockInfoList.Add(sci); } return(stockInfoList); }
public StockCardInfo GetStockCardInfo(int partID) { OpenConnection(); StockCardInfo result = new StockCardInfo(); MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(); cmd.Connection = m_connection; Period p = PeriodRepository.FindCurrentPeriod(cmd); cmd.CommandText = StockCard.FindByPartPeriod(partID, p.ID); MySql.Data.MySqlClient.MySqlDataReader r = cmd.ExecuteReader(); IList stockcards = StockCard.TransforReaderList(r); r.Close(); foreach (StockCard sc in stockcards) { result.BACKORDER += sc.BACK_ORDER; result.BALANCE += sc.BALANCE; result.BOOKED += sc.BOOKED; } return(result); }
public Period GetPrevPeriod(Period crnt) { if (crnt == null) { throw new Exception("Period Not Found"); } IList periods = PeriodRepository.LoadAll(m_command); ArrayList periodss = (ArrayList)periods; periodss.Sort(new PeriodComparer()); int crntdx = periods.IndexOf(crnt); int prev = crntdx + 1; if (prev < 0) { return(null); } if (prev > (periodss.Count - 1)) { return(null); } return(periods[prev] as Period); }
internal static GeneralSetup GetGeneralSetup(MySql.Data.MySqlClient.MySqlCommand cmd) { GeneralSetup e = new GeneralSetup(); cmd.CommandText = GeneralSetup.GetAllSQLStatic(); MySql.Data.MySqlClient.MySqlDataReader aReader = cmd.ExecuteReader(); GeneralSetup a = (GeneralSetup)e.Get(aReader); aReader.Close(); cmd.CommandText = AutoNumberSetup.GetAllSQLStatic(); aReader = cmd.ExecuteReader(); IList lst = AutoNumberSetup.GetAllStatic(aReader); aReader.Close(); foreach (AutoNumberSetup s in lst) { a.AUTONUMBER_LIST.Add(s.FORM_CODE, s); } a.START_ENTRY_PERIOD = PeriodRepository.FindPeriod(cmd, a.START_ENTRY_PERIOD.ID); return(a); }
public static StockCard FindStockCardHeader(MySql.Data.MySqlClient.MySqlCommand cmd, long partId, long locationId, Period periodId, DateTime trDate) { cmd.CommandText = String.Format("select * from table_stockcard where part_id = {0} and warehouse_id = {1} and period_id = {2}", partId, locationId, periodId.ID); MySql.Data.MySqlClient.MySqlDataReader r = cmd.ExecuteReader(); StockCard sc = StockCard.TransformReader(r); r.Close(); if (sc != null) { sc.PERIOD = PeriodRepository.FindPeriod(cmd, sc.PERIOD.ID); sc.WAREHOUSE = StockCardRepository.FindWarehouse(cmd, sc.WAREHOUSE.ID); cmd.CommandText = StockCardEntry.FindByStockCard(sc.ID, periodId.START_DATE, trDate); MySql.Data.MySqlClient.MySqlDataReader rx = cmd.ExecuteReader(); sc.STOCK_CARD_ENTRIES = StockCardEntry.TransformReaderList(rx); rx.Close(); foreach (StockCardEntry e in sc.STOCK_CARD_ENTRIES) { if (e.STOCK_CARD_ENTRY_TYPE == StockCardEntryType.SupplierInvoice) { cmd.CommandText = SupplierInvoiceItem.GetByIDSQL(e.EVENT_ITEM.ID); rx = cmd.ExecuteReader(); e.EVENT_ITEM = SupplierInvoiceItem.TransformReader(rx); rx.Close(); } if (e.STOCK_CARD_ENTRY_TYPE == StockCardEntryType.CustomerInvoice) { cmd.CommandText = CustomerInvoiceItem.GetByIDSQL(e.EVENT_ITEM.ID); rx = cmd.ExecuteReader(); e.EVENT_ITEM = CustomerInvoiceItem.TransformReader(rx); rx.Close(); } } } return(sc); }
public override IList GetAll() { try { OpenConnection(); MySql.Data.MySqlClient.MySqlCommand aCommand = new MySql.Data.MySqlClient.MySqlCommand(m_entity.GetAllSQL(), m_connection); MySql.Data.MySqlClient.MySqlDataReader aReader = aCommand.ExecuteReader(); IList a = m_entity.GetAll(aReader); aReader.Close(); foreach (GeneralSetup s in a) { s.START_ENTRY_PERIOD = PeriodRepository.FindPeriod(aCommand, s.START_ENTRY_PERIOD.ID); } return(a); } catch (Exception x) { throw new Exception(getErrorMessage(x)); } finally { m_connection.Close(); } }
public void RollBackTransaction(int currentPeriodId) { OpenConnection(); MySql.Data.MySqlClient.MySqlTransaction trc = m_connection.BeginTransaction(); try { m_command.Transaction = trc; Period crntPeriod = PeriodRepository.FindPeriod(m_command, currentPeriodId) as Period; if (crntPeriod == null) { throw new Exception("Current Period Not Found!"); } if (crntPeriod.PERIOD_STATUS != PeriodStatus.Current) { throw new Exception("Period is not in Active Month!"); } GeneralSetup gs = GeneralSetupRepository.GetGeneralSetup(m_command); if (gs.START_ENTRY_PERIOD == null) { throw new Exception("Start Entry Month Not Found!"); } OpeningStock p = r_openingStock.GetOpeningStockByNotes("AUTO" + crntPeriod.START_DATE.ToString(Utils.DATE_FORMAT_SHORT)); if (p == null) { throw new Exception("Opening Stock Closing is missing"); } IList invTrs = GetAllCodeListOfPostedEvent(crntPeriod.START_DATE, crntPeriod.END_DATA, p.ID); //this.GetAllCodeListOfNotPostedEvent(crntPeriod.START_DATE, crntPeriod.END_DATA); string invCodes = string.Empty; if (invTrs.Count > 0) { foreach (string code in invTrs) { invCodes += code + "\r\n"; } } if (invTrs.Count > 0) { throw new Exception("Please Unpost Transaction : \r\n" + invCodes); } Period prevPeriod = this.GetPrevPeriod(crntPeriod) as Period; if (prevPeriod == null) { throw new Exception("Previous Period Not Define!"); } r_openingStock.ReviseNoTransaction(p.ID, m_command); r_openingStock.DeleteNoTransaction(p, m_command); IList stockcards = StockCardRepository.FindStockCardByPeriod(m_command, crntPeriod.ID); IList vbalances = VendorBalanceRepository.FindVendorBalanceByPeriod(m_command, crntPeriod.ID); foreach (StockCard sc in stockcards) { StockCardRepository.DeleteHeader(m_command, sc); } foreach (VendorBalance sc in vbalances) { VendorBalanceRepository.DeleteHeader(m_command, sc); } prevPeriod.PERIOD_STATUS = PeriodStatus.Current; PeriodRepository.UpdatePeriod(m_command, prevPeriod); crntPeriod.PERIOD_STATUS = PeriodStatus.Open; PeriodRepository.UpdatePeriod(m_command, crntPeriod); trc.Commit(); } catch (Exception x) { trc.Rollback(); throw x; } }
public void ProcessTransaction(int currentPeriodId, Employee emp) { OpenConnection(); MySql.Data.MySqlClient.MySqlTransaction trc = m_connection.BeginTransaction(); try { m_command.Transaction = trc; Period crntPeriod = PeriodRepository.FindPeriod(m_command, currentPeriodId) as Period; if (crntPeriod == null) { throw new Exception("Current Period Not Found!"); } if (crntPeriod.PERIOD_STATUS != PeriodStatus.Current) { throw new Exception("Period is not in Active Month!"); } GeneralSetup gs = GeneralSetupRepository.GetGeneralSetup(m_command); if (gs.START_ENTRY_PERIOD == null) { throw new Exception("Start Entry Month Not Found!"); } IList invTrs = this.GetAllCodeListOfNotPostedEvent(crntPeriod.START_DATE, crntPeriod.END_DATA); string invCodes = string.Empty; if (invTrs.Count > 0) { foreach (string code in invTrs) { invCodes += code + "\r\n"; } } if (invTrs.Count > 0) { throw new Exception("Please Post Transaction : \r\n" + invCodes); } Period nextPeriod = this.GetNextPeriod(crntPeriod) as Period; if (nextPeriod == null) { throw new Exception("Next Period Not Define!"); } IList stockcards = StockCardRepository.FindStockCardByPeriod(m_command, crntPeriod.ID); nextPeriod.PERIOD_STATUS = PeriodStatus.Current; nextPeriod.CLOSED_DATE = DateTime.Now; PeriodRepository.UpdatePeriod(m_command, nextPeriod); crntPeriod.PERIOD_STATUS = PeriodStatus.Close; crntPeriod.CLOSED_DATE = DateTime.Now; PeriodRepository.UpdatePeriod(m_command, crntPeriod); IList newSCards = new ArrayList(); OpeningStock ops = new OpeningStock(); ops.TRANSACTION_DATE = nextPeriod.START_DATE; ops.CURRENCY = CurrencyRepository.GetBaseCurrency(m_command); ops.DOCUMENT_DATE = DateTime.Today; ops.EMPLOYEE = emp; ops.MODIFIED_BY = emp.NAME; ops.MODIFIED_DATE = DateTime.Today; ops.NOTES = "AUTO" + nextPeriod.START_DATE.ToString(Utils.DATE_FORMAT_SHORT); ops.NOTICE_DATE = DateTime.Today; ops.WAREHOUSE = getCommonStore(); double total = 0; for (int i = 0; i < stockcards.Count; i++) { StockCard sCard = stockcards[i] as StockCard; if (sCard.BALANCE == 0) { continue; } OpeningStockItem opi = new OpeningStockItem(); opi.EVENT = ops; opi.PART = sCard.PART; opi.PRICE = PartRepository.GetLatestPriceMovementItemPeriod(m_command, sCard.PART.ID, crntPeriod.START_DATE, crntPeriod.END_DATA); opi.QYTAMOUNT = sCard.BALANCE; opi.TOTAL_AMOUNT = opi.PRICE * sCard.BALANCE; Part p = PartRepository.GetByID(m_command, sCard.PART.ID); opi.UNIT = p.UNIT; opi.WAREHOUSE = sCard.WAREHOUSE; ops.EVENT_ITEMS.Add(opi); total += opi.TOTAL_AMOUNT; // newSCards.Add(sCard.Create(nextPeriod)); } ops.AMOUNT = total; r_openingStock.SaveNoTransaction(ops, m_command); r_openingStock.ConfirmNoTransaction(ops.ID, m_command); //foreach (StockCard sc in newSCards) //{ // StockCardRepository.SaveHeader(m_command, sc); // } IList vbalances = VendorBalanceRepository.FindVendorBalanceByPeriod(m_command, crntPeriod.ID); IList newvbalances = new ArrayList(); for (int i = 0; i < vbalances.Count; i++) { VendorBalance vb = vbalances[i] as VendorBalance; newvbalances.Add(vb.Create(nextPeriod)); //if (vb.VENDOR_BALANCE_TYPE == VendorBalanceType.Customer) //{ // CustomerOutStandingInvoice cosi = new CustomerOutStandingInvoice(); // cosi.CURRENCY = vb.CURRENCY; // cosi.TRANSACTION_DATE = nextPeriod.START_DATE; // cosi.EMPLOYEE = emp; // cosi.MODIFIED_BY = emp.NAME; // cosi.MODIFIED_COMPUTER_NAME = Environment.MachineName; // cosi.MODIFIED_DATE = DateTime.Now; // cosi.NOTES = "AUTO"; // cosi.NOTICE_DATE = DateTime.Now; // cosi.VENDOR = vb.VENDOR; // CustomerOutStandingInvoiceItem cosii = new CustomerOutStandingInvoiceItem(); // cosii.EVENT_JOURNAL = cosi; // cosii.INVOICE_NO = "AUTO_OPENING_BALANCE"; // cosii.INVOICE_DATE = DateTime.Today; // cosii.TOP = getCommonTOP(); // cosii.DUE_DATE = DateTime.Today; // cosii.EMPLOYEE = emp; // cosii.AMOUNT = vb.BALANCE; // cosii. //} } foreach (VendorBalance sc in newvbalances) { VendorBalanceRepository.SaveHeader(m_command, sc); } trc.Commit(); } catch (Exception x) { trc.Rollback(); throw x; } }