public SavingState DeletePayment(string paymentId)
{
SavingState svState = SavingState.Failed;
DbCommand comm = null;
try
{
comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = "DELETE FROM IM_SR_DSR_PAYMENT_DETAILS WHERE PaymentId=@PaymentId";
CreateParameter.AddParam(comm, "@PaymentId", paymentId, DbType.String);
GenericDataAccess.ExecuteNonQuery(comm);
comm.Parameters.Clear();
svState = SavingState.Success;
}
catch (Exception ex)
{
if (ex.Message.ToLower().Contains("duplicate key"))
{
svState = SavingState.DuplicateExists;
}
}
finally
{
if (comm != null && comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
}
return(svState);
}
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 bool IsItemExistInStock(string itemId, DbCommand command = null, bool isTransection = false)
{
bool ret = false;
DbCommand comm = null;
if (!isTransection)
{
comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
}
else
{
comm = command;
}
comm.CommandText = @"Select count(StockId) From IM_Items_Stock WHERE ItemId = @ItemId";
CreateParameter.AddParam(comm, "@ItemId", itemId, DbType.String);
string strRet = GenericDataAccess.ExecuteScalar(comm);
if (Convert.ToInt16(strRet) > 0)
{
ret = true;
}
if (!isTransection)
{
GenericDataAccess.CloseDBConnection(comm);
}
return(ret);
}
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 <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 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 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 SavingState DeleteAllItemsStockOfCompany(string companyId)
{
SavingState svState = SavingState.Failed;
DbCommand comm = null;
try
{
comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = "DELETE FROM IM_Items_Stock WHERE ItemId IN (SELECT ItemId FROM IM_Items WHERE CompanyId = @CompanyId)";
CreateParameter.AddParam(comm, "@CompanyId", companyId, DbType.String);
GenericDataAccess.ExecuteNonQuery(comm);
comm.Parameters.Clear();
svState = SavingState.Success;
}
catch (Exception ex)
{
if (ex.Message.ToLower().Contains("duplicate key"))
{
svState = SavingState.DuplicateExists;
}
}
finally
{
if (comm != null && comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
}
return(svState);
}
public SavingState SaveSrPaymentDetails(DetailPaymentInfo paymentInfo)
{
SavingState svState = SavingState.Failed;
/// paymentInfo.Id is SrId
if (paymentInfo != null && !string.IsNullOrEmpty(paymentInfo.Id) && paymentInfo.PaymentDate != null && !paymentInfo.PaymentDate.Equals(DateTime.MinValue))
{
DbCommand thisCommand = null;
try
{
thisCommand = GenericDataAccess.CreateCommand();
thisCommand.CommandType = CommandType.Text;
/// if new payment
if (!IsPaymentInfoExist(paymentInfo.Id, paymentInfo.CompanyId, paymentInfo.PaymentDate))
{
thisCommand.CommandText = "INSERT INTO IM_SR_DSR_PAYMENT_DETAILS (PaymentId, SrId, CompanyId, PaymentDate, ThousendCount, FiveHundredCount, OneHundredCount, FiftyCount, TwentyCount, TenCount, FiveCount, TwoCount, OneCount, TotalPayment) VALUES(@PaymentId, @SrId, @CompanyId, @PaymentDate, @ThousendCount, @FiveHundredCount, @OneHundredCount, @FiftyCount, @TwentyCount, @TenCount, @FiveCount, @TwoCount, @OneCount, @TotalPayment)";
CreateParameter.AddParam(thisCommand, "@PaymentId", Guid.NewGuid().ToString(), DbType.String);
}
else
{
thisCommand.CommandText = "UPDATE IM_SR_DSR_PAYMENT_DETAILS SET PaymentDate = @PaymentDate, ThousendCount = @ThousendCount, FiveHundredCount = @FiveHundredCount, OneHundredCount = @OneHundredCount, FiftyCount = @FiftyCount, TwentyCount = @TwentyCount, TenCount = @TenCount, FiveCount = @FiveCount, TwoCount = @TwoCount, OneCount = @OneCount, TotalPayment = @TotalPayment WHERE SrId = @SrId AND CompanyId = @CompanyId AND PaymentDate = @PaymentDate";
}
CreateParameter.AddParam(thisCommand, "@SrId", paymentInfo.Id, DbType.String);
CreateParameter.AddParam(thisCommand, "@CompanyId", paymentInfo.CompanyId, DbType.String);
CreateParameter.AddParam(thisCommand, "@PaymentDate", paymentInfo.PaymentDate, DbType.DateTime);
CreateParameter.AddParam(thisCommand, "@ThousendCount", paymentInfo.ThousendCount, DbType.Int32);
CreateParameter.AddParam(thisCommand, "@FiveHundredCount", paymentInfo.FiveHundredCount, DbType.Int32);
CreateParameter.AddParam(thisCommand, "@OneHundredCount", paymentInfo.OneHundredCount, DbType.Int32);
CreateParameter.AddParam(thisCommand, "@FiftyCount", paymentInfo.FiftyCount, DbType.Int32);
CreateParameter.AddParam(thisCommand, "@TwentyCount", paymentInfo.TwentyCount, DbType.Int32);
CreateParameter.AddParam(thisCommand, "@TenCount", paymentInfo.TenCount, DbType.Int32);
CreateParameter.AddParam(thisCommand, "@FiveCount", paymentInfo.FiveCount, DbType.Int32);
CreateParameter.AddParam(thisCommand, "@TwoCount", paymentInfo.TwoCount, DbType.Int32);
CreateParameter.AddParam(thisCommand, "@OneCount", paymentInfo.OneCount, DbType.Int32);
CreateParameter.AddParam(thisCommand, "@TotalPayment", Math.Round(paymentInfo.TotalPayment, 2), DbType.Decimal);
GenericDataAccess.ExecuteNonQuery(thisCommand);
thisCommand.Parameters.Clear();
svState = SavingState.Success;
}
catch (Exception ex)
{
if (ex.Message.ToLower().Contains("duplicate key"))
{
svState = SavingState.DuplicateExists;
}
}
finally
{
if (thisCommand != null && thisCommand.Connection.State != ConnectionState.Closed)
{
thisCommand.Connection.Close();
}
}
}
return(svState);
}
public SavingState SaveItem(Item item)
{
SavingState svState = SavingState.Failed;
if (!string.IsNullOrEmpty(item.CompanyId) && !string.IsNullOrEmpty(item.ItemName))
{
DbCommand thisCommand = null;
try
{
thisCommand = GenericDataAccess.CreateCommand();
thisCommand.CommandType = CommandType.Text;
/// if new Item
if (string.IsNullOrEmpty(item.ItemId))
{
if (!IsItemExist(item.CompanyId, item.ItemName))
{
thisCommand.CommandText = "INSERT INTO IM_Items (CompanyId, ItemId, ItemName, CountPerBox, Price) VALUES(@CompanyId, @ItemId, @ItemName, @CountPerBox, @Price)";
CreateParameter.AddParam(thisCommand, "@ItemId", Guid.NewGuid().ToString(), DbType.String);
}
else
{
return(SavingState.DuplicateExists);
}
}
else
{
thisCommand.CommandText = "UPDATE IM_Items SET CompanyId = @CompanyId, ItemName = @ItemName, CountPerBox = @CountPerBox, Price = @Price WHERE ItemId = @ItemId";
CreateParameter.AddParam(thisCommand, "@ItemId", item.ItemId, DbType.String);
}
CreateParameter.AddParam(thisCommand, "@CompanyId", item.CompanyId, DbType.String);
CreateParameter.AddParam(thisCommand, "@ItemName", item.ItemName, DbType.String);
CreateParameter.AddParam(thisCommand, "@CountPerBox", item.CountPerBox, DbType.Int32);
CreateParameter.AddParam(thisCommand, "@Price", item.Price, DbType.Double);
GenericDataAccess.ExecuteNonQuery(thisCommand);
thisCommand.Parameters.Clear();
svState = SavingState.Success;
}
catch (Exception ex)
{
if (ex.Message.ToLower().Contains("duplicate key"))
{
svState = SavingState.DuplicateExists;
}
}
finally
{
if (thisCommand != null && thisCommand.Connection.State != ConnectionState.Closed)
{
thisCommand.Connection.Close();
}
}
}
return(svState);
}
public SavingState SaveSrDsr(SRDSR srDsr)
{
SavingState svState = SavingState.Failed;
if (!string.IsNullOrEmpty(srDsr.Name) && !string.IsNullOrEmpty(srDsr.CellNo))
{
DbCommand thisCommand = null;
try
{
thisCommand = GenericDataAccess.CreateCommand();
thisCommand.CommandType = CommandType.Text;
/// if new sr
if (string.IsNullOrEmpty(srDsr.Id))
{
if (!IsSRDSRExist(srDsr.Name, srDsr.CellNo))
{
thisCommand.CommandText = "INSERT INTO IM_SR_DSR (Id, Name, Type, CellNo) VALUES(@Id, @Name, @Type, @CellNo)";
CreateParameter.AddParam(thisCommand, "@Id", Guid.NewGuid().ToString(), DbType.String);
}
else
{
return(SavingState.DuplicateExists);
}
}
else
{
thisCommand.CommandText = "UPDATE IM_SR_DSR SET Name = @Name, Type = @Type, CellNo = @CellNo WHERE Id = @Id";
CreateParameter.AddParam(thisCommand, "@Id", srDsr.Id, DbType.String);
}
CreateParameter.AddParam(thisCommand, "@Name", srDsr.Name, DbType.String);
CreateParameter.AddParam(thisCommand, "@Type", (int)srDsr.Type, DbType.Int32);
CreateParameter.AddParam(thisCommand, "@CellNo", srDsr.CellNo, DbType.String);
GenericDataAccess.ExecuteNonQuery(thisCommand);
thisCommand.Parameters.Clear();
svState = SavingState.Success;
}
catch (Exception ex)
{
if (ex.Message.ToLower().Contains("duplicate key"))
{
svState = SavingState.DuplicateExists;
}
}
finally
{
if (thisCommand != null && thisCommand.Connection.State != ConnectionState.Closed)
{
thisCommand.Connection.Close();
}
}
}
return(svState);
}
public SavingState SaveCompany(Company company)
{
SavingState svState = SavingState.Failed;
if (!string.IsNullOrEmpty(company.companyName))
{
DbCommand thisCommand = null;
try
{
thisCommand = GenericDataAccess.CreateCommand();
thisCommand.CommandType = CommandType.Text;
/// if new company
if (string.IsNullOrEmpty(company.companyId))
{
if (!IsCompanyExist(company.companyName))
{
thisCommand.CommandText = "INSERT INTO IM_Company (CompanyId, CompanyName) VALUES(@CompanyId, @CompanyName)";
CreateParameter.AddParam(thisCommand, "@CompanyId", Guid.NewGuid().ToString(), DbType.String);
}
else
{
return(SavingState.DuplicateExists);
}
}
else
{
thisCommand.CommandText = "UPDATE IM_Company SET CompanyName = @CompanyName WHERE CompanyId = @CompanyId";
CreateParameter.AddParam(thisCommand, "@CompanyId", company.companyId, DbType.String);
}
CreateParameter.AddParam(thisCommand, "@CompanyName", company.companyName, DbType.String);
GenericDataAccess.ExecuteNonQuery(thisCommand);
thisCommand.Parameters.Clear();
svState = SavingState.Success;
}
catch (Exception ex)
{
if (ex.Message.ToLower().Contains("duplicate key"))
{
svState = SavingState.DuplicateExists;
}
}
finally
{
if (thisCommand != null && thisCommand.Connection.State != ConnectionState.Closed)
{
thisCommand.Connection.Close();
}
}
}
return(svState);
}
public SavingState SaveChalan(Chalan chalan)
{
SavingState svState = SavingState.Failed;
if (!string.IsNullOrEmpty(chalan.ChalanNo))
{
DbCommand thisCommand = null;
try
{
thisCommand = GenericDataAccess.CreateCommand();
thisCommand.CommandType = CommandType.Text;
/// if new chalan
if (!IsItemChalanExist(chalan.ChalanNo, chalan.ItemId, chalan.ChalanDate))
{
thisCommand.CommandText = "INSERT INTO IM_Chalan_Activity (ChalanId, ChalanNo, ItemId, EntryCount, ChalanDate) VALUES(@ChalanId, @ChalanNo, @ItemId, @EntryCount, @ChalanDate)";
CreateParameter.AddParam(thisCommand, "@ChalanId", Guid.NewGuid().ToString(), DbType.String);
}
else
{
thisCommand.CommandText = "UPDATE IM_Chalan_Activity SET EntryCount = @EntryCount WHERE ItemId = @ItemId AND ChalanNo = @ChalanNo AND ChalanDate = @ChalanDate";
}
CreateParameter.AddParam(thisCommand, "@ChalanNo", chalan.ChalanNo, DbType.String);
CreateParameter.AddParam(thisCommand, "@ItemId", chalan.ItemId, DbType.String);
CreateParameter.AddParam(thisCommand, "@EntryCount", chalan.EntryCount, DbType.Int32);
CreateParameter.AddParam(thisCommand, "@ChalanDate", chalan.ChalanDate.Date, DbType.Date);
GenericDataAccess.ExecuteNonQuery(thisCommand);
thisCommand.Parameters.Clear();
svState = SavingState.Success;
}
catch (Exception ex)
{
if (ex.Message.ToLower().Contains("duplicate key"))
{
svState = SavingState.DuplicateExists;
}
}
finally
{
if (thisCommand != null && thisCommand.Connection.State != ConnectionState.Closed)
{
thisCommand.Connection.Close();
}
}
}
return(svState);
}
public MerkServiceResponse GetMerkById(string id)
{
MerkServiceResponse response = new MerkServiceResponse();
Merk merk = _merkRepository.GetById(id);
if (merk.Id == null)
{
response.Messages.Add(new Message("Data is not in Database"));
}
else
{
CreateParameter param = new CreateParameter(merk.Id, merk.Code, merk.Name, merk.Manufacture);
response.MerkDomain = MerkDomain.Create(param);
}
return(response);
}
public SavingState SaveSrDsrDue(SRDSRDue srDsrDue)
{
SavingState svState = SavingState.Failed;
if (!string.IsNullOrEmpty(srDsrDue.Id))
{
DbCommand thisCommand = null;
try
{
thisCommand = GenericDataAccess.CreateCommand();
thisCommand.CommandType = CommandType.Text;
/// if new sr
if (!IsSRDSRDueExist(srDsrDue.Id))
{
thisCommand.CommandText = "INSERT INTO IM_SR_DSR_ORDER_DUE (SrId, TotalDue) VALUES(@SrId, @TotalDue)";
}
else
{
thisCommand.CommandText = "UPDATE IM_SR_DSR_ORDER_DUE SET TotalDue = @TotalDue WHERE SrId = @SrId";
}
CreateParameter.AddParam(thisCommand, "@SrId", srDsrDue.Id, DbType.String);
CreateParameter.AddParam(thisCommand, "@TotalDue", Math.Round(srDsrDue.Due, 2), DbType.Double);
GenericDataAccess.ExecuteNonQuery(thisCommand);
thisCommand.Parameters.Clear();
svState = SavingState.Success;
}
catch (Exception ex)
{
if (ex.Message.ToLower().Contains("duplicate key"))
{
svState = SavingState.DuplicateExists;
}
}
finally
{
if (thisCommand != null && thisCommand.Connection.State != ConnectionState.Closed)
{
thisCommand.Connection.Close();
}
}
}
return(svState);
}
public bool DeleteUser(string userId)
{
bool ret = false;
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"DELETE FROM IM_USERS WHERE USER_ID <> 'admin' AND USER_ID = @USER_ID";
CreateParameter.AddParam(comm, "@USER_ID", userId, DbType.String);
if (GenericDataAccess.ExecuteNonQuery(comm) > 0)
{
ret = true;
}
if (comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
return(ret);
}
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 bool DeactiveUser(string userName)
{
bool ret = false;
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"UPDATE IM_USERS SET ACTIVE = @ACTIVE WHERE USER_NAME=@USER_NAME";
CreateParameter.AddParam(comm, "@USER_NAME", userName, DbType.String);
CreateParameter.AddParam(comm, "@ACTIVE", false, DbType.Boolean);
if (GenericDataAccess.ExecuteNonQuery(comm) > 0)
{
ret = true;
}
if (comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
return(ret);
}
public bool IsPaymentInfoExist(string paymentId)
{
bool ret = false;
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"Select count(PaymentId) From IM_SR_DSR_PAYMENT_DETAILS WHERE PaymentId=@PaymentId";
CreateParameter.AddParam(comm, "@PaymentId", paymentId, DbType.String);
string strRet = GenericDataAccess.ExecuteScalar(comm);
if (Convert.ToInt16(strRet) > 0)
{
ret = true;
}
if (comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
return(ret);
}
public static bool IsUserIdExist(string userId)
{
bool ret = false;
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"Select count(USER_ID) From IM_USERS WHERE USER_ID=@USER_ID";
CreateParameter.AddParam(comm, "@USER_ID", userId, DbType.String);
string strRet = GenericDataAccess.ExecuteScalar(comm);
if (Convert.ToInt16(strRet) > 0)
{
ret = true;
}
if (comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
return(ret);
}
public bool IsCompanyExist(string companyName)
{
bool ret = false;
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"Select count(CompanyId) From IM_Company WHERE CompanyName=@CompanyName";
CreateParameter.AddParam(comm, "@CompanyName", companyName, DbType.String);
string strRet = GenericDataAccess.ExecuteScalar(comm);
if (Convert.ToInt16(strRet) > 0)
{
ret = true;
}
if (comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
return(ret);
}
public IHttpActionResult CreateAll([FromBody] CreateParameter parameter)
{
try
{
var request = new AuthenticatedRequest();
if (!request.IsAdminLoggin ||
!request.AdminPermissionsImpl.HasSitePermissions(parameter.SiteId, ConfigManager.WebSitePermissions.Create))
{
return(Unauthorized());
}
CreateManager.CreateByAll(parameter.SiteId);
return(Ok(new { }));
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
public bool IsSRDSRDueExist(string Id)
{
bool ret = false;
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"Select count(SrId) From IM_SR_DSR_ORDER_DUE WHERE SrId=@SrId";
CreateParameter.AddParam(comm, "@SrId", Id, DbType.String);
string strRet = GenericDataAccess.ExecuteScalar(comm);
if (Convert.ToInt16(strRet) > 0)
{
ret = true;
}
if (comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
return(ret);
}
public SavingState DeleteSrDSrOrder(string companyId, string srDsrId, string marketId, DateTime orderDate)
{
SavingState svState = SavingState.Failed;
DbCommand comm = null;
try
{
comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = "DELETE FROM IM_Orders WHERE CompanyId = @CompanyId AND SrId = @SrId AND MarketId = @MarketId AND Date = @Date";
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", orderDate.Date, DbType.Date);
if (GenericDataAccess.ExecuteNonQuery(comm) > 0)
{
svState = SavingState.Success;
}
comm.Parameters.Clear();
}
catch (Exception ex)
{
if (ex.Message.ToLower().Contains("duplicate key"))
{
svState = SavingState.DuplicateExists;
}
}
finally
{
if (comm != null && comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
}
return(svState);
}
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 MerkServiceResponse GetAllMerk()
{
MerkServiceResponse response = new MerkServiceResponse();
Collection <Merk> merks = _merkRepository.GetAll();
if (merks.Count == 0)
{
response.Messages.Add(new Message("Tidak Ada Merk Yang Terdaftar"));
}
else
{
foreach (var m in merks)
{
CreateParameter param = new CreateParameter(m.Id, m.Code, m.Name, m.Manufacture);
MerkDomain merk = MerkDomain.Create(param);
MergeExtension.Merge(merk, m);
response.MerkDomains.Add(merk);
}
}
return(response);
}
public bool IsSRDSRExist(string name, string cellNo)
{
bool ret = false;
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"Select count(Id) From IM_SR_DSR WHERE Name=@Name AND CellNo = @CellNo";
CreateParameter.AddParam(comm, "@Name", name, DbType.String);
CreateParameter.AddParam(comm, "@CellNo", cellNo, DbType.String);
string strRet = GenericDataAccess.ExecuteScalar(comm);
if (Convert.ToInt16(strRet) > 0)
{
ret = true;
}
if (comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
return(ret);
}
public SavingState UpdateUser(User objUser)
{
SavingState svState = SavingState.Failed;
DbCommand thisCommand = null;
try
{
thisCommand = GenericDataAccess.CreateCommand();
thisCommand.CommandType = CommandType.Text;
thisCommand.CommandText = "UPDATE IM_USERS SET USER_NAME=@USER_NAME, PASSWORD=@PASSWORD, ACTIVE=@ACTIVE WHERE USER_ID=@USER_ID";
CreateParameter.AddParam(thisCommand, "@USER_ID", objUser.userID, DbType.String);
CreateParameter.AddParam(thisCommand, "@USER_NAME", objUser.userName, DbType.String);
CreateParameter.AddParam(thisCommand, "@PASSWORD", objUser.password, DbType.String);
CreateParameter.AddParam(thisCommand, "@ACTIVE", objUser.active, DbType.Boolean);
GenericDataAccess.ExecuteNonQuery(thisCommand);
thisCommand.Parameters.Clear();
svState = SavingState.Success;
}
catch (Exception ex)
{
if (ex.Message.ToLower().Contains("duplicate key"))
{
svState = SavingState.DuplicateExists;
}
}
finally
{
if (thisCommand != null && thisCommand.Connection.State != ConnectionState.Closed)
{
thisCommand.Connection.Close();
}
}
return(svState);
}
public bool IsUserDeactivated(string userName)
{
bool ret = false;
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"Select ACTIVE From IM_USERS WHERE USER_NAME=@USER_NAME";
CreateParameter.AddParam(comm, "@USER_NAME", userName, DbType.String);
string strRet = GenericDataAccess.ExecuteScalar(comm);
if (comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
if (strRet.Equals("0"))
{
ret = true;
}
return(ret);
}
public bool IsItemChalanExist(string chalanNo, string itemId, DateTime chalanDate)
{
bool ret = false;
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = @"Select count(ChalanId) From IM_Chalan_Activity WHERE ChalanNo=@ChalanNo AND ItemId=@ItemId AND ChalanDate=@ChalanDate";
CreateParameter.AddParam(comm, "@ChalanNo", chalanNo, DbType.String);
CreateParameter.AddParam(comm, "@ItemId", itemId, DbType.String);
CreateParameter.AddParam(comm, "@ChalanDate", chalanDate.Date, DbType.Date);
string strRet = GenericDataAccess.ExecuteScalar(comm);
if (Convert.ToInt16(strRet) > 0)
{
ret = true;
}
if (comm.Connection.State != ConnectionState.Closed)
{
comm.Connection.Close();
}
return(ret);
}
