public DataTable GetVendor(int VendorId)
{
DataTable dt;
dt = dataAccess.getDataTable("Select * from Vendor where VendorId =" + VendorId);
return(dt);
}
public DataTable GetSystemInfo()
{
DataTable dt = new DataTable();
dt = dataAccess.getDataTable("Select * from SystemParameters");
return(dt);
}
public DataTable GetUserByName(string UserName)
{
DataTable dt = new DataTable();
dt = dataAccess.getDataTable("Select * from dbo.[User] where UserName = '******'");
return(dt);
}
public DataTable GetExpenseById(int ExpenseId)
{
DataTable dt;
dt = dataAccess.getDataTable("Select * from Expense where ExpenseId =" + ExpenseId);
return(dt);
}
//public DataTable GetEmployeeByUserId(int UserId)
//{
// DataTable dt;
// Smartworks.ColumnField[] gEmployee = new Smartworks.ColumnField[1];
// gEmployee[0] = new Smartworks.ColumnField("@UserId", UserId);
// dt = dataAccess.getDataTableByStoredProcedure("dbo.GetEmployee", gEmployee);
// return dt;
//}
public DataTable GetEmployee(int EmployeeId)
{
DataTable dt;
dt = dataAccess.getDataTable("Select * from Employee where EmployeeId =" + EmployeeId);
return(dt);
}
public int GetReqMasterIdByCode(string Code)
{
int ReqMasterId = -1;
DataTable dt = new DataTable();
dt = dataAccess.getDataTable("Select StockReqMasterId from StockReqMaster where StockReqNo = '" + Code + "' ");
if (dt.Rows.Count > 0)
{
ReqMasterId = (int)dt.Rows[0]["StockReqMasterId"];
}
return(ReqMasterId);
}
public int GetPromotionIdByCode(string Code)
{
int Id = -1;
DataTable dt = new DataTable();
dt = dataAccess.getDataTable("Select PromotionId from PromotionMaster where PromotionNo = '" + Code + "'");
if (dt.Rows.Count > 0)
{
Id = Convert.ToInt32(dt.Rows[0]["PromotionId"].ToString());
}
return(Id);
}
public int GetSalesReturnMasterIdByCode(string Code, int Fyear)
{
int Id = -1;
DataTable dt = new DataTable();
dt = dataAccess.getDataTable("Select SalesReturnMasterId from SalesReturnMaster where SalesReturnNo ='" + Code + "' and Fyear= '" + Fyear + "' ");
if (dt.Rows.Count > 0)
{
Id = Convert.ToInt32(dt.Rows[0]["SalesReturnMasterId"].ToString());
}
return(Id);
}
public DataTable GetPurchaseMaster(int PurchaseMasterId)
{
DataTable dt;
dt = dataAccess.getDataTable("Select * from PurchaseMaster where PurchaseMasterId =" + PurchaseMasterId);
return(dt);
}
private DataTable FillSystemInfo()
{
DataTable dt = new DataTable();
try
{
//dt = dataAcess.getDataTable("Select PoweredBy = ( Select PoweredBy from SystemParameters) , Company = (Select Name from Company) , WelcomeNote = ( Select WelcomeNote from SystemParameters)");
dt = dataAcess.getDataTable("Select PoweredBy = PoweredBy , Company = (Select Name from Company) , WelcomeNote ,FooterText = ReportFooterText , FiscalYearStart , FiscalYearEnd , FYear from SystemParameters");
}
catch (Exception ex)
{
MessageBox.Show("System Info does not Found.", "System Info not Found.", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
return(dt);
}
private void txtFromVendorCode_TextChanged(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtFromVendorCode.Text))
{
DataTable dtVendor = dataAcess.getDataTable("SELECT * FROM dbo.Vendor WHERE Code = '" + txtFromVendorCode.Text + "' ");
if (dtVendor.Rows.Count > 0)
{
txtFVendorName.Text = dtVendor.Rows[0]["VendorName"].ToString();
}
}
else
{
txtFVendorName.Text = string.Empty;
}
}
public DataTable GetCompany()
{
DataTable dt;
dt = dataAccess.getDataTable("Select * from Company");
return(dt);
}
public DataTable GetVoucherMaster(int VoucherMasterId, Smartworks.DAL customdataAcess = null)
{
DataTable dt;
if (customdataAcess != null)
{
dt = customdataAcess.getDataTable("Select * from VoucherMaster where VoucherMasterId =" + VoucherMasterId);
}
else
{
dt = dataAccess.getDataTable("Select * from VoucherMaster where VoucherMasterId =" + VoucherMasterId);
}
return(dt);
}
private void txtOrderByCode_TextChanged(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtOrderByCode.Text))
{
DataTable dtEmployee = dataAcess.getDataTable("SELECT * FROM dbo.Employee WHERE Code = '" + txtOrderByCode.Text + "' ");
if (dtEmployee.Rows.Count > 0)
{
txtOrderByName.Text = dtEmployee.Rows[0]["EmployeeName"].ToString();
}
else
{
txtOrderByName.Text = string.Empty;
}
}
else
{
txtOrderByName.Text = string.Empty;
}
}
public int GetPackingListMasterIdByCode(string Code) //, int FYear
{
int PackingListMasterId = -1;
DataTable dt = new DataTable();
dt = dataAccess.getDataTable("Select PackingListId from PackingListMaster where PackingListNo = '" + Code + "'");
if (dt.Rows.Count > 0)
{
PackingListMasterId = Convert.ToInt32(dt.Rows[0]["PackingListId"].ToString());
}
return(PackingListMasterId);
}
public int GetRoleByUserId(int userId)
{
DataTable dt;
string queryString = " Select * From [User_crm] where UserId=" + userId;
dataAccess.ConnectionOpen();
dt = dataAccess.getDataTable(queryString);
dataAccess.ConnectionClose();
return(Convert.ToInt32(dt.Rows[0]["userId"]));
}
public DataTable GetCustomer(int CustomerId)
{
DataTable dt;
dt = dataAccess.getDataTable("Select * from Customer where CustomerId =" + CustomerId);
return(dt);
}
public decimal GetProcessingQtyBalance(int ProductId, DateTime Dated, int WarehouseId, string SONo, int ArticleId, Smartworks.DAL customdataAcess = null)
{
decimal qty = 0;
DataTable dtQty = new DataTable();
string qurey = "select dbo.GetProductionBalance('" + ProductId + "','" + Dated + "','" + WarehouseId + "','" + SONo + "', '" + ArticleId + "') as Qty";
if (customdataAcess != null)
{
dtQty = customdataAcess.getDataTable(qurey);
}
else
{
dtQty = dataAccess.getDataTable(qurey);
}
if (dtQty.Rows.Count > 0)
{
qty = Convert.ToDecimal(dtQty.Rows[0][0]);
}
return(qty);
}
public string GetSaleOrderNoBySOrderId(int SOrderId, Smartworks.DAL customdataAcess = null)
{
string SOrderNo = "";
try
{
if (customdataAcess != null)
{
SOrderNo = customdataAcess.getDataTable("Select * FROM SOrderMaster WHERE SOrderId =" + SOrderId).Rows[0]["SOrderNo"].ToString();
}
else
{
SOrderNo = dataAccess.getDataTable("Select * FROM SOrderMaster WHERE SOrderId =" + SOrderId).Rows[0]["SOrderNo"].ToString();
}
}
catch (Exception ex)
{
SOrderNo = "";
}
return(SOrderNo);
}
public DataTable GetAllDepartment()
{
return(dataAccess.getDataTable("select DepartmentId , DepartmentName from Department"));
}
public DataTable GetAllTransport()
{
return(dataAccess.getDataTable("select TransportId , TransportName from Transport"));
}
public int GetSalesOrderMasterIdByCode(string Code) //int FYear , bool IsAllYear = false
{
int SOrderMasterId = -1;
DataTable dt = new DataTable();
dt = dataAccess.getDataTable("Select SOrderId from SOrderMaster where SOrderNo = '" + Code + "' ");
if (dt.Rows.Count > 0)
{
SOrderMasterId = Convert.ToInt32(dt.Rows[0]["SOrderId"].ToString());
}
return(SOrderMasterId);
}
public DataTable GetAllProductionStatus()
{
return(dataAccess.getDataTable("select ProductionStatusId , ProductionStatus from ProductionStatus"));
}
public DataTable GetAllBank()
{
return(dataAccess.getDataTable("select BankId , BankName from Bank"));
}
public DataTable GetAllRoles()
{
return(dataAccess.getDataTable("select * from usertype "));
}
public DataTable GetAllAccountCategory()
{
return(dataAccess.getDataTable("select ACId , AccountCategory from AccountCategory"));
}
public DataTable GetAllCity()
{
return(dataAccess.getDataTable("select CityId , City from City"));
}
public DataTable GetAllLastNo()
{
return(dataAccess.getDataTable("select LastNoId , LastNo from LastNo"));
}
private void btnUpdate_Click(object sender, EventArgs e)
{
try
{
if (string.IsNullOrEmpty(txtPath.Text))
{
MessageBox.Show("File Path does not Found.", "Error File Path", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (!Directory.Exists(txtPath.Text))
{
MessageBox.Show("File Path does not Match or Location Error.", "Location does not Match", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (Directory.Exists(txtPath.Text))
{
string FileName = DateTime.Now.Day + "-" + DateTime.Now.Month + "-" + DateTime.Now.Year;
string Path = txtPath.Text + "\\" + FileName + ".bak";
if (File.Exists(Path))
{
File.Delete(Path);
}
Smartworks.DAL dataAcess = new Smartworks.DAL();
string connString = ConfigurationManager.ConnectionStrings["HSConnectionString"].ConnectionString;
//ConfigurationManager.ConnectionStrings["HSConnectionString"].
IDbConnection connection = new SqlConnection(connString);
string dbName = connection.Database;
if (!string.IsNullOrEmpty(dbName))
{
bool result = false;
var status = dataAcess.getDataTable("BACKUP DATABASE " + dbName + " TO DISK = '" + Path + "'");
if (status != null)
{
result = true;
}
if (result)
{
if (File.Exists(Path))
{
ZipFile zip = new ZipFile();
try
{
zip.AddFile(Path);
zip.Save(txtPath.Text + "\\" + FileName + ".zip");
if (File.Exists(Path))
{
File.Delete(Path);
}
clsRemember.SetSessionValue("DatabaseFilePath", txtPath.Text);
}
catch (Exception ex)
{
}
finally
{
zip.Dispose();
}
}
MessageBox.Show("Database Backup Create Sucessfully.", "Backup Created", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Error to Create Database Backup.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Connection Instance does not Found.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public DataTable GetSaleMaster(int SalesMasterId)
{
DataTable dt;
dt = dataAccess.getDataTable("Select * from SalesMaster where SalesMasterId =" + SalesMasterId);
return(dt);
}