public List <cCurrencyValue> getCurrencyList()
{
cMySQLCommands cMySQLCom = new cMySQLCommands();
List <cCurrencyValue> oCurrencyList = new List <cCurrencyValue>();
MySqlDataReader reader;
cMySQLCom.addFields("quote_currency.cur_isoCode");
cMySQLCom.addFields("currency.cur_name");
cMySQLCom.addFields("quote_currency.cur_value");
cMySQLCom.addFields("quote_currency.selected");
cMySQLCom._tblName = "quote_currency";
cMySQLCom._innerTableName = "currency";
cMySQLCom._innerLeftOn = "quote_currency.cur_isoCode";
cMySQLCom._innerRightOn = "currency.cur_isocode";
reader = cMySQLCom.innerSelectQuery();
if (reader.HasRows)
{
while (reader.Read())
{
oCurrencyList.Add(new cCurrencyValue
{
_currIsoCode = reader.GetString(0),
_currName = reader.GetString(1),
_currExchange = reader.GetDouble(2),
_selected = reader.GetString(3)
});
}
}
return(oCurrencyList);
}
//set ers columns based on object's values
public void setStatus()
{
cMySQLCommands cMySQLCom = new cMySQLCommands();
//cMySQLCommands oDeleteOldEntry = new cMySQLCommands();
//oDeleteOldEntry._values = "";
cMySQLCom._values = "";
//oDeleteOldEntry._tblName = "ers_tagged";
cMySQLCom._tblName = "ers_tagged";
//oDeleteOldEntry._condition = "ers_tagged.rst_no = '"+ _rstNo +
//"' and ers_tagged.tag_status = '' and ers_tagged.rst_loccd='"+ _rstLocCd + "'";
cMySQLCom.addFields("tag_status");
cMySQLCom.addFields("rst_no");
cMySQLCom.addFields("rst_locCd");
cMySQLCom.addValues(_rstStat);
cMySQLCom.addValues(_rstNo);
cMySQLCom.addValues(_rstLocCd);
//oDeleteOldEntry.deleteQuery();
cMySQLCom.insertQuery();
}
public void savePOCurrency()
{
cMySQLCommands cMySQLCom = new cMySQLCommands();
cMySQLCom._tblName = "spo_currency";
cMySQLCom.addFields("poc_no");
cMySQLCom.addFields("ers_no");
cMySQLCom.addFields("poc_date");
cMySQLCom.addFields("cur_code");
cMySQLCom.addFields("cur_name");
cMySQLCom.addFields("cur_val");
cMySQLCom.addFields("remarks");
cMySQLCom.addFields("prepby");
cMySQLCom.addFields("confirmed");
cMySQLCom.addFields("loc_code");
cMySQLCom._values = "";
cMySQLCom.addValues(_pocno);
cMySQLCom.addValues(_pocersno);
cMySQLCom.addValues(_pocdate);
cMySQLCom.addValues(_curCode);
cMySQLCom.addValues(_curname);
cMySQLCom.addValues(_curVal);
cMySQLCom.addValues(_pocremarks);
cMySQLCom.addValues(_pocPrepBy);
cMySQLCom.addValues(_pocConfirm);
cMySQLCom.addValues(_pocLocCode);
cMySQLCom.insertQuery();
}
//same as savetotable function but the only difference is the query
//removes all existing attachments related to the transaction no and transaction type
//and adds all attachments included
public void edittotable(string transno, string transtype, List <cAttachment> cAttachList)
{
//remove all transaction entries first
cMySQLCommands mySqlCom = new cMySQLCommands();
cMySQLCommands mySqlDel = new cMySQLCommands();
mySqlDel._tblName = "attachments";
mySqlDel._condition = " transno = '" + transno + "' and transtype = '" + transtype + "' and loc_code = '" + selLocCode + "'";
mySqlDel.deleteQuery();
mySqlCom._tblName = "attachments";
mySqlCom.addFields("transno");
mySqlCom.addFields("transtype");
mySqlCom.addFields("sup_code");
mySqlCom.addFields("filename");
mySqlCom.addFields("loc_code");
//adds attachment values per entry in list
foreach (cAttachment cAttach in cAttachList)
{
mySqlCom._values = "";
mySqlCom.addValues(transno);
mySqlCom.addValues(transtype);
mySqlCom.addValues(cAttach._supcd);
mySqlCom.addValues(Path.GetFileName(cAttach._attachmentName));
mySqlCom.addValues(cAttach._loccd);
copyToCNVPICT(cAttach._attachmentName);
mySqlCom.insertQuery();
}
}
//used to return attachments included from the supplier
//in the quotation transaction. Query is based on
//transaction number and supplier
public List <cAttachment> loadSupQuoteAttachment(string transno, string supcd, string loccd)
{
List <cAttachment> cAttachGetList = new List <cAttachment>();
cMySQLCommands mySqlCom = new cMySQLCommands();
MySqlDataReader reader;
mySqlCom._tblName = "attachments";
_transtype = "Quotation";
_transno = transno;
_supcd = supcd;
_loccd = loccd;
mySqlCom.addFields("transno");
mySqlCom.addFields("sup_code");
mySqlCom.addFields("filename");
mySqlCom._condition = "transno = '" + transno + "' and sup_code ='" + supcd + "' and loc_code = '" + _loccd + "'";
reader = mySqlCom.selectQueryWhere();
if (reader.HasRows)
{
while (reader.Read())
{
cAttachGetList.Add(new cAttachment
{
_Code = reader.GetString(0),
_supcd = reader.GetString(1),
_attachmentName = reader.GetString(2),
});
}
}
return(cAttachGetList);
}
//Used to add entry in ERS Currency table .dbf file in system
public void createERSCurrency(List <cERequisitionCur> eRequisitionCurList)
{
//for each value in listed currencies
foreach (cERequisitionCur oERequisitionCur in eRequisitionCurList)
{
//intialized variables
List <setDBFValues> dbfColumnList = new List <setDBFValues>();
cMySQLCommands cInsertERSQuery = new cMySQLCommands();
cInsertERSQuery._tblName = "ers_currency";
cInsertERSQuery._values = "";
//get ers number +1
_eno = (Convert.ToDouble(cInsertERSQuery.getDBFSequenceNo("ERS")) + 1).ToString();
//currency columns
//selected value is only 1 per entry list
cInsertERSQuery.addFields("ers_no");
cInsertERSQuery.addFields("cur_code");
cInsertERSQuery.addFields("cur_name");
cInsertERSQuery.addFields("cur_val");
cInsertERSQuery.addFields("selected");
//ers currency entry per list will be added based on value
cInsertERSQuery.addValues(_eno);
cInsertERSQuery.addValues(oERequisitionCur._rscCode);
cInsertERSQuery.addValues(oERequisitionCur._rscName);
cInsertERSQuery.addValues(oERequisitionCur._rscValue);
cInsertERSQuery.addValues(oERequisitionCur._rscSelected);
//commented of uninsert ers_line
// cInsertERSQuery.insertQuery();
//set datatypes for dbf Column
dbfColumnList.Add(new setDBFValues {
valueName = _ersno, valueType = "NUMERIC"
});
dbfColumnList.Add(new setDBFValues {
valueName = oERequisitionCur._rscCode, valueType = "STRING"
});
dbfColumnList.Add(new setDBFValues {
valueName = oERequisitionCur._rscName, valueType = "STRING"
});
dbfColumnList.Add(new setDBFValues {
valueName = oERequisitionCur._rscValue, valueType = "NUMERIC"
});
dbfColumnList.Add(new setDBFValues {
valueName = oERequisitionCur._rscSelected, valueType = "BOOL"
});
cInsertERSQuery.insertDBFQuery(dbfColumnList);
// cInsertERSQuery.closeDBFAll();}
}
}
public MySqlDataReader getQuoteLine()
{
cMySQLCommands cMySQLCom = new cMySQLCommands();
MySqlDataReader reader;
cMySQLCom.addFields("quote_line.itm_code");
cMySQLCom.addFields("quote_line.itm_qty");
cMySQLCom.addFields("quote_line.itm_uom");
cMySQLCom.addFields("quote_line.itm_cost");
cMySQLCom.addFields("quote_line.itm_disc");
cMySQLCom.addFields("quote_line.itm_freight");
cMySQLCom.addFields("quote_line.itm_vat");
cMySQLCom.addFields("quote_line.itm_net");
cMySQLCom._tblName = "quote_line";
cMySQLCom._innerTableName = "item_mst";
cMySQLCom._innerLeftOn = "quote_line.itm_code";
cMySQLCom._innerRightOn = "item_mst.itm_code";
cMySQLCom._condition = " quote_line.quote_no = '" + _quoteno + "'";
reader = cMySQLCom.innerSelectQueryWhere();
return(reader);
}
public MySqlDataReader getQuoteLineERS()
{
MySqlDataReader reader;
//DBConnection dbcon = new DBConnection();
//edited to fix bugs unselected items included in ers
cMySQLCommands cMySQLCom = new cMySQLCommands();
cMySQLCom.addFields("quote_line.itm_code");
cMySQLCom.addFields("quote_line.itm_qty");
cMySQLCom.addFields("quote_line.itm_uom");
cMySQLCom.addFields("quote_line.itm_cost");
cMySQLCom.addFields("(quote_line.itm_cost * quote_line.itm_qty) AS itm_amt");
cMySQLCom.addFields("quote_line.itm_disc");
cMySQLCom.addFields("quote_line.itm_net");
cMySQLCom.addFields("item_mst.itm_specs");
cMySQLCom._tblName = "quote_line";
cMySQLCom._innerTableName = "item_mst";
cMySQLCom._innerLeftOn = "quote_line.itm_code";
cMySQLCom._innerRightOn = "item_mst.itm_code";
//added quote _quote no due to varchar
cMySQLCom._condition = " quote_line.quote_no = '" + _quoteno + "' and quote_line.selected = 'True' and " +
"quote_line.quote_loccd = '" + _locCode + "'";
reader = cMySQLCom.innerSelectQueryWhere();
return(reader);
}
public void getSERSHeader(string selSERSNo)
{
DBConnection dbCon = new DBConnection();
cLocation oLocation = new cLocation();
cCompany oCompany = new cCompany();
cMySQLCommands oMySQLCommand = new cMySQLCommands();
MySqlDataReader reader;
oMySQLCommand.addFields("sers_tran.rsh_dreq");
oMySQLCommand.addFields("sers_tran.rsh_dneed");
oMySQLCommand.addFields("sers_tran.rsh_loccd");
oMySQLCommand.addFields("sers_tran.rsh_depcd");
oMySQLCommand.addFields("sers_tran.rsh_dstamp");
oMySQLCommand.addFields("sers_tran.rsh_stat");
oMySQLCommand.addFields("usertab.usr_name");
oMySQLCommand.addFields("sers_tran.rsh_rem");
oMySQLCommand.addFields("sers_tran.rsh_type");
oMySQLCommand.addFields("sers_tran.rsh_apprby");
oMySQLCommand._tblName = "sers_tran";
oMySQLCommand._innerTableName = "usertab";
oMySQLCommand._innerLeftOn = "sers_tran.rsh_uid";
oMySQLCommand._innerRightOn = "usertab.usr_login";
oMySQLCommand._condition = "sers_tran.rsh_no = '" + selSERSNo + "'";
reader = oMySQLCommand.innerSelectQueryWhere();
if (reader.HasRows)
{
while (reader.Read())
{
_dateRequested = reader.GetString(0);
_dateNeeded = reader.GetString(1);
_loccode = reader.GetString(2);
_deptcode = reader.GetString(3);
_datestamp = reader.GetString(4);
_status = reader.GetString(5);
_uid = reader.GetString(6);
_remarks = reader.GetString(7);
_sersType = reader.GetString(8);
//_noOfCopies = reader.GetString(25);
_approvedBy = reader.GetString(9);
}
}
////getLocation();
//getCompany();
}
public void addEventEntry()
{
cLocation oLocation = new cLocation();
oLocation._code = _locCode;
oLocation.setLocationDetails();
cMySQLCommands oMySQLCommands = new cMySQLCommands();
oMySQLCommands._tblName = "eventlog";
oMySQLCommands.addFields("eventtime");
oMySQLCommands.addFields("eventname");
oMySQLCommands._values = "";
oMySQLCommands.addValues(_logEventDateTime);
oMySQLCommands.addValues(oLocation._name.TrimEnd() + " has " + _logEventCount + "entries of " + _logEventName);
oMySQLCommands.insertQuery();
}
public void saveCurrencies(List <cCurrencyValue> cCurrencyValueList, string selCurCode, bool editMode)
{
string selectedCurrCode = selCurCode;
cMySQLCommands cMySQLCom = new cMySQLCommands();
cMySQLCommands cMySQLComDel = new cMySQLCommands();
cMySQLCom._tblName = "quote_currency";
cMySQLCom.addFields("quote_no");
cMySQLCom.addFields("quote_loccd");
cMySQLCom.addFields("cur_isoCode");
cMySQLCom.addFields("cur_value");
cMySQLCom.addFields("selected");
if (editMode == true)
{
cMySQLComDel._fields = "";
cMySQLComDel._tblName = "quote_currency";
cMySQLComDel._condition = "quote_no = '" + _quoteno + "'";
cMySQLComDel.deleteQuery();
}
if (cCurrencyValueList.Count > 0)
{
foreach (cCurrencyValue cCurrencyValue in cCurrencyValueList)
{
cMySQLCom._values = "";
cMySQLCom.addValues(_quoteno);
cMySQLCom.addValues(_locCode);
cMySQLCom.addValues(cCurrencyValue._currIsoCode);
cMySQLCom.addValues(Convert.ToString(cCurrencyValue._currExchange));
if (cCurrencyValue._currIsoCode == selectedCurrCode)
{
cMySQLCom.addValues("True");
}
else
{
cMySQLCom.addValues("False");
}
cMySQLCom.insertQuery();
}
}
}
public void editHeader()
{
cMySQLCommands cMySQLCom = new cMySQLCommands();
cMySQLCom._tblName = "quote_tran";
cMySQLCom.addFields("sersno");
cMySQLCom.addFields("quotedate");
cMySQLCom.addFields("remarks");
cMySQLCom.addFields("prepby");
cMySQLCom.addFields("quote_stat");
cMySQLCom._values = "";
cMySQLCom.addValues(_sersno);
cMySQLCom.addValues(_quoteDate);
cMySQLCom.addValues(_remarks);
cMySQLCom.addValues(_prepby);
cMySQLCom.addValues(_status);
cMySQLCom._condition = "quote_tran.quote_no = '" + _quoteno + "'";
cMySQLCom.updateQueries();
}
public MySqlDataReader getQuoteCurrency()
{
cMySQLCommands cMySQLCom = new cMySQLCommands();
MySqlDataReader reader;
cMySQLCom.addFields("quote_currency.quote_no");
cMySQLCom.addFields("quote_currency.cur_isoCode");
cMySQLCom.addFields("currency.cur_name");
cMySQLCom.addFields("quote_currency.cur_Value");
cMySQLCom.addFields("quote_currency.selected");
cMySQLCom._tblName = "quote_currency";
cMySQLCom._innerTableName = "currency";
cMySQLCom._innerLeftOn = "quote_currency.cur_isoCode";
cMySQLCom._innerRightOn = "currency.cur_isoCode";
cMySQLCom._condition = " quote_currency.quote_no = '" + _quoteno + "' and quote_currency.quote_loccd = '" + _locCode + "'";
reader = cMySQLCom.innerSelectQueryWhere();
return(reader);
}
public MySqlDataReader getSERSHeader()
{
cMySQLCommands cMySQLCom = new cMySQLCommands();
MySqlDataReader reader;
cMySQLCom.addFields("sers_tran.rsh_loccd");
cMySQLCom.addFields("sers_tran.rsh_depcd");
cMySQLCom.addFields("sers_tran.rsh_dneed");
cMySQLCom._tblName = "quote_tran";
cMySQLCom._innerTableName = "sers_tran";
cMySQLCom._innerLeftOn = "quote_tran.sersno";
cMySQLCom._innerRightOn = "sers_tran.rsh_no";
//added quote due to var char
cMySQLCom._condition = "sers_tran.rsh_no = '" + _sersno + "'";
reader = cMySQLCom.innerSelectQueryWhere();
return(reader);
}
//updates ers tagging
public void setUntagged()
{
cMySQLCommands cMySQLCom = new cMySQLCommands();
cMySQLCom._values = "";
cMySQLCom._tblName = "ers_tagged";
cMySQLCom.addFields("tag_status");
cMySQLCom.addValues(_rstStat);
cMySQLCom._condition = "rst_no = '" + _rstNo + "' and rst_loccd = '" + _rstLocCd + "'";
cMySQLCom.updateQuery();
}
//used to save the class attachments to table
//transno as the transaction number when the attachment was included
//transtype is the transaction type where the attachment is included e.g. "Quotation", "E-Requisition" etc.
//List<cAttachment> a list made to receive multiple attachments in saving
public void savetotable(string transno, string transtype, List <cAttachment> cAttachList)
{
cMySQLCommands mySqlCom = new cMySQLCommands();
mySqlCom._tblName = "attachments";
mySqlCom.addFields("transno");
mySqlCom.addFields("transtype");
mySqlCom.addFields("sup_code");
mySqlCom.addFields("filename");
mySqlCom.addFields("loc_code");
foreach (cAttachment cAttach in cAttachList)
{
mySqlCom._values = "";
mySqlCom.addValues(transno);
mySqlCom.addValues(transtype);
mySqlCom.addValues(cAttach._supcd);
mySqlCom.addValues(Path.GetFileName(cAttach._attachmentName));
mySqlCom.addValues(cAttach._loccd);
copyToCNVPICT(cAttach._attachmentName);
mySqlCom.insertQuery();
}
}
public void loadQuoteSupplier()
{
MySqlDataReader reader;
cMySQLCommands oMySqlCommands = new cMySQLCommands();
oMySqlCommands.addFields("quote_supplier.sup_code");
oMySqlCommands.addFields("supplier.sup_name");
oMySqlCommands.addFields("quote_supplier.netamt");
oMySqlCommands._tblName = "quote_supplier";
oMySqlCommands._innerTableName = "supplier";
oMySqlCommands._innerLeftOn = "supplier.sup_code";
oMySqlCommands._innerRightOn = "quote_supplier.sup_code";
oMySqlCommands._condition = "quote_supplier.quote_no = '" + _quoteno + "' and selected = 1";
reader = oMySqlCommands.innerSelectQueryWhere();
if (reader.HasRows)
{
while (reader.Read())
{
_supCode = reader.GetString(0);
_supName = reader.GetString(1);
_netAmt = reader.GetString(2);
}
}
}
public void setStatus()
{
cMySQLCommands cMySQLCom = new cMySQLCommands();
cMySQLCom._values = "";
cMySQLCom._tblName = "quote_tran";
cMySQLCom.addFields("quote_stat");
cMySQLCom.addValues(_status);
cMySQLCom._condition = " quote_no = '" + _quoteno + "' and quote_loccd = '" + _locCode + "'";
cMySQLCom.updateQuery();
}
//method used in retrieving ers currency
public void getErsCurrency()
{
// DBConnection dbCon = new DBConnection();
cMySQLCommands oMySQLCommand = new cMySQLCommands();
MySqlDataReader reader;
oMySQLCommand.addFields("quote_currency.cur_isoCode");
oMySQLCommand.addFields("quote_currency.cur_value");
oMySQLCommand._tblName = "quote_currency";
oMySQLCommand._innerTableName = "ers_tran";
oMySQLCommand._innerLeftOn = "quote_currency.quote_no";
oMySQLCommand._innerRightOn = "ers_tran.cnv_ctrlno";
oMySQLCommand._condition = " quote_currency.quote_no = '" + _canvassCtrlNo + "'";
reader = oMySQLCommand.innerSelectQueryWhere();
if (reader.HasRows)
{
while (reader.Read())
{
_rscCode = reader.GetString(0);
_rscValue = reader.GetString(1);
}
}
}
public void getCompany()
{
DBConnection dbCon = new DBConnection();
cMySQLCommands oMySQLCommand = new cMySQLCommands();
MySqlDataReader reader;
oMySQLCommand.addFields("company.com_name");
oMySQLCommand._tblName = "sers_tran";
oMySQLCommand._innerTableName = "company";
oMySQLCommand._innerLeftOn = "sers_tran.rsh_depcd";
oMySQLCommand._innerRightOn = "company.com_code";
oMySQLCommand._condition = " company.com_code = '" + _deptcode.TrimEnd() + "'";
reader = oMySQLCommand.innerSelectQueryWhere();
if (reader.HasRows)
{
while (reader.Read())
{
_deptname = reader.GetString(0);
}
}
}
public string[] getAllServer()
{
string[] addedLoc = { "" };
cMySQLCommands oMySQLCommands = new cMySQLCommands();
MySqlDataReader reader;
oMySQLCommands.addFields(" DISTINCT(serverloc) ");
oMySQLCommands._tblName = "locationconfig";
oMySQLCommands._orderby = "locationConfig.serverloc";
reader = oMySQLCommands.selectQuery();
if (reader.HasRows)
{
while (reader.Read())
{
Array.Resize(ref addedLoc, addedLoc.Length + 1);
addedLoc[addedLoc.Length - 1] = reader.GetString(0);
}
}
return(addedLoc);
}
public void addToTable()
{
cMySQLCommands oMySQLCommands = new cMySQLCommands();
cMySQLCommands mySQLAddNewUser = new cMySQLCommands();
MySqlDataReader reader;
string[] addedUser = { "" };
mySQLAddNewUser.addFields("usertab.usr_login");
mySQLAddNewUser.addFields("usertab.usr_name");
mySQLAddNewUser.addFields("usertab.usr_pass");
mySQLAddNewUser.addFields("usertab.usr_loc");
mySQLAddNewUser.addFields("usertab.usr_dept");
mySQLAddNewUser.addFields("usertab.usr_pos");
mySQLAddNewUser._tblName = "usertab";
oMySQLCommands._fields = "";
oMySQLCommands._tblName = "usertab";
oMySQLCommands._condition = "usertab.usr_login = '******'";
reader = oMySQLCommands.selectQueryWhere();
if (reader.HasRows == false)
{
cOleDBCommand oOledbCommand = new cOleDBCommand();
OleDbDataReader dbReader;
oOledbCommand.setConStr(setDBManual);
dbReader = oOledbCommand.oleDBQueryReturn("SELECT * FROM usertab WHERE alltrim(upper(usertab.usr_login)) = '" + _userlogin + "'");
if (dbReader.HasRows)
{
if (dbReader.Read())
{
mySQLAddNewUser._values = "";
mySQLAddNewUser.addValues(dbReader.GetString(0));
mySQLAddNewUser.addValues(dbReader.GetString(1));
mySQLAddNewUser.addValues("7007a91dd5b0e797a63ad8527b73f108");
mySQLAddNewUser.addValues(dbReader.GetString(5));
mySQLAddNewUser.addValues(dbReader.GetString(6));
mySQLAddNewUser.addValues(dbReader.GetString(7));
mySQLAddNewUser.insertQuery();
}
}
}
}
public void saveHeader()
{
cMySQLCommands cMySQLCom = new cMySQLCommands();
cMySQLCom._tblName = "quote_tran";
cMySQLCom.addFields("quote_no");
cMySQLCom.addFields("sersno");
cMySQLCom.addFields("quotedate");
cMySQLCom.addFields("remarks");
cMySQLCom.addFields("prepby");
cMySQLCom.addFields("quote_stat");
cMySQLCom.addFields("quote_loccd");
cMySQLCom._values = "";
cMySQLCom.addValues(_quoteno);
cMySQLCom.addValues(_sersno);
cMySQLCom.addValues(_quoteDate);
cMySQLCom.addValues(_remarks);
cMySQLCom.addValues(_prepby);
cMySQLCom.addValues(_status);
cMySQLCom.addValues(_locCode);
cMySQLCom.insertQuery();
}
public List <cSERSLine> getSERSLine()
{
DBConnection dbCon = new DBConnection();
cMySQLCommands oMySQLCommand = new cMySQLCommands();
MySqlDataReader reader;
oMySQLCommand.addFields("sers_line.rsd_date");
oMySQLCommand.addFields("sers_line.rsd_itemcd");
oMySQLCommand.addFields("item_mst.itm_desc");
oMySQLCommand.addFields("sers_line.rsd_qty");
oMySQLCommand.addFields("sers_line.rsd_untms");
//oMySQLCommand.addFields("sers_line.rsd_detl");
oMySQLCommand.addFields("item_mst.itm_specs");
oMySQLCommand._tblName = "sers_line";
oMySQLCommand._innerTableName = "item_mst";
oMySQLCommand._innerLeftOn = "sers_line.rsd_itemcd";
oMySQLCommand._innerRightOn = "item_mst.itm_code";
oMySQLCommand._condition = "sers_line.rsd_no ='" + _sersno + "'";
reader = oMySQLCommand.innerSelectQueryWhere();
if (reader.HasRows)
{
while (reader.Read())
{
cSERSitemList.Add(new cSERSLine
{
_sersdate = reader.GetString(0),
_itemCode = reader.GetString(1),
_description = reader.GetString(2),
_quantity = reader.GetString(3),
_unitOfMeasure = reader.GetString(4),
_specifications = reader.GetString(5)
});
}
}
return(cSERSitemList);
}
public void saveQuotePaymentEntry()
{
cMySQLCommands oMySqlCommands = new cMySQLCommands();
oMySqlCommands._tblName = "quote_payment";
oMySqlCommands.addFields("quote_no");
oMySqlCommands.addFields("sup_code");
oMySqlCommands.addFields("netamt");
oMySqlCommands.addFields("qp_frmPayment");
oMySqlCommands.addFields("qp_dolAccount");
oMySqlCommands.addFields("qp_payAmount");
oMySqlCommands.addValues(_quoteno);
oMySqlCommands.addValues(_supCode);
oMySqlCommands.addValues(_netAmt);
oMySqlCommands.addValues(_formOfPayment);
oMySqlCommands.addValues(_dollarAccount);
oMySqlCommands.addValues(_paymentAmount);
oMySqlCommands.insertQuery();
}
public void createSPOCurrency()
{
List <setDBFValues> dbfColumnList = new List <setDBFValues>();
cMySQLCommands cInsertPOCurQuery = new cMySQLCommands();
cInsertPOCurQuery._tblName = "spo_currency";
cInsertPOCurQuery._values = "";
cInsertPOCurQuery.addFields("poc_no");
cInsertPOCurQuery.addFields("ers_no");
cInsertPOCurQuery.addFields("poc_date");
cInsertPOCurQuery.addFields("cur_code");
cInsertPOCurQuery.addFields("cur_name");
cInsertPOCurQuery.addFields("cur_val");
cInsertPOCurQuery.addFields("remarks");
cInsertPOCurQuery.addFields("prepby");
cInsertPOCurQuery.addFields("confirmed");
cInsertPOCurQuery.addFields("loc_code");
cInsertPOCurQuery.addValues(_pocno);
cInsertPOCurQuery.addValues(_pocersno);
cInsertPOCurQuery.addValues(_pocdate);
cInsertPOCurQuery.addValues(_curCode);
cInsertPOCurQuery.addValues(_curname);
cInsertPOCurQuery.addValues(_curVal);
cInsertPOCurQuery.addValues(_pocremarks);
cInsertPOCurQuery.addValues(_pocPrepBy);
cInsertPOCurQuery.addValues(_pocConfirm);
cInsertPOCurQuery.addValues(_pocLocCode);
dbfColumnList.Add(new setDBFValues {
valueName = _pocno, valueType = "NUMERIC"
});
dbfColumnList.Add(new setDBFValues {
valueName = _pocersno, valueType = "NUMERIC"
});
dbfColumnList.Add(new setDBFValues {
valueName = _pocdate, valueType = "DATE"
});
dbfColumnList.Add(new setDBFValues {
valueName = _curCode, valueType = "STRING"
});
dbfColumnList.Add(new setDBFValues {
valueName = _curname, valueType = "STRING"
});
dbfColumnList.Add(new setDBFValues {
valueName = _curVal, valueType = "NUMERIC"
});
dbfColumnList.Add(new setDBFValues {
valueName = _pocremarks, valueType = "STRING"
});
dbfColumnList.Add(new setDBFValues {
valueName = _pocPrepBy, valueType = "STRING"
});
dbfColumnList.Add(new setDBFValues {
valueName = _pocConfirm, valueType = "BOOL"
});
dbfColumnList.Add(new setDBFValues {
valueName = _pocLocCode, valueType = "STRING"
});
cInsertPOCurQuery.insertDBFQuery(dbfColumnList);
}
public List <cPurchaseOrderItem> getPOLine()
{
DBConnection dbCon = new DBConnection();
cMySQLCommands oMySQLCommand = new cMySQLCommands();
MySqlDataReader reader;
oMySQLCommand.addFields("pod_date");
oMySQLCommand.addFields("pod_itemcd");
oMySQLCommand.addFields("item_mst.itm_desc");
oMySQLCommand.addFields("pod_qty");
oMySQLCommand.addFields("pod_untms");
oMySQLCommand.addFields("pod_untcst");
oMySQLCommand.addFields("pod_amt");
oMySQLCommand.addFields("pod_itemno");
oMySQLCommand.addFields("pod_dcount");
oMySQLCommand.addFields("pod_netamt");
oMySQLCommand.addFields("pod_detl");
oMySQLCommand.addFields("pod_rrstat");
oMySQLCommand.addFields("pod_balanc");
oMySQLCommand._tblName = "spo_line";
oMySQLCommand._innerTableName = "item_mst";
oMySQLCommand._innerLeftOn = "spo_line.pod_itemcd";
oMySQLCommand._innerRightOn = "item_mst.itm_code";
oMySQLCommand._condition = "spo_line.pod_no = " + _pohno;
reader = oMySQLCommand.innerSelectQueryWhere();
if (reader.HasRows)
{
while (reader.Read())
{
cPOItemsList.Add(new cPurchaseOrderItem
{
_poddate = reader.GetString(0),
_itemCode = reader.GetString(1),
_description = reader.GetString(2),
_quantity = reader.GetString(3),
_uom = reader.GetString(4),
_unitcost = reader.GetString(5),
_amount = reader.GetString(6),
_itemno = reader.GetString(7),
_discount = reader.GetString(8),
_netamount = reader.GetString(9),
_detail = reader.GetString(10),
_rrstat = reader.GetString(11),
_balance = reader.GetString(12)
});
}
}
return(cPOItemsList);
}
public List <cQuoteSuppliers> getQuoteSupplierList()
{
cMySQLCommands cMySQLCom = new cMySQLCommands();
MySqlDataReader reader;
cMySQLCom.addFields("quote_supplier.sup_code");
cMySQLCom.addFields("supplier.sup_name");
cMySQLCom.addFields("quote_supplier.discount1");
cMySQLCom.addFields("quote_supplier.d1percent");
cMySQLCom.addFields("quote_supplier.discount2");
cMySQLCom.addFields("quote_supplier.d2percent");
cMySQLCom.addFields("quote_supplier.discount3");
cMySQLCom.addFields("quote_supplier.d3percent");
cMySQLCom.addFields("quote_supplier.discount4");
cMySQLCom.addFields("quote_supplier.d4percent");
cMySQLCom.addFields("quote_supplier.special");
cMySQLCom.addFields("quote_supplier.sppercent");
cMySQLCom.addFields("quote_supplier.othercharges");
cMySQLCom.addFields("quote_supplier.othpercent");
cMySQLCom.addFields("quote_supplier.freight");
cMySQLCom.addFields("quote_supplier.freipercent");
cMySQLCom.addFields("quote_supplier.costanddel");
cMySQLCom.addFields("quote_supplier.cadpercent");
cMySQLCom.addFields("quote_supplier.vat");
cMySQLCom.addFields("quote_supplier.vatpercent");
cMySQLCom.addFields("quote_supplier.netamt");
cMySQLCom.addFields("quote_supplier.selected");
cMySQLCom._tblName = "quote_supplier";
cMySQLCom._innerTableName = "supplier";
cMySQLCom._innerLeftOn = "quote_supplier.sup_code";
cMySQLCom._innerRightOn = "supplier.sup_code";
cMySQLCom._condition = " quote_supplier.quote_no = '" + _quotesupno + "' and quote_supplier.quote_loccd = '" + _locCode + "'";
reader = cMySQLCom.innerSelectQueryWhere();
if (reader.HasRows)
{
while (reader.Read())
{
cQuoteSupplierList.Add(new cQuoteSuppliers
{
_supCode = reader.GetString(0),
_supName = reader.GetString(1),
_discount1 = reader.GetString(2),
_d1Percent = reader.GetString(3),
_discount2 = reader.GetString(4),
_d2Percent = reader.GetString(5),
_discount3 = reader.GetString(6),
_d3Percent = reader.GetString(7),
_discount4 = reader.GetString(8),
_d4Percent = reader.GetString(9),
_specialDiscount = reader.GetString(10),
_spePercent = reader.GetString(11),
_otherCharges = reader.GetString(12),
_othPercent = reader.GetString(13),
_freight = reader.GetString(14),
_freiPercent = reader.GetString(15),
_costAndDelivery = reader.GetString(16),
_cadPercent = reader.GetString(17),
_vat = reader.GetString(18),
_vatPercent = reader.GetString(19),
_netAmount = reader.GetString(20),
_selected = reader.GetString(21)
});
getTotalDisc();
getTotalAddOns();
}
}
return(cQuoteSupplierList);
}
public List <cSupplierPerCost> getitemSupplierCost()
{
cMySQLCommands cMySQLCom = new cMySQLCommands();
MySqlDataReader reader;
cMySQLCom.addFields("quote_line.sup_code");
cMySQLCom.addFields("quote_line.itm_code");
cMySQLCom.addFields("item_mst.itm_desc");
cMySQLCom.addFields("item_mst.itm_specs");
cMySQLCom.addFields("quote_line.itm_qty");
cMySQLCom.addFields("quote_line.itm_uom");
cMySQLCom.addFields("quote_line.itm_cost");
cMySQLCom.addFields("quote_line.itm_disc");
cMySQLCom.addFields("quote_line.itm_freight");
cMySQLCom.addFields("quote_line.itm_vat");
cMySQLCom.addFields("quote_line.itm_net");
cMySQLCom._tblName = "quote_line";
cMySQLCom._innerTableName = "item_mst";
cMySQLCom._innerLeftOn = "quote_line.itm_code";
cMySQLCom._innerRightOn = "item_mst.itm_code";
cMySQLCom._condition = " quote_line.quote_no = '" + _quoteno + "' and quote_line.quote_loccd = '" + _locCode + "'";
reader = cMySQLCom.innerSelectQueryWhere();
if (reader.HasRows)
{
while (reader.Read())
{
cSupplierItemPerCostList.Add(new cSupplierPerCost
{
_supplierCode = reader.GetString(0),
_itmCode = reader.GetString(1),
_description = reader.GetString(2),
_details = reader.GetString(3),
_quantity = reader.GetDouble(4),
_uom = reader.GetString(5),
_unitCost = reader.GetDouble(6),
_discount = reader.GetDouble(7),
_freight = reader.GetDouble(8),
_vat = reader.GetDouble(9),
_netTotal = reader.GetDouble(10),
});
}
}
return(cSupplierItemPerCostList);
}
public void saveDetails(List <cSupplierPerCost> cSupplierCostList, string selQuoteSupplier)
{
cMySQLCommands cMySQLComDel = new cMySQLCommands();
cMySQLCommands cMySQLCom = new cMySQLCommands();
cMySQLCom._tblName = "quote_line";
cMySQLCom.addFields("quote_no");
cMySQLCom.addFields("itm_code");
cMySQLCom.addFields("itm_qty");
cMySQLCom.addFields("itm_uom");
cMySQLCom.addFields("itm_cost");
cMySQLCom.addFields("itm_disc");
cMySQLCom.addFields("itm_freight");
cMySQLCom.addFields("itm_vat");
cMySQLCom.addFields("itm_net");
cMySQLCom.addFields("sup_code");
cMySQLCom.addFields("quote_loccd");
cMySQLCom.addFields("selected");
if (cSupplierCostList.Count > 0)
{
foreach (cSupplierPerCost cSupPerCost in cSupplierCostList)
{
cMySQLCom._values = "";
cMySQLCom.addValues(_quoteno);
cMySQLCom.addValues(cSupPerCost._itmCode);
cMySQLCom.addValues(Convert.ToString(cSupPerCost._quantity));
cMySQLCom.addValues(cSupPerCost._uom);
cMySQLCom.addValues(Convert.ToString(cSupPerCost._unitCost));
cMySQLCom.addValues(Convert.ToString(cSupPerCost._discount));
cMySQLCom.addValues(Convert.ToString(cSupPerCost._freight));
cMySQLCom.addValues(Convert.ToString(cSupPerCost._vat));
cMySQLCom.addValues(Convert.ToString(cSupPerCost._netTotal).Replace(",", ""));
cMySQLCom.addValues(Convert.ToString(cSupPerCost._supplierCode));
cMySQLCom.addValues(_locCode);
if (cSupPerCost._supplierCode == selQuoteSupplier)
{
cMySQLCom.addValues("True");
}
else
{
cMySQLCom.addValues("False");
}
cMySQLCom.insertQuery();
}
}
}