public static void SaveHeader(MySql.Data.MySqlClient.MySqlCommand cmd, StockCard sc)
{
cmd.CommandText = sc.GetInsertSQL();
cmd.ExecuteNonQuery();
cmd.CommandText = StockCard.SelectMaxIDSQL();
sc.ID = Convert.ToInt32(cmd.ExecuteScalar());
}
public StockCard Create(Period newPeriod)
{
StockCard sCard = new StockCard(PART, WAREHOUSE, newPeriod);
sCard.BALANCE = BALANCE;
sCard.BACK_ORDER = BACK_ORDER;
sCard.BOOKED = BOOKED;
return(sCard);
}
//public static StockCard FindStockCard(MySql.Data.MySqlClient.MySqlCommand cmd, long periodId)
//{
// cmd.CommandText = String.Format("select * from table_stockcard where period_id = {0}", periodId);
// MySql.Data.MySqlClient.MySqlDataReader r = cmd.ExecuteReader();
// StockCard sc = StockCard.TransformReader(r);
// r.Close();
// return sc;
//}
public static IList FindStockCardByPeriod(MySql.Data.MySqlClient.MySqlCommand cmd, long periodId)
{
cmd.CommandText = String.Format("select * from table_stockcard where period_id = {0}", periodId);
MySql.Data.MySqlClient.MySqlDataReader r = cmd.ExecuteReader();
IList sc = StockCard.TransforReaderList(r);
r.Close();
return(sc);
}
public StockCardEntry(StockCard stockCard, EventItem item)
{
STOCK_CARD = stockCard;
STOCK_CARD_ENTRY_TYPE = item.STOCK_CARD_ENTRY_TYPE;
TRANSACTION_DATE = item.EVENT.TRANSACTION_DATE;
AMOUNT = item.GetAmountInSmallestUnit();
UNIT = item.PART.UNIT;
item.STOCK_CARD_ENTRY = this;
EVENT_ITEM = item;
}
protected void SetStockCard(EventItem item, Period period)
{
StockCard sc = StockCardRepository.FindStockCardHeader(m_command, item.PART.ID, item.WAREHOUSE.ID, period, item.EVENT.TRANSACTION_DATE);
item.WAREHOUSE = StockCardRepository.FindWarehouse(m_command, item.WAREHOUSE.ID);
item.PART.UNIT = StockCardRepository.FindUnit(m_command, item.PART.UNIT.ID);
if (sc == null)
{
sc = StockCard.CreateStockCard(item, period);
}
item.STOCK_CARD = sc;
item.STOCK_CARD.PART = item.PART;
}
public static void Save(MySql.Data.MySqlClient.MySqlCommand cmd, StockCard sc)
{
cmd.CommandText = sc.GetInsertSQL();
cmd.ExecuteNonQuery();
cmd.CommandText = StockCard.SelectMaxIDSQL();
sc.ID = Convert.ToInt32(cmd.ExecuteScalar());
foreach (StockCardEntry sce in sc.STOCK_CARD_ENTRIES)
{
cmd.CommandText = sce.GetInsertSQL();
cmd.ExecuteNonQuery();
cmd.CommandText = StockCardEntry.SelectMaxIDSQL();
sce.ID = Convert.ToInt32(cmd.ExecuteScalar());
}
}
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);
}
protected void updateStockCards(IList items)
{
foreach (EventItem item in items)
{
StockCard sc = item.STOCK_CARD;
if (sc.ID > 0)
{
StockCardRepository.UpdateHeader(m_command, sc);
}
else if (sc.ID == 0)
{
StockCardRepository.SaveHeader(m_command, sc);
}
if (item.STOCK_CARD_ENTRY != null)
{
StockCardEntryRepository.Save(m_command, item.STOCK_CARD_ENTRY);
}
}
}
public static StockCard TransformReader(MySql.Data.MySqlClient.MySqlDataReader aReader)
{
StockCard stockcard = null;
if (aReader.HasRows)
{
aReader.Read();
stockcard = new StockCard();
stockcard.ID = Convert.ToInt32(aReader[0]);
stockcard.PART = new Part(Convert.ToInt32(aReader["part_id"]));
stockcard.WAREHOUSE = new Warehouse(Convert.ToInt32(aReader["warehouse_id"]));
stockcard.PERIOD = new Period(Convert.ToInt32(aReader["period_id"]));
stockcard.BALANCE = Convert.ToDouble(aReader["sc_balance"]);
stockcard.BACK_ORDER = Convert.ToDouble(aReader["sc_backorder"]);
stockcard.BOOKED = Convert.ToDouble(aReader["sc_booked"]);
stockcard.BALANCE_AVAILABLE = stockcard.BALANCE;
stockcard.BACK_ORDER_AVAILABLE = stockcard.BACK_ORDER;
stockcard.BOOK_AVAILABLE = stockcard.BOOKED;
}
return(stockcard);
}
public static IList TransforReaderList(MySql.Data.MySqlClient.MySqlDataReader aReader)
{
IList result = new ArrayList();
while (aReader.Read())
{
StockCard stockcard = new StockCard();
stockcard.ID = Convert.ToInt32(aReader[0]);
stockcard.PART = new Part(Convert.ToInt32(aReader["part_id"]));
stockcard.WAREHOUSE = new Warehouse(Convert.ToInt32(aReader["warehouse_id"]));
stockcard.PERIOD = new Period(Convert.ToInt32(aReader["period_id"]));
stockcard.BALANCE = Convert.ToDouble(aReader["sc_balance"]);
stockcard.BACK_ORDER = Convert.ToDouble(aReader["sc_backorder"]);
stockcard.BOOKED = Convert.ToDouble(aReader["sc_booked"]);
stockcard.BALANCE_AVAILABLE = stockcard.BALANCE;
stockcard.BACK_ORDER_AVAILABLE = stockcard.BACK_ORDER;
stockcard.BOOK_AVAILABLE = stockcard.BOOKED;
result.Add(stockcard);
}
return(result);
}
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 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 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 static void Update(MySql.Data.MySqlClient.MySqlCommand cmd, StockCard sc)
{
cmd.CommandText = sc.GetUpdateSQL();
cmd.ExecuteNonQuery();
foreach (StockCardEntry sce in sc.STOCK_CARD_ENTRIES)
{
if (sce.ID == 0)
{
cmd.CommandText = sce.GetInsertSQL();
cmd.ExecuteNonQuery();
cmd.CommandText = StockCardEntry.SelectMaxIDSQL();
sce.ID = Convert.ToInt32(cmd.ExecuteScalar());
}
else
{
cmd.CommandText = sce.GetUpdateSQL();
cmd.ExecuteNonQuery();
}
}
cmd.CommandText = StockCardEntry.FindByStockCard(sc.ID);
MySql.Data.MySqlClient.MySqlDataReader r = cmd.ExecuteReader();
IList sces = StockCardEntry.TransformReaderList(r);
r.Close();
foreach (StockCardEntry sce in sces)
{
sce.UPDATED = sc.STOCK_CARD_ENTRIES.Contains(sce);
}
foreach (StockCardEntry sce in sces)
{
if (!sce.UPDATED)
{
cmd.CommandText = StockCardEntry.DeleteSQL(sce.ID);
cmd.ExecuteNonQuery();
}
}
}
public static void DeleteHeader(MySql.Data.MySqlClient.MySqlCommand cmd, StockCard sc)
{
cmd.CommandText = sc.GetDeleteSQL();
cmd.ExecuteNonQuery();
}
public DataSet GetStockReport(bool allPart, string partStart, string partEnd, bool allGroup,
string groupStart, string groupEnd, DateTime asOfDate)
{
DataSet ds = new DataSet();
m_cmd.CommandText = "delete from table_temp";
m_cmd.ExecuteNonQuery();
m_cmd.CommandText = Part.GetStockReport(allPart, partStart, partEnd, allGroup, groupStart, groupEnd);
MySql.Data.MySqlClient.MySqlDataReader rdr = m_cmd.ExecuteReader();
IList result = Part.GetAllStaticForReport(rdr);
rdr.Close();
//IList stockcards = new ArrayList();
foreach (Part p in result)
{
p.UNIT = PartRepository.GetUnitByID(m_cmd, p.UNIT.ID);
p.PART_GROUP = PartRepository.GetPartGroupByID(m_cmd, p.PART_GROUP.ID);
StockCard sc = new StockCard(p, null, null);
m_cmd.CommandText = StockCardEntry.FindByStockCardEntryByPart(p.ID, new DateTime(2000, 1, 1), asOfDate);
rdr = m_cmd.ExecuteReader();
sc.STOCK_CARD_ENTRIES = StockCardEntry.TransformReaderList(rdr);
rdr.Close();
foreach (StockCardEntry e in sc.STOCK_CARD_ENTRIES)
{
if (e.STOCK_CARD_ENTRY_TYPE == StockCardEntryType.SupplierInvoice)
{
m_cmd.CommandText = SupplierInvoiceItem.GetByIDSQL(e.EVENT_ITEM.ID);
rdr = m_cmd.ExecuteReader();
e.EVENT_ITEM = SupplierInvoiceItem.TransformReader(rdr);
rdr.Close();
}
if (e.STOCK_CARD_ENTRY_TYPE == StockCardEntryType.CustomerInvoice)
{
m_cmd.CommandText = CustomerInvoiceItem.GetByIDSQL(e.EVENT_ITEM.ID);
rdr = m_cmd.ExecuteReader();
e.EVENT_ITEM = CustomerInvoiceItem.TransformReader(rdr);
rdr.Close();
}
}
sc.recalculateAvailable();
m_cmd.CommandText = string.Format(@"insert into table_temp
(
string1,string2,string3,double1,double2
)
values
(
'{0}','{1}','{2}',{3},{4}
)",
p.PART_GROUP.NAME + " ~ " + p.PART_GROUP.CODE,
p.CODE,
p.UNIT.NAME,
sc.BALANCE_AVAILABLE,
p.MINIMUM_STOCK
);
m_cmd.ExecuteNonQuery();
//stockcards.Add(sc);
}
return(ds);
}
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;
}
}
