public decimal GetTotalPaymentsOf(string srDsrId, string companyId, DateTime paymentDate)
{
DbCommand comm = null;
decimal totalPayment = 0;
try
{
comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"SELECT TotalPayment FROM IM_SR_DSR_PAYMENT_DETAILS WHERE SrId = @SrId AND CompanyId = @CompanyId AND PaymentDate = @PaymentDate";
CreateParameter.AddParam(comm, "@SrId", srDsrId, DbType.String);
CreateParameter.AddParam(comm, "@CompanyId", companyId, DbType.String);
CreateParameter.AddParam(comm, "@PaymentDate", paymentDate.Date, DbType.DateTime);
DbDataReader dr = GenericDataAccess.ExecuteQuery(comm);
if (dr.Read())
{
totalPayment = NullHandler.GetDecimal(dr["TotalPayment"]);
}
}
catch (Exception ex)
{
}
finally
{
if (comm != null && comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
}
return(totalPayment);
}
public List <Company> GetAllCompany()
{
DbCommand comm = null;
List <Company> companies = new List <Company>();
try
{
comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"SELECT * FROM IM_Company ORDER BY CompanyName";
DbDataReader dr = GenericDataAccess.ExecuteQuery(comm);
while (dr.Read())
{
companies.Add(MapCompany(dr));
}
}
catch (Exception ex)
{
}
finally
{
if (comm != null && comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
}
return(companies);
}
public List <DetailPaymentInfo> GetAllPaymentsOfSrDsr(string srDsrId, string companyId, DateTime fromDate, DateTime toDate)
{
DbCommand comm = null;
List <DetailPaymentInfo> detailPayments = new List <DetailPaymentInfo>();
try
{
comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"SELECT * FROM IM_SR_DSR_PAYMENT_DETAILS WHERE SrId = @SrId AND CompanyId = @CompanyId AND PaymentDate BETWEEN @FromDate AND @ToDate ORDER BY PaymentDate desc";
CreateParameter.AddParam(comm, "@SrId", srDsrId, DbType.String);
CreateParameter.AddParam(comm, "@CompanyId", companyId, DbType.String);
CreateParameter.AddParam(comm, "@FromDate", fromDate.Date, DbType.DateTime);
CreateParameter.AddParam(comm, "@ToDate", toDate.Date, DbType.DateTime);
DbDataReader dr = GenericDataAccess.ExecuteQuery(comm);
while (dr.Read())
{
detailPayments.Add(MapPayment(dr));
}
}
catch (Exception ex)
{
}
finally
{
if (comm != null && comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
}
return(detailPayments);
}
public List <SRDSRDue> GetAllSrDsrWithDues()
{
DbCommand comm = null;
List <SRDSRDue> srDsrs = new List <SRDSRDue>();
try
{
comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"SELECT * FROM IM_SR_DSR srDsr LEFT JOIN IM_SR_DSR_ORDER_DUE srDsrDue ON srDsr.Id = srDsrDue.SrId ORDER BY Name";
DbDataReader dr = GenericDataAccess.ExecuteQuery(comm);
while (dr.Read())
{
srDsrs.Add(MapSrDsrWithDue(dr));
}
}
catch (Exception ex)
{
}
finally
{
if (comm != null && comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
}
return(srDsrs);
}
public SRDSRDue GetSrDsrDue(string Id)
{
DbCommand comm = null;
SRDSRDue srDsrDue = null;
try
{
comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"SELECT * FROM IM_SR_DSR_ORDER_DUE WHERE SrId = @SrId";
CreateParameter.AddParam(comm, "@SrId", Id, DbType.String);
DbDataReader dr = GenericDataAccess.ExecuteQuery(comm);
if (dr.Read())
{
srDsrDue = MapSrDsrDue(dr);
}
}
catch (Exception ex)
{
}
finally
{
if (comm != null && comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
}
return(srDsrDue);
}
public List <Market> GetAllMarket()
{
DbCommand comm = null;
List <Market> market = new List <Market>();
try
{
comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"SELECT * FROM IM_Markets ORDER BY Name";
DbDataReader dr = GenericDataAccess.ExecuteQuery(comm);
while (dr.Read())
{
market.Add(MapMarket(dr));
}
}
catch (Exception ex)
{
}
finally
{
if (comm != null && comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
}
return(market);
}
public List <SRDSR> GetAllSrDsr()
{
DbCommand comm = null;
List <SRDSR> srDsrs = new List <SRDSR>();
try
{
comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"SELECT * FROM IM_SR_DSR";
DbDataReader dr = GenericDataAccess.ExecuteQuery(comm);
while (dr.Read())
{
srDsrs.Add(MapSrDsr(dr));
}
}
catch (Exception ex)
{
}
finally
{
if (comm != null && comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
}
return(srDsrs);
}
public List <ItemOrder> GetOrderTemplate(string companyId)
{
DbCommand comm = null;
List <ItemOrder> itemsOrder = new List <ItemOrder>();
try
{
comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"SELECT itm.CompanyId, itm.ItemId, itm.ItemName, itm.CountPerBox, itm.Price, itms.StockId, itms.TotalStock, itms.DamagedStock, itms.ChalanNo, itms.StockEntryDate
FROM IM_Items itm LEFT JOIN IM_Items_Stock itms
ON itm.ItemId = itms.ItemId
WHERE itm.CompanyId = @CompanyId ORDER BY ItemName";
CreateParameter.AddParam(comm, "@CompanyId", companyId, DbType.String);
DbDataReader dr = GenericDataAccess.ExecuteQuery(comm);
while (dr.Read())
{
itemsOrder.Add(MapItemOrderTemplate(dr));
}
}
catch (Exception ex)
{
}
finally
{
if (comm != null && comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
}
return(itemsOrder);
}
public List <Item> GetAllItems(string companyId)
{
DbCommand comm = null;
List <Item> items = new List <Item>();
try
{
comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"SELECT * FROM IM_Items WHERE CompanyId = @CompanyId ORDER BY ItemName";
CreateParameter.AddParam(comm, "@CompanyId", companyId, DbType.String);
DbDataReader dr = GenericDataAccess.ExecuteQuery(comm);
while (dr.Read())
{
items.Add(MapItem(dr));
}
}
catch (Exception ex)
{
}
finally
{
if (comm != null && comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
}
return(items);
}
public List <ItemStock> GetAllStockItem(string companyId, string stockItemName)
{
DbCommand comm = null;
List <ItemStock> stockItems = new List <ItemStock>();
try
{
comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
string sql = @"SELECT itm.CompanyId, itm.ItemId, itm.ItemName, itm.CountPerBox, itms.StockId, itms.TotalStock, itms.DamagedStock, itms.ChalanNo, itms.StockEntryDate
FROM IM_Items itm LEFT JOIN IM_Items_Stock itms
ON itm.ItemId = itms.ItemId
WHERE CompanyId = @CompanyId";
if (!string.IsNullOrEmpty(stockItemName))
{
sql += " AND ItemName LIKE @ItemName";
CreateParameter.AddParam(comm, "@ItemName", stockItemName + "%", DbType.String);
}
string orderBySQL = "ORDER BY ItemName";
comm.CommandText = string.Format("{0} {1}", sql, orderBySQL);
CreateParameter.AddParam(comm, "@CompanyId", companyId, DbType.String);
DbDataReader dr = GenericDataAccess.ExecuteQuery(comm);
while (dr.Read())
{
stockItems.Add(MapStockItem(dr));
}
}
catch (Exception ex)
{
}
finally
{
if (comm != null && comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
}
return(stockItems);
}
public List <Chalan> GetAllChalan(string companyId, DateTime fromDate, DateTime toDate, string chalanNo = "")
{
DbCommand comm = null;
List <Chalan> chalans = new List <Chalan>();
try
{
comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
string sql = @"SELECT ca.*, itms.ItemName, itms.CountPerBox FROM IM_Chalan_Activity ca INNER JOIN IM_Items itms ON ca.ItemId = itms.ItemId";
string whereSql = "WHERE itms.CompanyId = @CompanyId AND ChalanDate BETWEEN @FromDate AND @ToDate";
if (!string.IsNullOrEmpty(chalanNo))
{
whereSql = CreateParameter.CreateWhereClause(whereSql, "ca.ChalanNo LIKE @ChalanNo");
CreateParameter.AddParam(comm, "@ChalanNo", chalanNo + "%", DbType.String);
}
string orderBySql = "ORDER BY ChalanDate desc";
sql = string.Format("{0} {1} {2}", sql, whereSql, orderBySql);
comm.CommandText = sql;
CreateParameter.AddParam(comm, "@CompanyId", companyId, DbType.String);
CreateParameter.AddParam(comm, "@FromDate", fromDate.Date, DbType.Date);
CreateParameter.AddParam(comm, "@ToDate", toDate.Date, DbType.Date);
DbDataReader dr = GenericDataAccess.ExecuteQuery(comm);
while (dr.Read())
{
chalans.Add(MapChalan(dr));
}
}
catch (Exception ex)
{
}
finally
{
if (comm != null && comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
}
return(chalans);
}
public List <User> GetAllUsers()
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"SELECT * FROM IM_USERS ORDER BY USER_NAME";
DbDataReader dr = GenericDataAccess.ExecuteQuery(comm);
List <User> users = new List <User>();
while (dr.Read())
{
users.Add(Mapobject(dr));
}
if (comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
return(users);
}
public List <ItemOrder> GetAllOrders(string companyId, string srDsrId, string marketId, DateTime filterDate)
{
DbCommand comm = null;
List <ItemOrder> itemsOrder = new List <ItemOrder>();
try
{
comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"SELECT ord.OrderId, ord.SrId, ord.MarketId, ord.Date, ord.OrderCount, ord.ReturnCount, ord.DamagedCount, ord.SoldCount, itm.CompanyId, itm.ItemId, itm.ItemName, itm.CountPerBox, itm.Price, itms.StockId, itms.TotalStock, itms.DamagedStock, itms.ChalanNo, itms.StockEntryDate
FROM IM_Items itm LEFT JOIN IM_Items_Stock itms
ON itm.ItemId = itms.ItemId
LEFT JOIN IM_Orders ord ON itm.ItemId = ord.ItemId
WHERE itm.CompanyId = @CompanyId AND ord.SrId = @SrId AND ord.MarketId = @MarketId AND ord.Date = @Date ORDER BY ItemName";
CreateParameter.AddParam(comm, "@CompanyId", companyId, DbType.String);
CreateParameter.AddParam(comm, "@SrId", srDsrId, DbType.String);
CreateParameter.AddParam(comm, "@MarketId", marketId, DbType.String);
CreateParameter.AddParam(comm, "@Date", filterDate.Date, DbType.Date);
DbDataReader dr = GenericDataAccess.ExecuteQuery(comm);
while (dr.Read())
{
itemsOrder.Add(MapItemOrder(dr));
}
}
catch (Exception ex)
{
}
finally
{
if (comm != null && comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
}
return(itemsOrder);
}
public User AuthenticateUser(string userId, string passwordAsPlainText)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"SELECT * FROM IM_USERS WHERE USER_ID=@USER_ID AND PASSWORD=@PASSWORD";
CreateParameter.AddParam(comm, "@USER_ID", userId, DbType.String);
CreateParameter.AddParam(comm, "@PASSWORD", passwordAsPlainText, DbType.String);
DbDataReader dr = GenericDataAccess.ExecuteQuery(comm);
User user = null;
if (dr.Read())
{
user = Mapobject(dr);
}
if (comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
return(user);
}
