public static List <DataObject.Kho001> viewSPCL(String Ngay, String thu)
{
/*** Get Sữa Tồn ngày hôm trước *****/
List <DataObject.Kho001> lst = new List <DataObject.Kho001>();
SqlConnection conn = Common.Connection.SqlConnect();
DateTime d = DateTime.ParseExact(Ngay, "dd/MM/yyyy", CultureInfo.InvariantCulture);
DateTime ngayHomTruoc = d.AddDays(-1);
String strNgayHomTruoc = ngayHomTruoc.ToString("dd/MM/yyyy");
int dayOfWeek = Convert.ToInt32(ngayHomTruoc.DayOfWeek);
String strDay = "Select k.*,sp.Product_Name,sp.Product_Code from Kho001 k ";
strDay += "LEFT JOIN SYS_PRODUCT sp ON sp.ID = k.Product_ID ";
strDay += " where convert(date,CONVERT(VARCHAR(10),k.Ngay , 103),103) = convert(date,CONVERT(VARCHAR(10),'" + strNgayHomTruoc + "', 103),103)";
SqlCommand cmdDay = new SqlCommand(strDay);
cmdDay.CommandType = CommandType.Text;
cmdDay.Connection = conn;
conn.Open();
using (SqlDataReader oReader = cmdDay.ExecuteReader())
{
while (oReader.Read())
{
DataObject.Kho001 obj = new DataObject.Kho001();
obj.LoaiSua = oReader["Product_Name"].ToString();
String productCode = oReader["Product_Code"].ToString();
String strCheck = "SELECT * FROM fK001P1 ('" + productCode + "','" + strDay + "') AS MyResult";
DataTable dt = new DataTable();
SqlConnection connF = Common.Connection.SqlConnect();
SqlCommand cmdCheck = new SqlCommand(strCheck);
cmdCheck.CommandType = CommandType.Text;
cmdCheck.Connection = connF;
connF.Open();
dt.Load(cmdCheck.ExecuteReader());
connF.Close();
int thetich = Convert.ToInt16(oReader["Product_Unit"].ToString());
if (dt.Rows.Count > 0)
{
obj.SoLuongChai = 0;
obj.TheTich = dt.Rows[0][1].ToString() != "" ? (thetich - Int32.Parse(dt.Rows[0][1].ToString())) : 0;
obj.sugar = 0;
}
else
{
obj.TheTich = 0;
}
if (obj.TheTich > 0)
{
lst.Add(obj);
}
}
}
conn.Close();
return(lst);
}
public static List <DataObject.Kho001> viewK001P1(String Ngay, String thu)
{
List <DataObject.Kho001> lst = new List <DataObject.Kho001>();
SqlConnection conn = Common.Connection.SqlConnect();
String checkStr = "Select sm.*,sp.Product_Name,sp.Product_Code from SYS_MENU sm ";
checkStr += "LEFT JOIN SYS_PRODUCT sp ON sp.ID = sm.PRODUCT_ID ";
checkStr += " where sm.Code = '" + thu + "'";
SqlCommand cmd = new SqlCommand(checkStr);
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
conn.Open();
using (SqlDataReader oReader = cmd.ExecuteReader())
{
while (oReader.Read())
{
DataObject.Kho001 obj = new DataObject.Kho001();
DataObject.Kho001 obj1 = new DataObject.Kho001();
obj.LoaiSua = oReader["Product_Name"].ToString();
obj.ProductId = Convert.ToInt16(oReader["PRODUCT_ID"].ToString());
obj1.LoaiSua = oReader["Product_Name"].ToString();
obj1.ProductId = Convert.ToInt16(oReader["PRODUCT_ID"].ToString());
String productCode = oReader["Product_Code"].ToString();
/*** Không đường ****/
String strCheck = "SELECT * FROM fK001 ('" + productCode + "','" + Ngay + "',0) AS MyResult";
DataTable dt = new DataTable();
SqlConnection connF = Common.Connection.SqlConnect();
SqlCommand cmdCheck = new SqlCommand(strCheck);
cmdCheck.CommandType = CommandType.Text;
cmdCheck.Connection = connF;
connF.Open();
dt.Load(cmdCheck.ExecuteReader());
connF.Close();
if (dt.Rows.Count > 0)
{
obj.SoLuongChai = dt.Rows[0][0].ToString() != "" ? Int32.Parse(dt.Rows[0][0].ToString()) : 0;
obj.TheTich = dt.Rows[0][1].ToString() != "" ? Int32.Parse(dt.Rows[0][1].ToString()) : 0;
obj.sugar = dt.Rows[0][2].ToString() != "" ? (Int32.Parse(dt.Rows[0][2].ToString()) > 0 ? 1 : 0) : 0;
}
/*** Có đường ****/
String strSurgar = "SELECT * FROM fK001 ('" + productCode + "','" + Ngay + "',1) AS MyResult";
DataTable dtSurgar = new DataTable();
SqlConnection connSurgar = Common.Connection.SqlConnect();
SqlCommand cmdSurgar = new SqlCommand(strSurgar);
cmdSurgar.CommandType = CommandType.Text;
cmdSurgar.Connection = connSurgar;
connSurgar.Open();
dtSurgar.Load(cmdSurgar.ExecuteReader());
connSurgar.Close();
if (dtSurgar.Rows.Count > 0)
{
obj1.SoLuongChai = dtSurgar.Rows[0][0].ToString() != "" ? Int32.Parse(dtSurgar.Rows[0][0].ToString()) : 0;
obj1.TheTich = dtSurgar.Rows[0][1].ToString() != "" ? Int32.Parse(dtSurgar.Rows[0][1].ToString()) : 0;
obj1.sugar = dtSurgar.Rows[0][2].ToString() != "" ? (Int32.Parse(dtSurgar.Rows[0][2].ToString()) > 0 ? 1 : 0) : 0;
}
lst.Add(obj);
lst.Add(obj1);
}
}
conn.Close();
return(lst);
}