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); }