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 SelectAllActiveCustomer()
{
DataTable companyTable = new DataTable();
try
{
DataAccessManager dam = new DataAccessManager();
companyTable = dam.GetDataTable(CommandType.StoredProcedure, "SelectCustomer");
}
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 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 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 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 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 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 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 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 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 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", "1"},
{"@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 DataTable CompanyPurchaseReport(DateTime? StartDate, DateTime? EndDate, string Id, bool IsDetailed)
{
DataTable dt = null;
try
{
string[,] paramArray = {
{"@StartDate", Common.FormateDateForDB(StartDate)},
{"@EndDate", Common.FormateDateForDB(EndDate)},
{"@Id", Id},
{"@InDetail", IsDetailed?"1":"0"}
};
DataAccessManager dam = new DataAccessManager();
dt = dam.GetDataTable(CommandType.StoredProcedure, "Report_CompanyPurchaseReport", paramArray);
}
catch (Exception ex)
{
throw ex;
}
return dt;
}
private DataTable SalesPurchaseReport(string report, string StartDate, string EndDate, string Id)
{
DataTable dt = null;
try
{
string[,] paramArray = {
{"@Report",report},
{"@StartDate",StartDate},
{"@EndDate", EndDate},
{"@Id", Id}
};
DataAccessManager dam = new DataAccessManager();
dt = dam.GetDataTable(CommandType.StoredProcedure, "Report_SalesPurchase", paramArray);
}
catch (Exception ex)
{
throw ex;
}
return dt;
}
public DataTable ProfitLoassReport(DateTime? StartDate, DateTime? EndDate, string reportLevel)
{
DataTable dt = null;
try
{
string[,] paramArray = {
{"@StartDate", Common.FormateDateForDB(StartDate)},
{"@EndDate", Common.FormateDateForDB(EndDate)},
{"@Level", reportLevel}
};
DataAccessManager dam = new DataAccessManager();
dt = dam.GetDataTable(CommandType.StoredProcedure, "Report_ProfitLost", paramArray);
}
catch (Exception ex)
{
throw ex;
}
return dt;
}
public DataTable ProductTransactionSummaryReprot(string StartDate, string EndDate, string Id)
{
DataTable dt = null;
try
{
string[,] paramArray = {
{"@StartDate",StartDate},
{"@EndDate", EndDate},
{"@Id", Id}
};
DataAccessManager dam = new DataAccessManager();
dt = dam.GetDataTable(CommandType.StoredProcedure, "Report_ProductInOutSummaryReport", paramArray);
}
catch (Exception ex)
{
throw ex;
}
return dt;
}
public DataSet DailySalePurchaseReport(DateTime? StartDate, DateTime? EndDate, Int32 InDetail)
{
DataSet ds = null;
try
{
string[,] paramArray = {
{"@StartDate", Common.FormateDateForDB(StartDate)},
{"@EndDate", Common.FormateDateForDB(EndDate)},
{"@InDetail", InDetail.ToString()}
};
DataAccessManager dam = new DataAccessManager();
ds = dam.GetDataSet(CommandType.StoredProcedure, "Report_DailySalesPurchaseReport", paramArray);
}
catch (Exception ex)
{
throw ex;
}
return ds;
}
public DataSet DailySalePurchaseProductSummaryReport(DateTime? StartDate, DateTime? EndDate, string Id)
{
DataSet ds = null;
try
{
if (string.IsNullOrEmpty(Id))
Id = "0";
string[,] paramArray = {
{"@StartDate", Common.FormateDateForDB(StartDate)},
{"@EndDate", Common.FormateDateForDB(EndDate)},
{"@Id", Id}
};
DataAccessManager dam = new DataAccessManager();
ds = dam.GetDataSet(CommandType.StoredProcedure, "Report_ProductTransactionReport", paramArray);
}
catch (Exception ex)
{
throw ex;
}
return ds;
}
public DataTable CurrentCashReport()
{
DataTable ds = null;
try
{
DataAccessManager dam = new DataAccessManager();
ds = dam.GetDataTable(CommandType.StoredProcedure, "Report_CurrentCashReport");
}
catch (Exception ex)
{
throw ex;
}
return ds;
}
public DataTable SelectAllCustomer()
{
DataTable companyTable = new DataTable();
try
{
DataAccessManager dam = new DataAccessManager();
string[,] paramArray = {
{"@OnlyActive", "0"}
};
companyTable = dam.GetDataTable(CommandType.StoredProcedure, "SelectCustomer", paramArray);
}
catch (Exception ex)
{
throw ex;
}
return companyTable;
}
public DataTable SelectCustomerTransactions(string fromDate, string toDate, string Id)
{
DataTable companyTable = new DataTable();
try
{
DataAccessManager dam = new DataAccessManager();
string[,] paramArray = {
{"@CustomerCompanyId",Id},
{"@FromTransactionDate",fromDate},
{"@ToTransactionDate", toDate },
{"@IsCustomer", "1"}
};
companyTable = dam.GetDataTable(CommandType.StoredProcedure, "SelectTransactions", paramArray);
}
catch (Exception ex)
{
throw ex;
}
return companyTable;
}
