protected override void doUpdate(Event en) { MySql.Data.MySqlClient.MySqlTransaction trc = m_connection.BeginTransaction(); m_command.Transaction = trc; try { OpeningStock e = (OpeningStock)en; m_command.CommandText = e.GetUpdateSQL(); m_command.ExecuteNonQuery(); foreach (OpeningStockItem sti in e.EVENT_ITEMS) { if (sti.ID > 0) { m_command.CommandText = sti.GetUpdateSQL(); m_command.ExecuteNonQuery(); } else { m_command.CommandText = sti.GetInsertSQL(); m_command.ExecuteNonQuery(); m_command.CommandText = OpeningStockItem.SelectMaxIDSQL(); sti.ID = Convert.ToInt32(m_command.ExecuteScalar()); } } m_command.CommandText = OpeningStockItem.DeleteUpdate(e.ID, e.EVENT_ITEMS); m_command.ExecuteNonQuery(); trc.Commit(); } catch (Exception x) { trc.Rollback(); throw x; } }
public IList GetSupplierInvoiceItem(int partID) { MySql.Data.MySqlClient.MySqlDataReader rdr; Part p = PartRepository.GetByID(m_command, partID); ArrayList result = new ArrayList(); m_command.CommandText = SupplierInvoiceItem.GetByPartIDOrderByDateSQL(partID); rdr = m_command.ExecuteReader(); IList piis = SupplierInvoiceItem.TransformReaderList(rdr); rdr.Close(); foreach (SupplierInvoiceItem itm in piis) { itm.EVENT = SupplierInvoiceRepository.GetHeaderOnly(m_command, itm.EVENT.ID); itm.PART = p; result.Add(itm); } m_command.CommandText = StockTakingItems.GetByPartIDOrderByDateSQL(partID); rdr = m_command.ExecuteReader(); IList sti = StockTakingItems.TransformReaderList(rdr); rdr.Close(); foreach (StockTakingItems itm in sti) { itm.EVENT = StockTakingRepository.GetHeaderOnly(m_command, itm.EVENT.ID); itm.PART = p; result.Add(itm); } m_command.CommandText = OpeningStockItem.GetByPartIDOrderByDateSQL(partID); rdr = m_command.ExecuteReader(); IList opi = OpeningStockItem.TransformReaderList(rdr); rdr.Close(); foreach (OpeningStockItem itm in opi) { itm.EVENT = OpeningStockRepository.GetHeaderOnly(m_command, itm.EVENT.ID); itm.PART = p; result.Add(itm); } result.Sort(new Profit.Server.PartRepository.EventDateComparer()); return(result); }
public OpeningStock GetOpeningStockByNotes(string note) { m_command.CommandText = OpeningStock.GetByNotesSQL(note); MySql.Data.MySqlClient.MySqlDataReader r = m_command.ExecuteReader(); OpeningStock st = OpeningStock.TransformReader(r); r.Close(); m_command.CommandText = OpeningStockItem.GetByEventIDSQL(st.ID); r = m_command.ExecuteReader(); IList stis = OpeningStockItem.TransformReaderList(r); r.Close(); foreach (OpeningStockItem sti in stis) { sti.EVENT = st; sti.PART = PartRepository.GetByID(m_command, sti.PART.ID); sti.STOCK_CARD_ENTRY = StockCardEntryRepository.FindStockCardEntryByEventItem(m_command, sti.ID, sti.STOCK_CARD_ENTRY_TYPE); st.EVENT_ITEMS.Add(sti); } return(st); }
public void DeleteNoTransaction(Event e, MySql.Data.MySqlClient.MySqlCommand command) { OpeningStock st = (OpeningStock)e;//this.Get(e.ID); m_command = command; try { if (getEventStatus(st.ID) == EventStatus.Confirm) { throw new Exception("Revise before delete"); } m_command.CommandText = OpeningStockItem.DeleteAllByEventSQL(st.ID); m_command.ExecuteNonQuery(); m_command.CommandText = OpeningStock.DeleteSQL(st.ID); m_command.ExecuteNonQuery(); } catch (Exception x) { throw x; } }
public static IList TransformReaderList(MySql.Data.MySqlClient.MySqlDataReader aReader) { IList result = new ArrayList(); while (aReader.Read()) { OpeningStockItem transaction = new OpeningStockItem(); transaction.ID = Convert.ToInt32(aReader["opsti_id"]); transaction.EVENT = new OpeningStock(Convert.ToInt32(aReader["opst_id"])); transaction.PART = new Part(Convert.ToInt32(aReader["part_id"])); transaction.WAREHOUSE = new Warehouse(Convert.ToInt32(aReader["warehouse_id"])); transaction.QYTAMOUNT = Convert.ToDouble(Convert.ToInt32(aReader["opsti_amount"])); transaction.STOCK_CARD_ENTRY = new StockCardEntry(Convert.ToInt32(aReader["sce_id"])); transaction.STOCK_CARD_ENTRY_TYPE = (StockCardEntryType)Enum.Parse(typeof(StockCardEntryType), aReader["opst_scentrytype"].ToString()); transaction.STOCK_CARD = new StockCard(Convert.ToInt32(aReader["sc_id"])); transaction.UNIT = new Unit(Convert.ToInt32(aReader["unit_id"]));// transaction.PRICE = Convert.ToDouble(Convert.ToInt32(aReader["opsti_price"])); transaction.TOTAL_AMOUNT = Convert.ToDouble(Convert.ToInt32(aReader["opsti_totalamount"])); result.Add(transaction); } return result; }
protected override void doSave(Event e) { MySql.Data.MySqlClient.MySqlTransaction trc = m_connection.BeginTransaction(); try { m_command.Transaction = trc; DateTime trDate = DateTime.Today; string codesample = AutoNumberSetupRepository.GetCodeSampleByDomainName(m_command, "OpeningStock"); Event codeDate = FindLastCodeAndTransactionDate(codesample); string lastCode = codeDate == null ? string.Empty : codeDate.CODE; DateTime lastDate = codeDate == null ? trDate : codeDate.TRANSACTION_DATE; int trCount = RecordCount(); e.CODE = AutoNumberSetupRepository.GetAutoNumberByDomainName(m_command, "OpeningStock", e.CODE, lastCode, lastDate, trDate, trCount == 0); OpeningStock stk = (OpeningStock)e; m_command.CommandText = e.GetInsertSQL(); m_command.ExecuteNonQuery(); m_command.CommandText = OpeningStock.SelectMaxIDSQL(); stk.ID = Convert.ToInt32(m_command.ExecuteScalar()); foreach (OpeningStockItem item in stk.EVENT_ITEMS) { m_command.CommandText = item.GetInsertSQL(); m_command.ExecuteNonQuery(); m_command.CommandText = OpeningStockItem.SelectMaxIDSQL(); item.ID = Convert.ToInt32(m_command.ExecuteScalar()); } trc.Commit(); } catch (Exception x) { e.ID = 0; foreach (EventItem item in e.EVENT_ITEMS) { item.ID = 0; } trc.Rollback(); throw x; } }
protected override void doDelete(Event e) { OpeningStock st = (OpeningStock)e;//this.Get(e.ID); MySql.Data.MySqlClient.MySqlTransaction trc = m_connection.BeginTransaction(); m_command.Transaction = trc; try { if (getEventStatus(st.ID) == EventStatus.Confirm) { throw new Exception("Revise before delete"); } m_command.CommandText = OpeningStockItem.DeleteAllByEventSQL(st.ID); m_command.ExecuteNonQuery(); m_command.CommandText = OpeningStock.DeleteSQL(st.ID); m_command.ExecuteNonQuery(); trc.Commit(); } catch (Exception x) { trc.Rollback(); throw x; } }
public static double GetLatestPriceMovementItemPeriod(MySql.Data.MySqlClient.MySqlCommand m_command, int partID, DateTime start, DateTime end) { MySql.Data.MySqlClient.MySqlDataReader rdr; Part p = PartRepository.GetByID(m_command, partID); ArrayList result = new ArrayList(); m_command.CommandText = SupplierInvoiceItem.GetByPartIDOrderByDateRangeSQL(partID, start, end); rdr = m_command.ExecuteReader(); IList piis = SupplierInvoiceItem.TransformReaderList(rdr); rdr.Close(); foreach (SupplierInvoiceItem itm in piis) { itm.EVENT = SupplierInvoiceRepository.GetHeaderOnly(m_command, itm.EVENT.ID); itm.PART = p; result.Add(itm); } m_command.CommandText = StockTakingItems.GetByPartIDOrderByDateSQL(partID); rdr = m_command.ExecuteReader(); IList sti = StockTakingItems.TransformReaderList(rdr); rdr.Close(); foreach (StockTakingItems itm in sti) { itm.EVENT = StockTakingRepository.GetHeaderOnly(m_command, itm.EVENT.ID); itm.PART = p; result.Add(itm); } m_command.CommandText = OpeningStockItem.GetByPartIDOrderByDateSQL(partID); rdr = m_command.ExecuteReader(); IList opi = OpeningStockItem.TransformReaderList(rdr); rdr.Close(); foreach (OpeningStockItem itm in opi) { itm.EVENT = OpeningStockRepository.GetHeaderOnly(m_command, itm.EVENT.ID); itm.PART = p; result.Add(itm); } result.Sort(new Profit.Server.PartRepository.EventDateComparer()); double average = 0; double price = 0; double priceBaseCcy = 0; Currency baseccy = CurrencyRepository.GetBaseCurrency(m_command); for (int i = 0; i < result.Count; i++) { if (result[i] is OpeningStockItem) { price = ((OpeningStockItem)result[i]).TOTAL_AMOUNT / ((OpeningStockItem)result[i]).GetAmountInSmallestUnit(); if (baseccy.ID == ((OpeningStock)((OpeningStockItem)result[i]).EVENT).CURRENCY.ID) { priceBaseCcy = price; } else { priceBaseCcy = CurrencyRepository.ConvertToBaseCurrency(m_command, ((OpeningStock)((OpeningStockItem)result[i]).EVENT).CURRENCY, price, DateTime.Today); } } if (result[i] is StockTakingItems) { price = ((StockTakingItems)result[i]).TOTAL_AMOUNT / ((StockTakingItems)result[i]).GetAmountInSmallestUnit(); if (baseccy.ID == ((StockTaking)((StockTakingItems)result[i]).EVENT).CURRENCY.ID) { priceBaseCcy = price; } else { priceBaseCcy = CurrencyRepository.ConvertToBaseCurrency(m_command, ((StockTaking)((StockTakingItems)result[i]).EVENT).CURRENCY, price, DateTime.Today); } } if (result[i] is SupplierInvoiceItem) { price = ((SupplierInvoiceItem)result[i]).SUBTOTAL / ((SupplierInvoiceItem)result[i]).GetAmountInSmallestUnit(); if (baseccy.ID == ((SupplierInvoice)((SupplierInvoiceItem)result[i]).EVENT).CURRENCY.ID) { priceBaseCcy = price; } else { priceBaseCcy = CurrencyRepository.ConvertToBaseCurrency(m_command, ((SupplierInvoice)((SupplierInvoiceItem)result[i]).EVENT).CURRENCY, price, DateTime.Today); } } if (i == 0) { average = priceBaseCcy; } else { average = (average + priceBaseCcy) / 2; } } return(average < 0 ? -average : average); }
//------------- //public void UpdatePart() //{ // MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(); // cmd.Connection = m_connection; // m_connection.Open(); // cmd.CommandText = m_entity.GetAllSQL(); // MySql.Data.MySqlClient.MySqlDataReader r = cmd.ExecuteReader(); // IList result = new ArrayList(); // while (r.Read()) // { // PartUnit pu = new PartUnit(); // pu.PART = Convert.ToInt32(r["part_id"]); // pu.UNIT = Convert.ToInt32(r["unit_id"]); // result.Add(pu); // } // r.Close(); // foreach (PartUnit e in result) // { // cmd.CommandText = UnitConversion.GetByPartAndUnitConIDSQL(e.PART, e.UNIT); // r = cmd.ExecuteReader(); // UnitConversion uc = UnitConversion.GetUnitConversion(r); // r.Close(); // if (uc == null) // { // uc = new UnitConversion(); // uc.BARCODE = e.BARCODE; // uc.CONVERSION_QTY = 1; // uc.CONVERSION_UNIT = e.UNIT; // uc.COST_PRICE = e.COST_PRICE; // uc.ORIGINAL_QTY = 1; // uc.PART = e; // uc.SELL_PRICE = e.SELL_PRICE; // cmd.CommandText = uc.GetInsertSQL(); // cmd.ExecuteNonQuery(); // } // } //} //private class PartUnit //{ // public int PART, UNIT; //} public IList GetAllEvents(int partID) { ArrayList result = new ArrayList(); OpenConnection(); MySql.Data.MySqlClient.MySqlDataReader rdr; Part p = PartRepository.GetByID(m_cmd, partID); m_cmd.CommandText = PurchaseOrderItem.GetByPartIDSQL(partID); rdr = m_cmd.ExecuteReader(); IList pois = PurchaseOrderItem.TransformReaderList(rdr); rdr.Close(); foreach (PurchaseOrderItem itm in pois) { itm.EVENT = PurchaseOrderRepository.GetHeaderOnly(m_cmd, itm.EVENT.ID); itm.PART = p; result.Add(itm); } m_cmd.CommandText = SalesOrderItem.GetByPartIDSQL(partID); rdr = m_cmd.ExecuteReader(); IList sois = SalesOrderItem.TransformReaderList(rdr); rdr.Close(); foreach (SalesOrderItem itm in sois) { itm.EVENT = SalesOrderRepository.GetHeaderOnly(m_cmd, itm.EVENT.ID); itm.PART = p; result.Add(itm); } m_cmd.CommandText = GoodReceiveNoteItem.GetByPartIDSQL(partID); rdr = m_cmd.ExecuteReader(); IList grnis = GoodReceiveNoteItem.TransformReaderList(rdr); rdr.Close(); foreach (GoodReceiveNoteItem itm in grnis) { itm.EVENT = GoodReceiveNoteRepository.GetHeaderOnly(m_cmd, itm.EVENT.ID); itm.PART = p; result.Add(itm); } m_cmd.CommandText = PurchaseReturnItem.GetByPartIDSQL(partID); rdr = m_cmd.ExecuteReader(); IList prnis = PurchaseReturnItem.TransformReaderList(rdr); rdr.Close(); foreach (PurchaseReturnItem itm in prnis) { itm.EVENT = PurchaseReturnRepository.GetHeaderOnly(m_cmd, itm.EVENT.ID); itm.PART = p; result.Add(itm); } m_cmd.CommandText = SalesReturnItem.GetByPartIDSQL(partID); rdr = m_cmd.ExecuteReader(); IList srnis = SalesReturnItem.TransformReaderList(rdr); rdr.Close(); foreach (SalesReturnItem itm in srnis) { itm.EVENT = SalesReturnRepository.GetHeaderOnly(m_cmd, itm.EVENT.ID); itm.PART = p; result.Add(itm); } m_cmd.CommandText = SupplierInvoiceItem.GetByPartIDSQL(partID); rdr = m_cmd.ExecuteReader(); IList piis = SupplierInvoiceItem.TransformReaderList(rdr); rdr.Close(); foreach (SupplierInvoiceItem itm in piis) { itm.EVENT = SupplierInvoiceRepository.GetHeaderOnly(m_cmd, itm.EVENT.ID); itm.PART = p; if (itm.GRN_ITEM.ID == 0) { result.Add(itm); } } m_cmd.CommandText = CustomerInvoiceItem.GetByPartIDSQL(partID); rdr = m_cmd.ExecuteReader(); IList ciis = CustomerInvoiceItem.TransformReaderList(rdr); rdr.Close(); foreach (CustomerInvoiceItem itm in ciis) { itm.EVENT = CustomerInvoiceRepository.GetHeaderOnly(m_cmd, itm.EVENT.ID); itm.PART = p; if (itm.DO_ITEM.ID == 0) { result.Add(itm); } } m_cmd.CommandText = POSItem.GetByPartIDSQL(partID); rdr = m_cmd.ExecuteReader(); IList posi = POSItem.TransformReaderList(rdr); rdr.Close(); foreach (POSItem itm in posi) { itm.EVENT = POSRepository.GetHeaderOnly(m_cmd, itm.EVENT.ID); itm.PART = p; if (itm.DO_ITEM.ID == 0) { result.Add(itm); } } m_cmd.CommandText = StockTakingItems.GetByPartIDSQL(partID); rdr = m_cmd.ExecuteReader(); IList sti = StockTakingItems.TransformReaderList(rdr); rdr.Close(); foreach (StockTakingItems itm in sti) { itm.EVENT = StockTakingRepository.GetHeaderOnly(m_cmd, itm.EVENT.ID); itm.PART = p; result.Add(itm); } m_cmd.CommandText = OpeningStockItem.GetByPartIDSQL(partID); rdr = m_cmd.ExecuteReader(); IList opi = OpeningStockItem.TransformReaderList(rdr); rdr.Close(); foreach (OpeningStockItem itm in opi) { itm.EVENT = OpeningStockRepository.GetHeaderOnly(m_cmd, itm.EVENT.ID); itm.PART = p; result.Add(itm); } m_cmd.CommandText = DeliveryOrderItem.GetByPartIDSQL(partID); rdr = m_cmd.ExecuteReader(); IList doi = DeliveryOrderItem.TransformReaderList(rdr); rdr.Close(); foreach (DeliveryOrderItem itm in doi) { itm.EVENT = DeliveryOrderRepository.GetHeaderOnly(m_cmd, itm.EVENT.ID); itm.PART = p; result.Add(itm); } result.Sort(new EventDateComparer()); return(result); }
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; } }