public DataTable SelectAllShop()
{
DataTable companyTable = new DataTable();
DataAccessManager dam = new DataAccessManager();
companyTable = dam.GetDataTable(CommandType.StoredProcedure, "SelectShop");
return companyTable;
}
private static void Log(string Ticks, string UserId, string ActionId, string ActionDetails)
{
DataAccessManager dam = new DataAccessManager();
string[,] paramArray = { {"@Ticks", Ticks},
{"@UserId", UserId},
{"@ActionId", ActionId},
{"@ActionDetails", ActionDetails}};
dam.ExecuteNonQuery(CommandType.StoredProcedure, "InsertUserLog", paramArray);
}
public static DataTable Login(string UserName, string Password)
{
DataTable dt = null;
DataAccessManager dam = new DataAccessManager();
string[,] paramArray = {
{"@UserName",UserName},
{"@Password",Password}
};
dt = dam.GetDataTable(System.Data.CommandType.StoredProcedure, "SystemUserLogin", paramArray);
return dt;
}
public static DataTable Select(string UserId, string StartTicks, string EndTicks)
{
DataTable dt = null;
DataAccessManager dam = new DataAccessManager();
string[,] paramArray = { {"@UserId", UserId},
{"@StartTicks", StartTicks},
{"@EndTicks", EndTicks}};
dt = dam.GetDataTable(CommandType.StoredProcedure, "SelectUserLog", paramArray);
return dt;
}
//New Method for Delete Order on OrderId
public int DeleteOrder(int OrderID, int Operation)
{
int Result=0;
//Operation=2;
DataAccessManager dam= new DataAccessManager();
string[,] paraArray =
{
{"@OrderId",OrderID.ToString()},
{"@Operation",Operation.ToString()}
};
Result = dam.ExecuteNonQuery(CommandType.StoredProcedure, "InsertUpdateDeleteOrder", paraArray);
return Result;
}
public int SaveOrder(string CompanyId, string GrandTotal, string Discount, string Payment, string Arrears, string Comments, DateTime DeliveryDate, DataTable OrderDetail, bool IsReturn)
{
int newId = 0;
try
{
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
{
if (OrderDetail != null && OrderDetail.Rows.Count > 0)
{
DataAccessManager dam = new DataAccessManager();
string[,] paramArray = {
{"@CompanyId", CompanyId },
{"@GrandTotal", GrandTotal },
{"@Discount", Discount },
{"@Payment", Payment },
{"@Arrears", Arrears},
{"@Comments", Comments},
{"@DeliveryDate", Common.FormateDateForDB(DeliveryDate)},
{"@Status", "1"}
};
int id = Convert.ToInt32(dam.ExecuteScalar(CommandType.StoredProcedure, "InsertUpdateDeleteOrder", paramArray));
if (id > 0)
{
foreach (DataRow row in OrderDetail.Rows)
{
SaveOrderDetail(id.ToString(),
Convert.ToString(row["ItemId"])
, Convert.ToString(row["PurchasePrice"])
, Convert.ToString(row["Quantity"])
, Convert.ToString(row["Total"])
, Convert.ToString(row["Discount"])
, Convert.ToString(row["GrandTotal"])
);
}
scope.Complete();
newId = id;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return newId;
}
public static DataTable SelectRecentReceipt()
{
DataTable dt = null;
try
{
dt = new DataTable();
DataAccessManager dam = new DataAccessManager();
dt = dam.GetDataTable(CommandType.StoredProcedure, "RecentTopReceipt");
}
catch (Exception exe)
{
throw exe;
}
return dt;
}
public DataTable SelectAllActiveCompanies()
{
DataTable companyTable = new DataTable();
try
{
DataAccessManager dam = new DataAccessManager();
companyTable = dam.GetDataTable(CommandType.StoredProcedure, "SelectCompany");
}
catch (Exception ex)
{
throw ex;
}
return companyTable;
}
public DataTable SelectAllItemTypes(string isActive)
{
DataTable companyTable = new DataTable();
try
{
DataAccessManager dam = new DataAccessManager();
string[,] paramArray = {
{"@isActive",isActive}
};
companyTable = dam.GetDataTable(CommandType.StoredProcedure, "SelectItemType", paramArray);
}
catch (Exception ex)
{
throw ex;
}
return companyTable;
}
public static DataTable SelectReceipt(string ReceiptId)
{
DataTable dt = new DataTable();
try
{
DataAccessManager dam = new DataAccessManager();
string[,] paramArray = {
{ "@ReceiptId", ReceiptId }
};
dt = dam.GetDataTable(CommandType.StoredProcedure, "SelectReceipt", paramArray, "Receipt");
}
catch (Exception ex)
{
throw ex;
}
return dt;
}
public static DataTable ViewExceptionLog(string startDate,string endDate,string Module)
{
DataTable datatableException = new DataTable();
try
{
DataAccessManager dam = new DataAccessManager();
string[,] pararray = {
{"@StartDate",startDate},
{"@EndDate",endDate},
{"@Module",Module}
};
datatableException = dam.GetDataTable(CommandType.StoredProcedure, "SelectExceptionLog",pararray);
}
catch (Exception exe)
{
throw exe;
}
return datatableException;
}
public static bool CreateUpdateUser(string UserName
, string Password, string Name, string ContactNumber, string Address, string IsActive
, string AllowInvoice, string AllowStock, string AllowCompany, string AllowCustomer
, string AllowItem, string AllowShop, string AllowReports, string AllowUserManagement,
string AllowOrder, string AllowCompanyPayment, string AllowCustomerPayment,
string AllowReturnSale, string AllowReturnPurchase, int Opertion)
{
bool result = false;
try
{
DataAccessManager dam = new DataAccessManager();
string[,] pararray = {{"@UserName",UserName},
{"@Password",Password},
{"@Name",Name},
{"@ContactNumber",ContactNumber},
{"@Address",Address},
{"@IsActive",IsActive},
{"@AllowInvoice",AllowInvoice},
{"@AllowStock",AllowStock},
{"@AllowCompany",AllowCompany},
{"@AllowCustomer",AllowCustomer},
{"@AllowItem",AllowItem},
{"@AllowShop",AllowShop},
{"@AllowReports",AllowReports},
{"@AllowUserManagement",AllowUserManagement},
{"@AllowOrder",AllowOrder},
{"@AllowCompanyPayment",AllowCompanyPayment},
{"@AllowCustomerPayment",AllowCustomerPayment},
{"@AllowReturnSale",AllowReturnSale},
{"@AllowReturnPurchase",AllowReturnPurchase},
{"@Id",Opertion.ToString()}
};
result = dam.ExecuteNonQuery(CommandType.StoredProcedure, "InsertUpdateDeleteUser", pararray) > 0;
}
catch (Exception exe)
{
throw exe;
}
return result;
}
public bool InsertNewShop(string Operation, string ShopName, string ShopSologon, string Registration,
string Address, string PhoneNumber, string Properiter, string SaleNote)
{
bool result = false;
DataAccessManager dam = new DataAccessManager();
string[,] paramArray = {
{"@Operation", Operation},
{"@ShopName", ShopName},
{"@ShopAddress", Address},
{"@ShopNumber", PhoneNumber},
{"@ShopSlogon", ShopSologon},
{"@RegistrationNumber", Registration},
{"@Properiter", Properiter},
{"@SaleNote", SaleNote}
};
result = dam.ExecuteNonQuery(CommandType.StoredProcedure, "InsertUpdateDeleteShop", paramArray) > 0;
return result;
}
public bool InsertUpdateItemType(string Id, string Name, string IsActive)
{
bool result = false;
int operation = 0;
int.TryParse(Id, out operation);
if (operation > 0)
operation = 1;
DataAccessManager dam = new DataAccessManager();
string[,] paramArray = {
{"@Operation", operation.ToString()},
{"@ItemTypeId", Id},
{"@ItemTypeName", Name},
{"@IsActive", IsActive}
};
result = dam.ExecuteNonQuery(CommandType.StoredProcedure, "InsertUpdateDeleteItemType", paramArray) > 0;
return result;
}
public DataTable BuyerLedger(DateTime? StartDate, DateTime? EndDate, string Id)
{
DataTable dt = null;
try
{
string[,] paramArray = {
{"@StartDate", Common.FormateDateForDB(StartDate)},
{"@EndDate", Common.FormateDateForDB(EndDate)},
{"@Id", Id}
};
DataAccessManager dam = new DataAccessManager();
dt = dam.GetDataTable(CommandType.StoredProcedure, "Report_BuyerLedger", paramArray);
}
catch (Exception ex)
{
throw ex;
}
return dt;
}
public static bool ChangePassword(string UserName, string Password, string NewPassword, int Operation)
{
try
{
bool result = false;
DataAccessManager dam = new DataAccessManager();
string[,] pararry = {{"@UserName",UserName},
{"@Password",Password},
{"@NewPassword",NewPassword},
{"@Operation",Operation.ToString()}
};
result = dam.ExecuteNonQuery(CommandType.StoredProcedure, "ChangePassword", pararry) > 0;
return result;
}
catch (Exception exe)
{
throw exe;
}
}
public DataSet SelectOrderandOrderDetails(string OrderId, string CompanyId, DateTime StartDate, DateTime EndDate, bool All)
{
DataSet ds = new DataSet();
DataAccessManager dam = new DataAccessManager();
string[,] paramArray = {
{ "@OrderId", All?null:OrderId },
{ "@CompanyId", All?null:CompanyId} ,
{ "@StartDate", All?null:Common.FormateDateForDB(StartDate)} ,
{ "@EndDate", All?null:Common.FormateDateForDB(EndDate)}
};
ds = dam.GetDataSet(CommandType.StoredProcedure, "SelectOrderandOrderDetails", paramArray);
if (ds != null && ds.Tables.Count == 2)
{
ds.Tables[0].TableName = "Order";
ds.Tables[1].TableName = "OrderDetail";
DataRelation relation = new DataRelation("Order_OrderDetail"
, ds.Tables["Order"].Columns["Id"], ds.Tables["OrderDetail"].Columns["OrderId"]);
ds.Relations.Add(relation);
}
return ds;
}
public bool InsertUpdateCustomer(string Id, string code, string name, string contact, string address, string balance, string IsActive,
string Remarks, string City, string ContactPerson, string Phone, string Fax, string Web, string Email)
{
bool result = false;
try
{
//@Remarks ,@City ,@ContactPerson ,@Phone ,@Fax ,@Web ,@Email
int operation = 0;
int.TryParse(Id, out operation);
if (operation > 0)
operation = 1;
DataAccessManager dam = new DataAccessManager();
string[,] paramArray = {
{"@Operation", operation.ToString()},
{"@CustomerId", Id},
{"@CustomerCode", code},
{"@CustomerName", name},
{"@ContactNumber", contact},
{"@Address", address},
{"@CustomerBalance", balance},
{"@Remarks", Remarks},
{"@City", City},
{"@ContactPerson", ContactPerson},
{"@Phone", Phone},
{"@Fax", Fax},
{"@Web", Web},
{"@Email", Email},
{"@IsActive", IsActive}
};
result = dam.ExecuteNonQuery(CommandType.StoredProcedure, "InsertUpdateDeleteCustomer", paramArray) > 0;
}
catch (Exception ex)
{
throw ex;
}
return result;
}
public bool InsertUpdateItem(string Id, string ItemCode, string ItemName, string itemType, string packing, string CompanyId, string ItemInStock,
string PurchasePrice, string SalePrice, string Discount, string IsActive,string BatchNo,DateTime ExpiryDate)
{
bool result = false;
try
{
int operation = 0;
int.TryParse(Id, out operation);
if (operation > 0)
operation = 1;
DataAccessManager dam = new DataAccessManager(); //object of DataManager class
string[,] paramArray = {
{"@Operation", operation.ToString()},
{"@ItemId", Id},
{"@ItemCode", ItemCode},
{"@ItemName", ItemName},
{"@ItemType", itemType},
{"@Packing", packing},
{"@CompanyId",CompanyId},
{"@ItemInStock", ItemInStock},
{"@PurchasePrice", PurchasePrice},
{"@SalePrice", SalePrice},
{"@Discount", Discount},
{"@IsActive", IsActive},
{"@ExpiryDate",Common.FormateDateForDB(ExpiryDate)},
{"@BatchNo",BatchNo}
};
result = dam.ExecuteNonQuery(CommandType.StoredProcedure, "InsertUpdateDeleteItem", paramArray) > 0;
}
catch (Exception ex)
{
throw ex;
}
return result;
}
public static void LogException(Modules Module, string Method, Exception excp,string title)
{
try
{
if (excp != null)
{
string Message = excp.Message;
string StackTrace = excp.StackTrace;
string InnerMessage = null;
if (excp.InnerException != null)
{
InnerMessage = excp.InnerException.Message;
}
bool result = false;
DataAccessManager dam = new DataAccessManager();
string[,] paramArray = {
{"@Module", Module.ToString()},
{"@Method", Method},
{"@Message", Message},
{"@StackTrace", StackTrace},
{"@InnerMessage", InnerMessage}
};
dam.ExecuteNonQuery(CommandType.StoredProcedure, "InsertExceptionLog", paramArray);
ExceptionMessageShow exception1 = new ExceptionMessageShow(title,Message);
exception1.ShowExceptionMessage();
}
}
catch (Exception ex)
{
ExceptionMessageShow exception1 = new ExceptionMessageShow(title,ex.Message.ToString());
exception1.ShowExceptionMessage();
}
}
public long MakePayment(string CompanyId, string datetime, string amount, string Comments)
{
long TransactionId = -1;
try
{
DataAccessManager dam = new DataAccessManager();
string[,] paramArray = {
{"@CustomerCompanyId", CompanyId},
{"@TransactionDate", datetime},
{"@Amount", amount},
{"@IsCustomer", "0"},
{"@UserId", Common.CURRENT_USER.Id.ToString()},
{"@Comments", Comments}
};
object obj =dam.ExecuteScalar(CommandType.StoredProcedure, "InsertUpdateDeleteTransaction", paramArray) ;
TransactionId = Convert.ToInt64(obj);
}
catch (Exception ex)
{
throw ex;
}
return TransactionId;
}
public static bool VerifyRegistration()
{
bool IsValid = false;
try
{
DataAccessManager dam = new DataAccessManager();
object val = dam.ExecuteScalar(CommandType.Text, "SELECT top 1 [AD97DC5D7E618288E63528CC5CC6F3AE] from Shop");
if (val != null)
{
string strVal = Convert.ToString(val);
string newKey = CreateUniqueKey();
IsValid = strVal == newKey;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return IsValid;
}
public static bool VerifyLastRun()
{
bool result = false;
try
{
DateTime LRDate = DateTime.MinValue;
DataAccessManager dam = new DataAccessManager();
object val = dam.ExecuteScalar(CommandType.Text, "SELECT top 1 [A34C0CEC63EC0F1739FB5CB8F43A9CE1] from Shop");
if (val != null)
{
string strVal = Convert.ToString(val);
strVal = Decrypt(strVal, pass);
LRDate = Convert.ToDateTime(strVal, CultureInfo.InvariantCulture);
DateTime dtNow = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, CultureInfo.InvariantCulture.Calendar);
result = LRDate < DateTime.Now.AddMinutes(5);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return result;
}
public static void UpdateLR()
{
DataAccessManager dam = new DataAccessManager();
dam.ExecuteNonQuery(CommandType.Text, string.Format("UPDATE Shop SET [A34C0CEC63EC0F1739FB5CB8F43A9CE1]='{0}'", Encrypt(DateTime.Now.ToString(), pass)));
}
public static bool UnRegister()
{
bool result = false;
DataAccessManager dam = new DataAccessManager();
string[,] paramArray = {
{"@P1", null },
{"@P2", null},
{"@P3", null},
{"@P4", null}
};
result = dam.ExecuteNonQuery(CommandType.StoredProcedure, "Test", paramArray) > 0;
return result;
}
public static void RestoreBackup(string filePath)
{
try
{
string Query = string.Empty;
Query = "USE [master]" + Environment.NewLine;
Query += "ALTER DATABASE EBusiness" + Environment.NewLine;
Query += "SET SINGLE_USER WITH" + Environment.NewLine;
Query += "ROLLBACK IMMEDIATE" + Environment.NewLine;
Query += "RESTORE DATABASE EBusiness FROM DISK = '" + filePath + "' WITH REPLACE" + Environment.NewLine;
Query += "ALTER DATABASE EBusiness SET MULTI_USER" + Environment.NewLine;
DataAccessManager dam = new DataAccessManager();
//dam.ExecuteNonQuery(CommandType.Text, Query);
dam.ExecuteNonQueryOnMaster(CommandType.Text, Query);
}
catch (Exception ex)
{
throw;
}
}
public static bool RegistrationExists()
{
bool Exists = true;
try
{
DataAccessManager dam = new DataAccessManager();
object val = dam.ExecuteScalar(CommandType.Text, "SELECT count(*) as cnt from Shop");
if (val == null || Convert.ToInt32(val) == 0)
Exists = false;
}
catch (Exception ex)
{
}
return Exists;
}
public static int NoOfRegisteredDaysLeft()
{
int days = 0;
try
{
DateTime xpDate = DateTime.MinValue;
DataAccessManager dam = new DataAccessManager();
object val = dam.ExecuteScalar(CommandType.Text, "SELECT top 1 [1B29BA78A2DF965545C5A563B6E997AE] from Shop");
if (val != null)
{
string strVal = Convert.ToString(val);
strVal = Decrypt(strVal, pass);
xpDate = Convert.ToDateTime(strVal, CultureInfo.InvariantCulture);
DateTime dtNow = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, CultureInfo.InvariantCulture.Calendar);
TimeSpan ts = new TimeSpan(xpDate.Ticks - dtNow.Date.Ticks);
days = ts.Days;
CultureInfo cInfo = CultureInfo.InvariantCulture;
}
}
catch (Exception ex)
{
throw;
}
return days;
}
public static void InitializeDatabase()
{
DataAccessManager dam = new DataAccessManager();
dam.ExecuteNonQuery(CommandType.StoredProcedure, "Admin_InitializeDatabase");
}
public static int ExecuteUpdateDeleteQuery(string query)
{
int count = 0;
try
{
DataAccessManager dam = new DataAccessManager();
count = dam.ExecuteNonQuery(CommandType.Text, query);
}
catch (Exception ex)
{
throw;
}
return count;
}
