public DataTable getSalesReport(DateTime d1, DateTime d2)
{
dbCon.openCon();
SqlDataAdapter da = new SqlDataAdapter("SELECT Sales_master.sale_id, Client.clt_name, Sales_master.sale_date, Sales_master.sale_total From Sales_master INNER JOIN Client ON Sales_master.clt_id = Client.clt_id where Sales_master.sale_date between '" + d1 + "' AND '" + d2 + "'", dbCon.con);
dt = new DataTable();
da.Fill(dt);
dbCon.closeCon();
return(dt);
}
public int createClient(Client client)
{
dbCon.openCon();
cmd = new SqlCommand("insert into Client (clt_id,clt_name,clt_add,clt_email,clt_cont) values('" + client.Clt_Id + "','" + client.Clt_Name + "','" + client.Clt_Address + "','" + client.Clt_Email + "','" + client.Clt_ContactNo + "')", dbCon.con);
int status = cmd.ExecuteNonQuery();
dbCon.closeCon();
return(status);
}
public String findEmp(String User, String Pass, String Post)
{
dbCon.openCon();
SqlDataAdapter da = new SqlDataAdapter("select * from Employee where EmpPost = '" + Post + "' and UserName = '******' and UserPassword = '******'", dbCon.con);
dt = new DataTable();
da.Fill(dt);
dbCon.closeCon();
String username = null;
if (dt.Rows.Count != 0)
{
DataRow dr = dt.Rows[0];
username = dr["UserName"].ToString();
}
return(username);
}
public DataTable getBrand()
{
dbCon.openCon();
SqlDataAdapter da = new SqlDataAdapter("select * from Brand", dbCon.con);
dt = new DataTable();
da.Fill(dt);
dbCon.closeCon();
return dt;
}
public String GetProductId()
{
dbCon.openCon();
SqlDataAdapter da = new SqlDataAdapter("select top(1) Pro_id from Stock_Product order by Pro_id desc", dbCon.con);
dt = new DataTable();
da.Fill(dt);
DataRow dr = dt.Rows[0];
String productId;
productId = dr["Pro_id"].ToString();
dbCon.closeCon();
String abc = (Regex.Match(productId, @"\d+").Value);
int ProId = Convert.ToInt32(abc);
ProId++;
String newProId = "PRO" + ProId;
return(newProId);
}
public int createSupplier(Supplier supplier)
{
dbCon.openCon();
cmd = new SqlCommand("insert into Supplier (sup_id,sup_name,sup_add,sup_email,sup_cont) values('" + supplier.Sup_Id + "','" + supplier.Sup_Name + "','" + supplier.Address + "','" + supplier.Email + "','" + supplier.ContactNo + "')", dbCon.con);
int status = cmd.ExecuteNonQuery();
dbCon.closeCon();
return(status);
}
public String GetItemId()
{
dbCon.openCon();
SqlDataAdapter da = new SqlDataAdapter("select top(1) Item_id from Products order by Item_id desc", dbCon.con);
dt = new DataTable();
da.Fill(dt);
DataRow dr = dt.Rows[0];
dbCon.closeCon();
String Item_Id;
Item_Id = dr["Item_id"].ToString();
String abc = (Regex.Match(Item_Id, @"\d+").Value);
int ItemId = Convert.ToInt32(abc);
ItemId++;
String newItemId = "I" + ItemId;
return(newItemId);
}
public int createSale_master(SaleMaster SM)
{
dbCon.openCon();
String st = "Pending";
cmd = new SqlCommand("insert into Sales_master (sale_id,clt_id,sale_date,sale_total,sale_status) values('" + SM.sale_id + "','" + SM.clt_id + "','" + SM.sale_date + "','" + SM.sale_total + "','" + st + "')", dbCon.con);
int status = cmd.ExecuteNonQuery();
dbCon.closeCon();
return(status);
}
public int createPurOrder_master(PurMaster PM)
{
dbCon.openCon();
String st = "Waiting for Approval";
int A = 100000;
cmd = new SqlCommand("insert into purchase_master (pur_id,sup_id,pur_date,pur_status,pur_total) values('" + PM.pur_id + "','" + PM.sup_id + "','" + PM.pur_date + "','" + st + "','" + A + "')", dbCon.con);
int status = cmd.ExecuteNonQuery();
dbCon.closeCon();
return(status);
}