public List <So> BuyerGetSoAccordingToFilter(int userId, bool includedSubs, string filterColumn, string filterString, List <int> states)
{
List <So> soList = new List <So>();
if (states.Count == 0)
{
return(soList);
}
List <int> buyersIds = new List <int>();
if (includedSubs)
{
AmbleAppServer.AccountMgr.AccountMgr accountMgr = new AccountMgr.AccountMgr();
buyersIds.AddRange(accountMgr.GetAllSubsId(userId));
}
else
{
buyersIds.Add(userId);
}
StringBuilder sb = new StringBuilder();
sb.Append(string.Format("select soId from So s,rfq r where(s.rfqId=r.rfqNo) and ( (r.firstPA={0} or r.secondPa={0})", buyersIds[0]));
for (int i = 1; i < buyersIds.Count; i++)
{
sb.Append(string.Format(" or (firstPA={0} or secondPA={0}) ", buyersIds[i]));
}
sb.Append(" ) ");
//append the filter
if ((!string.IsNullOrWhiteSpace(filterColumn)) && (!string.IsNullOrWhiteSpace(filterString)))
{
sb.Append(string.Format(" and {0} like '%{1}%' ", filterColumn, filterString));
}
sb.Append(" and (soStates=" + states[0]);
for (int i = 1; i < states.Count; i++)
{
sb.Append(" or soStates=" + states[i]);
}
sb.Append(" )");
DataTable dt = db.GetDataTable(sb.ToString(), "soId");
foreach (DataRow dr in dt.Rows)
{
soList.Add(GetSoAccordingToSoId(Convert.ToInt32(dr["soId"])));
}
return(soList);
}
public bool AddRfqHistory(int rfqId,int who, string action)
{
string strSql = "select routingHistory from rfq where rfqNo=" + rfqId;
string history = db.GetSingleObject(strSql).ToString();
AccountMgr.AccountMgr accountMgr=new AccountMgr.AccountMgr();
history = DateTime.Now.ToString() + ": " + accountMgr.GetNameById(who) + " " + action+System.Environment.NewLine+history;
strSql = string.Format("update rfq set routingHistory='{0}'", history);
int row = db.ExecDataBySql(strSql);
if (row == 1)
return true;
else
return false;
}
//for sales manager
public int GetThePageCountOfDataTable(int itemsPerPage, int salesId, string filterColumn, string filterString, List <RfqStatesEnum> selections)
{
int count = 0;
//get the subs IDs include himself
AmbleAppServer.AccountMgr.AccountMgr accountMgr = new AccountMgr.AccountMgr();
List <int> subIds = accountMgr.GetAllSubsId(salesId);
foreach (int id in subIds)
{
count += GetThePageCountOfDataTablePerSale(itemsPerPage, id, filterColumn, filterString, selections);
}
return(count);
}
public List<So> BuyerGetSoAccordingToFilter(int userId, bool includedSubs, string filterColumn, string filterString, List<int> states)
{
List<So> soList = new List<So>();
if (states.Count == 0) return soList;
List<int> buyersIds = new List<int>();
if (includedSubs)
{
AmbleAppServer.AccountMgr.AccountMgr accountMgr = new AccountMgr.AccountMgr();
buyersIds.AddRange(accountMgr.GetAllSubsId(userId));
}
else
{
buyersIds.Add(userId);
}
StringBuilder sb = new StringBuilder();
sb.Append(string .Format("select soId from So s,rfq r where(s.rfqId=r.rfqNo) and ( (r.firstPA={0} or r.secondPa={0})",buyersIds[0]));
for (int i = 1; i < buyersIds.Count; i++)
{
sb.Append(string.Format(" or (firstPA={0} or secondPA={0}) ",buyersIds[i]));
}
sb.Append(" ) ");
//append the filter
if ((!string.IsNullOrWhiteSpace(filterColumn)) && (!string.IsNullOrWhiteSpace(filterString)))
{
sb.Append(string.Format(" and {0} like '%{1}%' ", filterColumn, filterString));
}
sb.Append(" and (soStates=" + states[0]);
for (int i = 1; i < states.Count; i++)
{
sb.Append(" or soStates=" + states[i]);
}
sb.Append(" )");
DataTable dt = db.GetDataTable(sb.ToString(), "soId");
foreach (DataRow dr in dt.Rows)
{
soList.Add(GetSoAccordingToSoId(Convert.ToInt32(dr["soId"])));
}
return soList;
}
public bool AddRfqHistory(int rfqId, int who, string action)
{
string strSql = "select routingHistory from rfq where rfqNo=" + rfqId;
string history = db.GetSingleObject(strSql).ToString();
AccountMgr.AccountMgr accountMgr = new AccountMgr.AccountMgr();
history = DateTime.Now.ToString() + ": " + accountMgr.GetNameById(who) + " " + action + System.Environment.NewLine + history;
strSql = string.Format("update rfq set routingHistory='{0}'", history);
int row = db.ExecDataBySql(strSql);
if (row == 1)
{
return(true);
}
else
{
return(false);
}
}
//for sale and salesManager
public DataTable GetICanSeeRfqDataTableAccordingToPageNumber(int salesId, int pageNumber, int itemsPerPage, string filterColumn, string filterString, List <RfqStatesEnum> selections)
{
if (selections.Count == 0)
{
return(null);
}
AmbleAppServer.AccountMgr.AccountMgr accountMgr = new AccountMgr.AccountMgr();
List <int> subIds = accountMgr.GetAllSubsId(salesId);
StringBuilder sb = new StringBuilder();
if ((!string.IsNullOrEmpty(filterColumn)) && (!(string.IsNullOrEmpty(filterString))))
{
sb.Append(string.Format("select * from rfq where {0} like '%{1}%' and ( salesId={2}", filterColumn, filterString, subIds[0]));
}
else
{
sb.Append("select * from rfq where ( salesId=" + subIds[0]);
}
if (subIds.Count() > 1)
{
for (int i = 1; i < subIds.Count(); i++)
{
sb.Append(" or salesId=" + subIds[i]);
}
}
sb.Append(" )");
sb.Append(" and (rfqstates=" + selections[0].GetHashCode());
for (int i = 1; i < selections.Count; i++)
{
sb.Append(" or rfqstates=" + selections[i].GetHashCode());
}
sb.Append(string.Format(") limit {0},{1}", pageNumber * itemsPerPage, itemsPerPage));
return(db.GetDataTable(sb.ToString(), "Table" + pageNumber));
}
public DataTable GetTheCustomersOrVendorsICanSee(int cvtype, int id)
{
//get the subs IDs include himself
AmbleAppServer.AccountMgr.AccountMgr accountMgr = new AccountMgr.AccountMgr();
List <int> subIds = accountMgr.GetAllSubsId(id);
List <string> sqlCodes = new List <string>();
DataTable theCustomerOrVendorICanSee = new DataTable();
foreach (int subId in subIds)
{
string strSql = "select * from custVendor where cvtype=" + cvtype + " and ownerName=" + subId;
sqlCodes.Add(strSql);
}
//get the whole datatable
return(db.GetDataTable(sqlCodes, "custVendor"));
}
//for sales manager
public int GetThePageCountOfDataTable(int itemsPerPage, int salesId, string filterColumn, string filterString, List<RfqStatesEnum> selections)
{
int count=0;
//get the subs IDs include himself
AmbleAppServer.AccountMgr.AccountMgr accountMgr = new AccountMgr.AccountMgr();
List<int> subIds = accountMgr.GetAllSubsId(salesId);
foreach (int id in subIds)
{
count += GetThePageCountOfDataTablePerSale(itemsPerPage, id, filterColumn,filterString,selections);
}
return count;
}
//for sale and salesManager
public DataTable GetICanSeeRfqDataTableAccordingToPageNumber(int salesId, int pageNumber, int itemsPerPage, string filterColumn, string filterString, List<RfqStatesEnum> selections)
{
if (selections.Count == 0)
return null;
AmbleAppServer.AccountMgr.AccountMgr accountMgr = new AccountMgr.AccountMgr();
List<int> subIds = accountMgr.GetAllSubsId(salesId);
StringBuilder sb = new StringBuilder();
if ((!string.IsNullOrEmpty(filterColumn)) && (!(string.IsNullOrEmpty(filterString))))
{
sb.Append(string.Format("select * from rfq where {0} like '%{1}%' and ( salesId={2}",filterColumn,filterString,subIds[0]));
}
else
{
sb.Append("select * from rfq where ( salesId=" + subIds[0]);
}
if (subIds.Count() > 1)
{
for (int i = 1; i < subIds.Count(); i++)
sb.Append(" or salesId=" + subIds[i]);
}
sb.Append(" )");
sb.Append(" and (rfqstates=" + selections[0].GetHashCode());
for (int i = 1; i < selections.Count; i++)
{
sb.Append(" or rfqstates=" + selections[i].GetHashCode());
}
sb.Append(string.Format(") limit {0},{1}", pageNumber*itemsPerPage, itemsPerPage));
return db.GetDataTable(sb.ToString(), "Table" + pageNumber);
}
public DataTable GetTheCustomersOrVendorsICanSee(int cvtype,int id)
{
//get the subs IDs include himself
AmbleAppServer.AccountMgr.AccountMgr accountMgr = new AccountMgr.AccountMgr();
List<int> subIds = accountMgr.GetAllSubsId(id);
List<string> sqlCodes = new List<string>();
DataTable theCustomerOrVendorICanSee = new DataTable();
foreach (int subId in subIds)
{
string strSql = "select * from custVendor where cvtype=" + cvtype + " and ownerName=" + subId;
sqlCodes.Add(strSql);
}
//get the whole datatable
return db.GetDataTable(sqlCodes, "custVendor");
}