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); }