public EMPLOYEE getEmployeeBy(string accCode, RECORD_STATUS status)
{
EMPLOYEE employee = null;
var queryResult = from acc in UShopDB.ACCOUNTs
join emp in UShopDB.EMPLOYEEs
on acc.EMP_ID equals emp.EMP_ID
where (
(status != 0 ? emp.RECORD_STATUS.Equals(((char)status) + "") : true) &&
(status != 0 ? acc.RECORD_STATUS.Equals(((char)status) + "") : true) &&
acc.ACC_CODE.Equals(accCode))
select emp;
try
{
employee = queryResult.SingleOrDefault();
}catch (ArgumentNullException e)
{
Console.WriteLine(e.Message);
}catch (InvalidOperationException ex)
{
Console.WriteLine(ex.Message);
}
return(employee);
}
public CUSTOMER getCustomerBy(string cusCode, RECORD_STATUS status)
{
CUSTOMER cus = null;
try
{
cus = UShopDB.CUSTOMERs.Where(o => o.CUS_CODE.Equals(cusCode) &&
o.RECORD_STATUS.Equals(((char)status) + "")
).SingleOrDefault();
var boughtAmountResult =
(
from c in UShopDB.CUSTOMERs
join inv in UShopDB.INVOICEs on c.CUS_ID equals inv.SELLER_ID
select(inv.TOTAL_AMOUNT)
);
cus.BOUGHT_AMOUNT = boughtAmountResult.Sum();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(cus);
}
public DataTable getInvoiceItemListBy(int invoiceId, RECORD_STATUS status)
{
DataTable table = null;
try
{
var result = from invi in UShopDB.INVOICE_ITEMs
join pro in UShopDB.PRODUCTs
on invi.PRODUCT_ID equals pro.PRODUCT_ID
where
(invoiceId != 0 ? invi.INVOICE_ID == invoiceId : true) &&
(status != 0 ? invi.RECORD_STATUS.Equals((char)status) : true)
select new
{
invi.INVOICE_ITEM_CODE,
pro.PRODUCT_CODE,
pro.PRODUCT_NAME,
invi.SIZE,
pro.UNIT,
invi.DISCOUNT,
invi.QUANTITY,
invi.PRICE,
invi.AMOUNT,
invi.NOTE
};
table = new DataTable();
var collumns = table.Columns;
collumns.Add("INVOICE_ITEM_CODE");
collumns.Add("PRODUCT_NAME");
collumns.Add("SIZE");
collumns.Add("UNIT");
collumns.Add("DISCOUNT_AMOUNT");
collumns.Add("QUANTITY");
collumns.Add("UNIT_PRICE");
collumns.Add("AMOUNT");
collumns.Add("NOTE");
int index = 0;
foreach (var o in result)
{
table.Rows.Add(index++,
o.PRODUCT_CODE + "-" + o.PRODUCT_NAME,
o.SIZE,
o.UNIT,
o.DISCOUNT,
o.QUANTITY,
o.PRICE,
o.AMOUNT,
o.NOTE);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(table);
}
public DataTable getAppointmentListBy(RECORD_STATUS status)
{
DataTable table = new DataTable();
var cols = table.Columns;
cols.Add("APP_CODE");
cols.Add("CUS_NAME");
cols.Add("EMP_NAME");
cols.Add("CREATED_DATE");
cols.Add("APP_DATE");
cols.Add("DESCRIPTION");
cols.Add("NOTE");
var result =
(from app in UShopDB.APPOINTMENTs
join cus in UShopDB.CUSTOMERs
on app.CUS_ID equals cus.CUS_ID
join emp in UShopDB.EMPLOYEEs
on app.PREPARER_ID equals emp.EMP_ID
where (status != 0 ? app.RECORD_STATUS.Equals(((char)RECORD_STATUS.ACTIVE) + "") : true)
select new
{
app.APP_CODE,
cus.CUS_NAME,
emp.EMP_NAME,
app.CREATED_DATE,
app.APP_DATE,
app.DESCRIPTION,
app.NOTE
}).DefaultIfEmpty();
foreach (var o in result)
{
if (o != null)
{
table.Rows.Add(
o.APP_CODE,
o.CUS_NAME,
o.EMP_NAME,
((DateTime)(o.CREATED_DATE)).ToString("dd/MM/yyyy"),
((DateTime)(o.APP_DATE)).ToString("dd/MM/yyyy"),
o.DESCRIPTION,
o.NOTE
);
}
}
return(table);
}
public EMPLOYEE getEmployeeBy(string empCode, RECORD_STATUS status)
{
EMPLOYEE emp = null;
try
{
emp = UShopDB.EMPLOYEEs.Where(o => o.EMP_CODE.Equals(empCode) &&
o.RECORD_STATUS.Equals(((char)status) + "")
).SingleOrDefault();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(emp);
}
public APPOINTMENT getAppointmentBy(string appCode, RECORD_STATUS status)
{
APPOINTMENT app = null;
try
{
app = UShopDB.APPOINTMENTs.Where(o => o.APP_CODE.Equals(appCode) &&
o.RECORD_STATUS.Equals(((char)status) + "")
).SingleOrDefault();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(app);
}
public ACCOUNT getAccountBy(string accCode, RECORD_STATUS status)
{
ACCOUNT acc = null;
try
{
acc = UShopDB.ACCOUNTs.Where(o => o.ACC_CODE.Equals(accCode) &&
o.RECORD_STATUS.Equals(((char)status) + "")
).SingleOrDefault();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(acc);
}
public List <EMPLOYEE> getEmployeesBy(RECORD_STATUS status)
{
List <EMPLOYEE> empList = null;
try
{
var queryResult = UShopDB.EMPLOYEEs
.Where
(o => (status != 0 ? o.RECORD_STATUS.Equals((char)status) : true) //if status = 0 -> independent record status
).DefaultIfEmpty();
empList = queryResult.ToList();
}
catch (ArgumentNullException e)
{
Console.WriteLine(e.Message);
}
return(empList);
}
public List <CUSTOMER> getCustomersBy(RECORD_STATUS status)
{
List <CUSTOMER> cusList = null;
try
{
var queryResult = UShopDB.CUSTOMERs
.Where
(o => (status != 0 ? o.RECORD_STATUS.Equals((char)status) : true) //if status = 0 -> independent record status
).DefaultIfEmpty();
cusList = queryResult.ToList();
}
catch (ArgumentNullException e)
{
Console.WriteLine(e.Message);
}
return(cusList);
}
public List <PRODUCT> getProductsBy(RECORD_STATUS status)
{
List <PRODUCT> proList = null;
try
{
var queryResult = UShopDB.PRODUCTs
.Where
(o => (status != 0 ? o.RECORD_STATUS.Equals((char)status) : true) //if status = 0 -> independent record status
).DefaultIfEmpty();
proList = queryResult.ToList();
}
catch (ArgumentNullException e)
{
Console.WriteLine(e.Message);
}
return(proList);
}
public DataTable getEmployeeListBy(RECORD_STATUS status)
{
DataTable table = new DataTable();
var cols = table.Columns;
cols.Add("EMP_CODE");
cols.Add("EMP_NAME");
cols.Add("GENDER");
cols.Add("BIRTHDAY");
cols.Add("ID_CARD_NO");
cols.Add("ADDRESS");
cols.Add("PHONE");
cols.Add("APPROVE_DATE");
cols.Add("WORK_STATUS");
cols.Add("SEV_DATE");
cols.Add("POSITION");
var result = UShopDB.EMPLOYEEs.Where(o => (status != 0 ? o.RECORD_STATUS.Equals((char)status) : true)).DefaultIfEmpty();
foreach (var o in result)
{
if (o != null)
{
table.Rows.Add(
o.EMP_CODE,
o.EMP_NAME,
o.GENDER,
o.BIRTHDAY != null ? ((DateTime)o.BIRTHDAY).ToString("dd/MM/yyyy"):"",
o.ID_CARD_NO,
o.ADDRESS,
o.PHONE,
o.APPROVE_DATE != null ? ((DateTime)o.APPROVE_DATE).ToString("dd/MM/yyyy") : "",
o.WORK_STATUS,
o.SEV_DATE != null ? ((DateTime)o.SEV_DATE).ToString("dd/MM/yyyy") : "",
o.POSITION
);
}
}
return(table);
}
//public DataTable getProductsBy(RECORD_STATUS status)
//{
// DataTable table = null;
// try
// {
// var result = UShopDB.PRODUCTs
// .Where(o => (status != 0 ? o.RECORD_STATUS.Equals((char)status) : true)).DefaultIfEmpty();
// table = new DataTable();
// table.Rows.Add(result.ToArray());
// table.Columns.Add("PRODUCT_ID");
// table.Columns.Add("PRODUCT_CODE");
// table.Columns.Add("PRODUCT_NAME");
// table.Columns.Add("CATEGORY_ID");
// table.Columns.Add("SELLING_PRICE");
// table.Columns.Add("COST_PRICE");
// table.Columns.Add("DISCOUNT_AMOUNT");
// table.Columns.Add("DESCRIPTION");
// table.Columns.Add("PRODUCER");
// table.Columns.Add("ORIGIN");
// table.Columns.Add("IMAGE_SRC");
// table.Columns.Add("RECORD_STATUS");
// }
// catch (Exception e)
// {
// Console.WriteLine(e.Message);
// }
// return table;
//}
//public DataTable getSizeBy(int proid, RECORD_STATUS status)
//{
// throw new NotImplementedException();
//}
public List <PRODUCT_SIZE> getProductSizeBy(int proid, RECORD_STATUS status)
{
List <PRODUCT_SIZE> prosizeList = null;
try
{
var queryResult = UShopDB.PRODUCT_SIZEs
.Where
(o => //(status != 0 ? o.RECORD_STATUS.Equals((char)status) : true)
//&&
(proid >= 0 ? o.PRODUCT_ID == proid : true)
).DefaultIfEmpty();
prosizeList = queryResult.ToList();
}
catch (ArgumentNullException e)
{
Console.WriteLine(e.Message);
}
return(prosizeList);
}
public INVOICE getInvoiceByCode(string code, RECORD_STATUS status)
{
INVOICE invoice = new INVOICE();
try
{
var queryResult = UShopDB.INVOICEs
.Where
(o => o.INVOICE_CODE.Equals(code) &&
(status != 0 ? o.RECORD_STATUS.Equals((char)status) : true) //if status = 0 -> independent record status
).SingleOrDefault();
invoice = queryResult;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(invoice);
}
public List <INVOICE> getInvoicesBy(String byEmployee, String date, RECORD_STATUS status)
{
List <INVOICE> invoiceList = null;
//try
//{
// var queryResult = UShopDB.INVOICEs.Where
// (o =>
// (byEmployee != null? o.SELLER_ID == byEmployee: true)
// && (date != null ? o.DATE.ToString().Equals(date): true)
// && (status != 0 ? o.RECORD_STATUS.Equals(status+""): true ) //if status = 0 -> independent record status
// );
// invoiceList = queryResult.ToList();
//}
//catch (ArgumentNullException e)
//{
// Console.WriteLine(e.Message);
//}
return(invoiceList);
}
public ACCOUNT getAccountByEmpCode(string empCode, RECORD_STATUS status)
{
ACCOUNT account = null;
try
{
var result = from acc in UShopDB.ACCOUNTs
join emp in UShopDB.EMPLOYEEs
on acc.EMP_ID equals emp.EMP_ID
where (emp.EMP_CODE.Equals(empCode) &&
(status != 0 ? emp.RECORD_STATUS.Equals((char)status) : true) &&
(status != 0 ? acc.RECORD_STATUS.Equals((char)status) : true)
)
select acc;
account = result.SingleOrDefault();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(account);
}
public DataTable getAccountListBy(RECORD_STATUS status)
{
DataTable table = new DataTable();
var cols = table.Columns;
cols.Add("EMP_CODE");
cols.Add("ACC_CODE");
cols.Add("PASSWORD");
cols.Add("POSITION");
var result = from acc in UShopDB.ACCOUNTs
join emp in UShopDB.EMPLOYEEs
on acc.EMP_ID equals emp.EMP_ID
where (status != 0 ? acc.RECORD_STATUS.Equals(((char)status) + "") : true)
select new
{
emp.EMP_CODE,
emp.EMP_NAME,
acc.ACC_CODE,
acc.PASSWORD,
emp.POSITION
};
foreach (var o in result)
{
if (o != null)
{
table.Rows.Add(
o.EMP_CODE + "-" + o.EMP_NAME,
o.ACC_CODE,
o.PASSWORD,
o.POSITION
);
}
}
return(table);
}
public DataTable getCustomerListBy(RECORD_STATUS status)
{
DataTable table = new DataTable();
var cols = table.Columns;
cols.Add("CUS_CODE");
cols.Add("CUS_NAME");
cols.Add("PHONE");
cols.Add("ADDRESS");
cols.Add("BOUGHT_AMOUNT");
var result = UShopDB.CUSTOMERs.Where
(o => (status != 0 ? o.RECORD_STATUS.Equals((char)status) : true)).DefaultIfEmpty();
foreach (var o in result)
{
if (o != null)
{
var boughtAmountResult =
UShopDB.INVOICEs.Where
(i => i.SELLER_ID == o.CUS_ID &&
i.RECORD_STATUS.Equals((char)RECORD_STATUS.ACTIVE)
)
.Select(i => i.TOTAL_AMOUNT).Sum();
table.Rows.Add(
o.CUS_CODE,
o.CUS_NAME,
o.PHONE,
o.ADDRESS,
boughtAmountResult > 0? boughtAmountResult: 0
);
}
}
return(table);
}
public DataTable getInvoiceListBy(int empId, string date, RECORD_STATUS status)
{
DataTable table = null;
try
{
var result = from inv in UShopDB.INVOICEs
join emp in UShopDB.EMPLOYEEs
on inv.SELLER_ID equals emp.EMP_ID
into final1
from empinv in final1.DefaultIfEmpty()
join cus in UShopDB.CUSTOMERs
on inv.BUYER_ID equals cus.CUS_ID
into final2
from empinvcus in final2.DefaultIfEmpty()
where
(empId != 0 ? empinv.EMP_ID == empId : true) &&
(date != null ? inv.DATE.ToString().Equals(date) : true) &&
(status != 0 ? inv.RECORD_STATUS.Equals((char)status) : true)
select new
{
inv.INVOICE_CODE,
inv.TOTAL_AMOUNT,
inv.TOTAL_PAYMENT,
inv.PAYMENT_METHOD,
empinv.EMP_NAME,
empinvcus.CUS_NAME,
inv.DATE
};
table = new DataTable();
var collumns = table.Columns;
collumns.Add("INVOICE_CODE"); //("Mã hóa đơn");
collumns.Add("TOTAL_AMOUNT"); //("Tổng tiền");
collumns.Add("TOTAL_PAYMENT"); //("Đã thanh toán");
collumns.Add("PAYMENT_METHOD");
collumns.Add("EMP_NAME");
collumns.Add("CUS_NAME");
collumns.Add("DATE");
foreach (var o in result)
{
table.Rows.Add(o.INVOICE_CODE, o.TOTAL_AMOUNT, o.TOTAL_PAYMENT, o.PAYMENT_METHOD, o.EMP_NAME, o.CUS_NAME, ((DateTime)(o.DATE)).ToString("dd/MM/yyyy"));
}
}
catch (ArgumentNullException e)
{
Console.WriteLine(e.Message);
}
//DataTable dt = new DataTable();
//dt.Columns.Add("RN_ID");
//dt.Columns.Add("RN_CODE");
//dt.Columns.Add("SUPPLIER_ID");
//dt.Columns.Add("SUPPLIER_NAME");
//dt.Columns.Add("PREPARER_ID");
//dt.Columns.Add("EMP_NAME");
//dt.Columns.Add("ISSUED_DATE");
//dt.Columns.Add("ACCOUNTING_DATE");
//dt.Columns.Add("ACCOUNTED");
//dt.Columns.Add("TOTAL");
//dt.Columns.Add("NOTE");
//dt.Columns.Add("RECORD_STATUS");
//foreach (var o in result.Where(o => o.RECORD_STATUS.Equals("A")))
//{
// dt.Rows.Add(o.RN_ID, o.RN_CODE, o.SUPPLIER_ID, o.SUPPLIER_NAME, o.PREPARER_ID, o.EMP_NAME,
// o.ISSUED_DATE, o.ACCOUNTING_DATE, o.ACCOUNTED, o.TOTAL, o.NOTE, o.RECORD_STATUS);
//}
return(table);
}
