public int RecordCount()
{
m_command.CommandText = OpeningStock.RecordCount();
int result = Convert.ToInt32(m_command.ExecuteScalar());
return(result);
}
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 static OpeningStock TransformReader(MySql.Data.MySqlClient.MySqlDataReader aReader)
{
OpeningStock transaction = null;
if (aReader.HasRows)
{
aReader.Read();
transaction = new OpeningStock();
transaction.ID = Convert.ToInt32(aReader["opst_id"]);
transaction.TRANSACTION_DATE = Convert.ToDateTime(aReader["opst_date"]);
transaction.NOTICE_DATE = Convert.ToDateTime(aReader["opst_noticedate"]);
transaction.STOCK_CARD_ENTRY_TYPE = (StockCardEntryType)Enum.Parse(typeof(StockCardEntryType), aReader["opst_scentrytype"].ToString());
transaction.EMPLOYEE = new Employee(Convert.ToInt32(aReader["emp_id"]));
transaction.NOTES = aReader["opst_notes"].ToString();
transaction.POSTED = Convert.ToBoolean(aReader["opst_posted"]);
transaction.EVENT_STATUS = (EventStatus)Enum.Parse(typeof(EventStatus), aReader["opst_eventstatus"].ToString());
transaction.WAREHOUSE = new Warehouse(Convert.ToInt32(aReader["warehouse_id"]));
transaction.AMOUNT = Convert.ToDouble(Convert.ToInt32(aReader["opst_amount"]));
transaction.CURRENCY = new Currency(Convert.ToInt32(aReader["ccy_id"]));
transaction.CODE = aReader["opst_code"].ToString();
transaction.MODIFIED_BY = aReader["modified_by"].ToString();
transaction.MODIFIED_DATE = Convert.ToDateTime(aReader["modified_date"].ToString());
transaction.MODIFIED_COMPUTER_NAME = aReader["modified_computer"].ToString();
}
return(transaction);
}
private EventStatus getEventStatus(int id)
{
m_command.CommandText = OpeningStock.GetEventStatus(id);
object b = m_command.ExecuteScalar();
EventStatus m = (EventStatus)Enum.Parse(typeof(EventStatus), b.ToString());
return(m);
}
public static OpeningStock GetHeaderOnly(MySql.Data.MySqlClient.MySqlCommand cmd, int poID)
{
cmd.CommandText = OpeningStock.GetByIDSQL(poID);
MySql.Data.MySqlClient.MySqlDataReader r = cmd.ExecuteReader();
OpeningStock st = OpeningStock.TransformReader(r);
r.Close();
return(st);
}
public override Event FindLastCodeAndTransactionDate(string codesample)
{
m_command.CommandText = OpeningStock.FindLastCodeAndTransactionDate(codesample);
MySql.Data.MySqlClient.MySqlDataReader r = m_command.ExecuteReader();
Event e = OpeningStock.TransformReader(r);
r.Close();
return(e);
}
protected override bool doIsCodeExist(string code)
{
try
{
m_command.CommandText = OpeningStock.SelectCountByCode(code);
int t = Convert.ToInt32(m_command.ExecuteScalar());
return(t > 0);
}
catch (Exception x)
{
throw x;
}
}
protected override IList doSearch(string find)
{
try
{
m_command.CommandText = OpeningStock.GetSearch(find);
MySql.Data.MySqlClient.MySqlDataReader r = m_command.ExecuteReader();
IList rest = OpeningStock.TransformReaderList(r);
r.Close();
return(rest);
}
catch (Exception x)
{
throw x;
}
}
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;
}
}
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;
}
}
protected override void doUpdateStatus(Event e, bool posted)
{
m_command.CommandText = OpeningStock.GetUpdateStatusSQL(e);
m_command.ExecuteNonQuery();
}
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;
}
}
