public virtual void GetProductIDFromBarCode(CDBUtil dbUtil, MySqlConnection conn, string productCode)
{
string sqlBarCode = " SELECT pb.ProductID, pb.ProductBarCode FROM productbarcode pb "
+ " INNER JOIN products p "
+ " ON(p.ProductID = pb.ProductID) "
+ " WHERE pb.ProductBarCode = '" + productCode.Trim() + "'";
string sqlProductCode = " SELECT ProductID, ProductCode FROM products WHERE ProductCode = '" + productCode.Trim() + "'";
MySqlDataReader reader = dbUtil.sqlRetrive(sqlBarCode, conn);
if (reader.Read())
{
if (reader["ProductID"] != DBNull.Value)
{
ProductId = Convert.ToInt32(reader["ProductID"]);
}
}
else
{
MySqlDataReader reader2 = dbUtil.sqlRetrive(sqlProductCode, conn);
if (reader2.Read())
{
if (reader2["ProductID"] != DBNull.Value)
{
ProductId = Convert.ToInt32(reader2["ProductID"]);
}
}
else
{
ProductId = 0;
}
}
reader.Close();
}
public virtual bool CheckDailyDocIsApprove(CDBUtil dbUtil, MySqlConnection conn)
{
string sql = " SELECT DocumentDate, DocumentID FROM document WHERE documenttypeid=30 AND shopid=" + ShopId +
" AND DocumentStatus=1 AND DocumentDate='" + this.DocumentDate.ToString("yyyy'-'MM'-'dd", dateProvider) + "'";
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
DocumentDate = reader.GetDateTime("DocumentDate");
DocumentId = reader.GetInt32("DocumentID");
reader.Close();
return(false); // Approved
}
else
{
sql = " SELECT DocumentDate, DocumentID FROM document WHERE documenttypeid=30 AND shopid=" + ShopId +
" AND DocumentStatus=2 AND DocumentDate='" + this.DocumentDate.ToString("yyyy'-'MM'-'dd", dateProvider) + "'";
reader.Close();
reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
DocumentDate = reader.GetDateTime("DocumentDate");
DocumentId = reader.GetInt32("DocumentID");
reader.Close();
return(true); // Not Approve
}
else
{
reader.Close();
}
}
return(false);
}
public virtual bool CheckDailyDocIsApprove(CDBUtil dbUtil, MySqlConnection conn, int documentId)
{
string sql = " SELECT * FROM document WHERE documentId=" + documentId +
" AND documenttypeid=" + this.DocumentTypeId + " AND shopid=" + this.ShopId +
" AND DocumentStatus=1 ";
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
if (reader["DocumentDate"] != DBNull.Value)
{
this.DocumentDate = reader.GetDateTime("DocumentDate");
}
if (reader["DocumentID"] != DBNull.Value)
{
this.DocumentId = reader.GetInt32("DocumentID");
}
if (reader["Remark"] != DBNull.Value)
{
this.Remark = reader.GetString("Remark");
}
reader.Close();
return(false); // Approved
}
else
{
sql = " SELECT * FROM document WHERE documentId=" + documentId +
" AND documenttypeid=" + this.DocumentTypeId + " AND shopid=" + this.ShopId +
" AND DocumentStatus=2 ";
reader.Close();
reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
if (reader["DocumentDate"] != DBNull.Value)
{
this.DocumentDate = reader.GetDateTime("DocumentDate");
}
if (reader["DocumentID"] != DBNull.Value)
{
this.DocumentId = reader.GetInt32("DocumentID");
}
if (reader["Remark"] != DBNull.Value)
{
this.Remark = reader.GetString("Remark");
}
reader.Close();
return(true); // Not Approve
}
else
{
reader.Close();
}
}
return(false);
}
public int GetMaxVendorId(CDBUtil dbUtil, MySqlConnection conn)
{
// get max vendorId from IdRange tabel
int maxVendorId = 0;
string sql = " SELECT MAX(VendorID) AS MaxVendorID FROM vendors WHERE ShopID=" + ShopId;
//ShopId = ShopId == 1 ? 0 : ShopId;
IdRange idRange = new IdRange(ShopId, 15);
idRange.getIdRange(conn);
using (MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn))
{
if (reader.Read())
{
if (reader["MaxVendorID"] != DBNull.Value)
{
maxVendorId = reader.GetInt32("MaxVendorID") + 1;
if (!(maxVendorId >= idRange.MinId && maxVendorId <= idRange.MaxId))
{
maxVendorId = idRange.MinId;
}
}
else
{
maxVendorId = idRange.MinId;
}
}
else
{
maxVendorId = idRange.MinId;
}
}
return(maxVendorId);
}
public void StaffsInfo(CDBUtil dbUtil, MySqlConnection conn, int staffId)
{
string sql = "SELECT * FROM staffs WHERE StaffID=" + staffId;
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
while (reader.Read())
{
if (reader["StaffID"] != DBNull.Value)
{
StaffId = Convert.ToInt32(reader["StaffID"]);
}
if (reader["StaffCode"] != DBNull.Value)
{
StaffCode = reader["StaffCode"].ToString();
}
if (reader["StaffFirstName"] != DBNull.Value)
{
StaffFirstName = reader["StaffFirstName"].ToString();
}
if (reader["StaffLastName"] != DBNull.Value)
{
StaffLastName = reader["StaffLastName"].ToString();
}
}
reader.Close();
}
public List <Document> ListDocType10(CDBUtil dbUtil, MySqlConnection conn)
{
string sql = " SELECT * FROM document " +
" WHERE ShopID= " + ProductLevelId + " AND DocumentTypeID = 10 " +
" AND documentstatus=2";
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
List <Document> docList = new List <Document>();
while (reader.Read())
{
Document document = new Document();
if (reader["DocumentID"] != DBNull.Value)
{
document.DocumentId = Convert.ToInt32(reader["DocumentID"]);
}
if (reader["DocumentYear"] != DBNull.Value)
{
document.DocumentYear = Convert.ToInt32(reader["DocumentYear"]);
document.DocumentMonth = Convert.ToInt32(reader["DocumentMonth"]);
document.DocumentMonthStr = Convert.ToDateTime(new DateTime(Convert.ToInt32(reader["DocumentYear"]),
Convert.ToInt32(reader["DocumentMonth"]), 1)).ToString("MMMM");
}
docList.Add(document);
}
reader.Close();
return(docList);
}
// check working document from operationdate
public virtual bool GetWorkingStockDocument(CDBUtil dbUtil, MySqlConnection conn, DateTime operationDate)
{
Boolean isApprove = true;
string sql = " SELECT * FROM document " +
" WHERE documenttypeid=" + this.DocumentTypeId + " AND shopid=" + this.ShopId +
" AND DocumentStatus=1 " +
" AND DocumentDate='" + operationDate.ToString("yyyy'-'MM'-'dd", dateProvider) + "'";
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
if (reader["DocumentDate"] != DBNull.Value)
{
this.DocumentDate = reader.GetDateTime("DocumentDate");
}
if (reader["DocumentID"] != DBNull.Value)
{
this.DocumentId = reader.GetInt32("DocumentID");
}
if (reader["Remark"] != DBNull.Value)
{
this.Remark = reader.GetString("Remark");
}
if (reader["DocumentStatus"] != DBNull.Value)
{
this.DocumentStatus = reader.GetInt32("DocumentStatus");
}
isApprove = false;
}
reader.Close();
return(isApprove);
}
public List <ProductLevel> ListInv(CDBUtil dbUtil, MySqlConnection conn, bool isStockCountConfig)
{
string sql = "";
sql = " SELECT * FROM ProductLevel WHERE Deleted = 0 ";
MySqlDataReader reader;
reader = dbUtil.sqlRetrive(sql, conn);
List <ProductLevel> invList = new List <ProductLevel>();
while (reader.Read())
{
ProductLevel invInfo = new ProductLevel();
if (reader["ProductLevelID"] != DBNull.Value)
{
invInfo.ProductLevelId = reader.GetInt32("ProductLevelID");
}
if (reader["ProductLevelCode"] != DBNull.Value)
{
invInfo.ProductLevelCode = reader.GetString("ProductLevelCode");
}
if (reader["ProductLevelName"] != DBNull.Value)
{
invInfo.ProductLevelName = reader.GetString("ProductLevelName");
}
invList.Add(invInfo);
}
reader.Close();
return(invList);
}
public virtual bool CheckTransferDocument(CDBUtil dbUtil, MySqlConnection conn, int inv, ref DateTime lastTransferDate)
{
// หาเอกสารยกยอดของเดือนปัจจุบัน
bool isTransfer = false;
string sql = "SELECT * FROM document WHERE ShopID=" + inv + " AND DocumentTypeID=10 " +
" AND DocumentStatus=" + DocumentStatus + " AND DocumentMonth=" + DateTime.Now.Month + " AND DocumentYear=" + DateTime.Now.ToString("yyyy", dateProvider);
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
if (reader["DocumentDate"] != DBNull.Value)
{
lastTransferDate = reader.GetDateTime("DocumentDate");
}
if (reader["Remark"] != DBNull.Value)
{
this.Remark = reader.GetString("Remark");
}
isTransfer = true;
}
reader.Close();
//ถ้าไม่เจอ หาล่าสุด
if (isTransfer == false)
{
sql = "SELECT * FROM document WHERE ShopID=" + inv + " AND DocumentTypeID=" +
" 10 AND DocumentStatus=" + DocumentStatus + " ORDER BY DocumentDate DESC LIMIT 1";
reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
if (reader["DocumentDate"] != DBNull.Value)
{
lastTransferDate = reader.GetDateTime("DocumentDate");
}
isTransfer = true;
}
}
reader.Close();
return(isTransfer);
}
public override bool CheckCountStockWeeklyDay(CDBUtil dbUtil, MySqlConnection conn)
{
// programpropertyvalue programtype 3 = invent, PropertyID 7 = weekly stock count
DayOfWeek dayOfWeek = (DayOfWeek)DateTime.Now.DayOfWeek;
int day = 0;
switch (dayOfWeek.ToString())
{
case "Sunday":
day = 0;
break;
case "Monday":
day = 1;
break;
case "Tuesday":
day = 2;
break;
case "Wednesday":
day = 3;
break;
case "Thursday":
day = 4;
break;
case "Friday":
day = 5;
break;
case "Saturday":
day = 6;
break;
}
string sql = @" SELECT * FROM programpropertyvalue " +
@" WHERE ProgramTypeID=3 AND PropertyID=7 AND PropertyValue=" + day;
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
reader.Close();
return(false);
}
else
{
}
return(true);
}
public bool CheckTransferStockOfThisShop(CDBUtil dbUtil, MySqlConnection conn)
{
string sql = " SELECT * FROM document WHERE " +
" ShopID= " + ProductLevelId + " AND DocumentTypeID = 10 " +
" AND DocumentMonth = " + DocumentMonth +
" AND DocumentYear = " + DocumentYear + " AND DocumentStatus = 2";
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
reader.Close();
return(true);
}
return(false);
}
public DateTime GetLastTransferDocument(CDBUtil dbUtil, MySqlConnection conn, int invId)
{
string sql = "SELECT * FROM document WHERE ShopID=" + invId + " AND DocumentTypeID=10 ORDER BY DocumentDate DESC LIMIT 1";
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
if (reader["DocumentID"] != DBNull.Value)
{
this.DocumentDate = reader.GetDateTime("DocumentDate");
}
}
reader.Close();
return(this.DocumentDate);
}
public int GetMaxVendorGroupId(CDBUtil dbUtil, MySqlConnection conn)
{
int maxVendorGroupId = 0;
string sql = " SELECT MAX(VendorGroupID) AS MaxVendorGroupID FROM vendorgroup WHERE ShopID=" + ShopId;
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
if (reader["MaxVendorGroupID"] != DBNull.Value)
{
maxVendorGroupId = reader.GetInt32("MaxVendorGroupID") + 1;
}
}
reader.Close();
return(maxVendorGroupId);
}
public int GetLastEndMonth(CDBUtil dbUtil, MySqlConnection conn)
{
int month = 0;
string sql = " SELECT MAX(DocumentMonth) AS DocumentMonth FROM document " +
" WHERE ShopID= " + ProductLevelId + " AND DocumentTypeID = 10 " +
" AND DocumentYear = " + DocumentYear + " AND documentstatus=2";
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
while (reader.Read())
{
if (reader["DocumentMonth"] != DBNull.Value)
{
month = Convert.ToInt32(reader["DocumentMonth"]);
}
}
reader.Close();
return(month);
}
public void GetLastDocdetailID(CDBUtil dbUtil, MySqlConnection conn)
{
string strSql = "SELECT MAX(DocDetailID) AS LastDocDetailID FROM docdetailtemp " +
" WHERE DocumentID=" + DocumentId + " AND ShopID=" + ShopId;
MySqlDataReader reader = dbUtil.sqlRetrive(strSql, conn);
int lastDocDetailID = 0;
while (reader.Read())
{
if (reader["LastDocDetailID"] != DBNull.Value)
{
lastDocDetailID = reader.GetInt32(0);
}
}
reader.Close();
this.DocDetailId = lastDocDetailID + 1;
}
public decimal GetMaterialSumAll(CDBUtil dbUtil, MySqlConnection conn,
DateTime dateFrom, DateTime dateTo, int materialId, int invId)
{
string strDocumentTypeId = "";
if (this.DocumentTypeId == 24)
{
strDocumentTypeId = "(18,19)";
}
else if (DocumentTypeId == 30)
{
strDocumentTypeId = "(31,32)";
}
else if (DocumentTypeId == 57)
{
strDocumentTypeId = "(58,59)";
}
string sql = "";
sql = " SELECT SUM(b.ProductAmount) * c.MovementInStock AS DiffAmount " +
" FROM document a " +
" INNER JOIN docdetail b " +
" ON a.DocumentID=b.DocumentID AND a.ShopID=b.ShopID " +
" INNER JOIN documenttype c " +
" ON a.DocumentTypeID=c.DocumentTypeID AND a.ShopID=c.ShopID " +
" WHERE a.DocumentTypeID IN " + strDocumentTypeId + " AND b.ProductID=" + materialId + " AND " +
" a.DocumentDate BETWEEN {d '" + dateFrom.ToString("yyyy'-'MM'-'dd", dateProvider) + "'} " +
" AND {d '" + dateTo.ToString("yyyy'-'MM'-'dd", dateProvider) + "'} " +
" AND a.ShopID=" + invId + " AND c.LangID=2 GROUP BY b.ProductID";
decimal diffAmount = 0;
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
diffAmount = reader.GetDecimal("DiffAmount");
}
reader.Close();
return(diffAmount);
}
public decimal GetMaterialSumByCountDate(CDBUtil dbUtil, MySqlConnection conn,
DateTime documentDate, int materialId, int invId)
{
string sql = "";
sql = " SELECT SUM(b.ProductAmount) AS DiffAmount " +
" FROM document a " +
" INNER JOIN docdetail b " +
" ON a.DocumentID=b.DocumentID AND a.ShopID=b.ShopID " +
" WHERE a.DocumentTypeID = " + this.DocumentTypeId + " AND b.ProductID=" + materialId + " AND " +
" a.DocumentDate={d '" + documentDate.ToString("yyyy'-'MM'-'dd", dateProvider) + "'} " +
" AND a.ShopID=" + invId + " GROUP BY b.ProductID, b.ProductAmount, a.DocumentDate";
decimal diffAmount = 0;
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
diffAmount = reader.GetDecimal("DiffAmount");
}
reader.Close();
return(diffAmount);
}
public virtual bool GetLastCountDate(CDBUtil dbUtil, MySqlConnection conn, int invId, ref DateTime lastCountDate)
{
bool isCount = false;
string sql = " SELECT * FROM document WHERE ShopID=" + invId + " AND DocumentTypeID=" + this.DocumentTypeId +
" AND DocumentStatus IN (" + DocumentStatusStr + ") ORDER BY DocumentDate DESC LIMIT 1";
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
if (reader["DocumentDate"] != DBNull.Value)
{
lastCountDate = reader.GetDateTime("DocumentDate");
}
if (reader["Remark"] != DBNull.Value)
{
this.Remark = reader.GetString("Remark");
}
isCount = true;
}
reader.Close();
return(isCount);
}
public void GetLastDocumentNumber(CDBUtil dbUtil, MySqlConnection conn)
{
//MySqlDataReader reader = dbUtil.sqlRetrive(" SELECT MaxDocumentNumber FROM maxdocumentnumber WHERE " +
// " ShopID=" + this.ShopId + " AND DocType=" + this.DocumentTypeId + " AND DocumentYear = " + this.DocumentYear +
// " AND DocumentMonth = " + this.DocumentMonth, conn);
//if (reader.Read())
//{
// if (reader["MaxDocumentNumber"] != DBNull.Value)
// this.DocumentNumber = reader.GetInt32(0) + 1;
//}
//else
//{
//reader.Close();
MySqlDataReader reader = dbUtil.sqlRetrive("SELECT MAX(DocumentNumber) AS DocumentNumber FROM document " +
" WHERE ShopID=" + this.ShopId + " AND DocumentTypeID=" + this.DocumentTypeId +
" AND DocumentYear=" + this.DocumentYear + " AND DocumentMonth=" + this.DocumentMonth, conn);
if (reader.Read())
{
if (reader["DocumentNumber"] != DBNull.Value)
{
this.DocumentNumber = reader.GetInt32(0) + 1;
}
else
{
this.DocumentNumber = 1;
}
}
else
{
this.DocumentNumber = 1;
}
reader.Close();
//}
//if (!reader.IsClosed)
// reader.Close();
}
public void ProductData(CDBUtil dbUtil, MySqlConnection conn, int productId)
{
string sql = " SELECT a.*, b.* FROM products a " +
" JOIN productprice b " +
" ON a.ProductID=b.ProductID " +
" WHERE b.MainPrice=1 AND a.ProductID=" + productId +
" AND a.Deleted=0";
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
while (reader.Read())
{
if (reader["ProductCode"] != DBNull.Value)
{
ProductCode = reader["ProductCode"].ToString();
}
if (reader["ProductName"] != DBNull.Value)
{
ProductName = reader["ProductName"].ToString();
}
if (reader["ProductVATRate"] != DBNull.Value)
{
VatRate = Convert.ToInt32(reader["ProductVATRate"]);
}
if (reader["VATType"] != DBNull.Value)
{
VatType = Convert.ToInt32(reader["VATType"]);
}
if (reader["ProductPrice"] != DBNull.Value)
{
ProductPrice = Convert.ToDecimal(reader["ProductPrice"]);
}
if (reader["ProductUnitName"] != DBNull.Value)
{
ProductUnitName = reader["ProductUnitName"].ToString();
}
}
reader.Close();
}
public virtual bool GetWorkingStockDocument(CDBUtil dbUtil, MySqlConnection conn)
{
/* ตรวจสอบว่า มีเอกสารการนับที่ยังไม่อนุมัติหรือป่าว โดยเอาตัวล่าสุดเสมอ
*
*/
Boolean isApprove = true;
string sql = " SELECT * FROM document " +
" WHERE documenttypeid=" + this.DocumentTypeId + " AND shopid=" + this.ShopId +
" AND DocumentStatus=1 AND DocumentMonth=" + this.DocumentDate.Month +
" AND DocumentYear=" + this.DocumentDate.Year + //" ORDER BY documentdate DESC LIMIT 1";
" AND DocumentDate='" + this.DocumentDate.ToString("yyyy'-'MM'-'dd", dateProvider) + "'";
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
if (reader["DocumentDate"] != DBNull.Value)
{
this.DocumentDate = reader.GetDateTime("DocumentDate");
}
if (reader["DocumentID"] != DBNull.Value)
{
this.DocumentId = reader.GetInt32("DocumentID");
}
if (reader["Remark"] != DBNull.Value)
{
this.Remark = reader.GetString("Remark");
}
if (reader["DocumentStatus"] != DBNull.Value)
{
this.DocumentStatus = reader.GetInt32("DocumentStatus");
}
isApprove = false;
}
reader.Close();
return(isApprove);
}
public List <Vendors> GetVendorGroup(CDBUtil dbUtil, MySqlConnection conn)
{
string strSql = "SELECT * FROM vendorgroup WHERE Deleted=0";
if (ShopId != 1)
{
strSql += " AND ShopID=" + ShopId;
}
List <Vendors> listVendorGroup = new List <Vendors>();
MySqlDataReader reader = dbUtil.sqlRetrive(strSql, conn);
while (reader.Read())
{
if (reader["VendorGroupID"] != DBNull.Value)
{
listVendorGroup.Add(new Vendors(Convert.ToInt32(reader["VendorGroupID"]),
reader["VendorGroupCode"].ToString(),
reader["VendorGroupName"].ToString()));
}
}
reader.Close();
return(listVendorGroup);
}
public List <Vendors> ListVendorGroup(CDBUtil dbUtil, MySqlConnection conn)
{
string sql = " SELECT a.*, b.ProductLevelName FROM vendorgroup a, productlevel b WHERE a.ShopID=b.ProductLevelID AND a.Deleted=0";
// if (ShopId != 0)
// sql += " AND ShopID=" + ShopId;
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
List <Vendors> vendorList = new List <Vendors>();
while (reader.Read())
{
Vendors vendor = new Vendors();
if (reader["VendorGroupID"] != DBNull.Value)
{
vendor.VendorGroupId = reader.GetInt32("VendorGroupID");
}
if (reader["ShopID"] != DBNull.Value)
{
vendor.ShopId = reader.GetInt32("ShopID");
}
if (reader["VendorGroupCode"] != DBNull.Value)
{
vendor.VendorGroupCode = reader.GetString("VendorGroupCode");
}
if (reader["VendorGroupName"] != DBNull.Value)
{
vendor.VendorGroupName = reader.GetString("VendorGroupName");
}
if (reader["ProductLevelName"] != DBNull.Value)
{
vendor.VendorInvName = reader.GetString("ProductLevelName");
}
vendorList.Add(vendor);
}
reader.Close();
return(vendorList);
}
public List <ProductLevel> ListInv(CDBUtil dbUtil, MySqlConnection conn, int staffRoleId)
{
bool isHQ = false;
string sql = "";
// Check shop is hq
sql = " SELECT * FROM property WHERE HeadOrBranch=1";
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
isHQ = true;
}
else
{
isHQ = false;
}
reader.Close();
sql = "";
if (isHQ)
{
sql = " SELECT * FROM ProductLevel WHERE Deleted = 0 AND IsInv=1 ";
}
else
{
sql = " SELECT * FROM ProductLevel WHERE Deleted = 0 AND IsInv=1 AND ProductLevelID <> 1 ";
}
System.Data.DataTable dtResult = dbUtil.List("SELECT * FROM staffroleviewinventory WHERE StaffRoleID=" + staffRoleId, conn);
if (dtResult.Rows.Count > 0)
{
sql += " AND ProductLevelID IN ( ";
for (int i = 0; i <= dtResult.Rows.Count - 1; i++)
{
sql += dtResult.Rows[i]["ProductLevelID"].ToString();
if (i < dtResult.Rows.Count - 1)
{
sql += " , ";
}
}
sql += " ) ";
}
reader = dbUtil.sqlRetrive(sql, conn);
List <ProductLevel> invList = new List <ProductLevel>();
while (reader.Read())
{
ProductLevel invInfo = new ProductLevel();
if (reader["ProductLevelID"] != DBNull.Value)
{
invInfo.ProductLevelId = reader.GetInt32("ProductLevelID");
}
if (reader["ProductLevelCode"] != DBNull.Value)
{
invInfo.ProductLevelCode = reader.GetString("ProductLevelCode");
}
if (reader["ProductLevelName"] != DBNull.Value)
{
invInfo.ProductLevelName = reader.GetString("ProductLevelName");
}
invList.Add(invInfo);
}
reader.Close();
return(invList);
}
public virtual bool CheckCountStockWeeklyDay(CDBUtil dbUtil, MySqlConnection conn)
{
// programpropertyvalue programtype 3 = invent, PropertyID 7 = weekly stock count
DayOfWeek dayOfWeek = (DayOfWeek)DateTime.Now.DayOfWeek;
int day = 0;
switch (dayOfWeek.ToString())
{
case "Sunday":
day = 0;
break;
case "Monday":
day = 1;
break;
case "Tuesday":
day = 2;
break;
case "Wednesday":
day = 3;
break;
case "Thursday":
day = 4;
break;
case "Friday":
day = 5;
break;
case "Saturday":
day = 6;
break;
}
string sql = " SELECT b.PropertyValue FROM programproperty a, programpropertyvalue b " +
" WHERE a.ProgramTypeID=b.ProgramTypeID AND a.PropertyID=b.PropertyID AND " +
" a.ProgramTypeID=3 AND a.PropertyID=7 AND b.PropertyValue=" + day;
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
reader.Close();
return(true);
}
else
{
reader.Close();
sql = " SELECT b.PropertyValue FROM programproperty a, programpropertyvalue b " +
" WHERE a.ProgramTypeID=b.ProgramTypeID AND a.PropertyID=b.PropertyID AND " +
" a.ProgramTypeID=3 AND a.PropertyID=7 ";
reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
if (reader["PropertyValue"] != DBNull.Value)
{
if (reader["PropertyValue"].ToString() == "-1")
{
reader.Close();
return(false);
}
else
{
reader.Close();
}
}
else
{
reader.Close();
}
}
else
{
reader.Close();
}
}
return(false);
}
public int GetVendor(CDBUtil dbUtil, MySqlConnection conn, int vendorId)
{
string sql = "SELECT * FROM vendors WHERE VendorID=" + vendorId + " AND Deleted=0";
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
int i = 0;
while (reader.Read())
{
if (reader["VendorID"] != DBNull.Value)
{
VendorId = reader.GetInt32("VendorID");
}
if (reader["VendorGroupID"] != DBNull.Value)
{
VendorGroupId = reader.GetInt32("VendorGroupID");
}
if (reader["ShopID"] != DBNull.Value)
{
ShopId = reader.GetInt32("ShopID");
}
if (reader["VendorName"] != DBNull.Value)
{
VendorName = reader.GetString("VendorName");
}
if (reader["VendorCode"] != DBNull.Value)
{
VendorCode = reader.GetString("VendorCode");
}
if (reader["VendorFirstName"] != DBNull.Value)
{
VendorFirstName = reader.GetString("VendorFirstName");
}
if (reader["VendorLastName"] != DBNull.Value)
{
VendorLastName = reader.GetString("VendorLastName");
}
if (reader["VendorAddress1"] != DBNull.Value)
{
VendorAddress1 = reader.GetString("VendorAddress1");
}
if (reader["VendorCity"] != DBNull.Value)
{
VendorCity = reader.GetString("VendorCity");
}
if (reader["VendorProvince"] != DBNull.Value)
{
VendorProvice = reader.GetInt32("VendorProvince");
}
if (reader["VendorZipCode"] != DBNull.Value)
{
VendorZipCode = reader.GetString("VendorZipCode");
}
if (reader["VendorTelephone"] != DBNull.Value)
{
VendorTel = reader.GetString("VendorTelephone");
}
if (reader["VendorMobile"] != DBNull.Value)
{
VendorMobile = reader.GetString("VendorMobile");
}
if (reader["VendorFax"] != DBNull.Value)
{
VendorFax = reader.GetString("VendorFax");
}
if (reader["VendorEmail"] != DBNull.Value)
{
VendorEmail = reader.GetString("VendorEmail");
}
if (reader["VendorAdditional"] != DBNull.Value)
{
VendorAdditional = reader.GetString("VendorAdditional");
}
if (reader["InsertDate"] != DBNull.Value)
{
InsertDate = reader.GetDateTime("InsertDate");
}
if (reader["InputBy"] != DBNull.Value)
{
InputBy = reader.GetInt32("InputBy");
}
if (reader["UpdateDate"] != DBNull.Value)
{
UpdateDate = reader.GetDateTime("UpdateDate");
}
if (reader["VendorTermOfPayment"] != DBNull.Value)
{
VendorTermOfPayMent = reader.GetInt32("VendorTermOfPayment");
}
if (reader["VendorCreditDay"] != DBNull.Value)
{
VendorCreditDay = reader.GetInt32("VendorCreditDay");
}
if (reader["DefaultTaxType"] != DBNull.Value)
{
DefaultTaxType = reader.GetInt32("DefaultTaxType");
}
i++;
}
reader.Close();
return(i);
}
public virtual bool AddDocument(CDBUtil dbUtil, MySqlConnection conn)
{
string sql = "";
if (this.DocumentTypeId == 7 || this.DocumentTypeId == 10)
{
sql = " SELECT * FROM document WHERE ShopID=" + ShopId +
" AND DocumentTypeID = " + DocumentTypeId + " AND DocumentYear=" +
this.DocumentYear + " AND DocumentMonth = " + this.DocumentMonth +
" AND DocumentStatus=1";
}
else if (this.DocumentTypeId == 57)
{
StockCountWeeklyDoro scd = new StockCountWeeklyDoro(conn, ShopId, 57);
string dateFrom = scd.SundayDate();
sql = " SELECT * FROM document \n" +
" WHERE ShopID=" + ShopId + " AND DocumentTypeID=" + DocumentTypeId + "\n" +
" AND DocumentDate BETWEEN '" + dateFrom + "' AND \n" +
" '" + DateTime.Now.ToString("yyyy-MM-dd", dateProvider) + "' \n" +
" AND DocumentStatus=1";
}
else
{
sql = " SELECT * FROM document WHERE ShopID=" + ShopId +
" AND DocumentTypeID = " + DocumentTypeId +
" AND DocumentStatus=1 AND DocumentDate=" +
" {d '" + this.DocumentDate.ToString("yyyy'-'MM'-'dd", dateProvider) + "'} " +
" AND DocumentStatus=1";
}
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
// Add Docdetail
if (reader["DocumentID"] != DBNull.Value)
{
DocumentId = Convert.ToInt32(reader["DocumentID"]);
}
if (reader["Remark"] != DBNull.Value)
{
Remark = reader.GetString("Remark");
}
reader.Close();
sql = "UPDATE document SET UpdateDate='" + DateTime.Now.ToString("yyyy'-'MM'-'dd HH':'mm':'ss", dateProvider) + "', " +
" Remark = '" + Remark + "' WHERE DocumentID=" + DocumentId + " AND ShopID=" + ShopId;
dbUtil.sqlExecute(sql, conn);
sql = "DELETE FROM docdetail WHERE DocumentID=" + DocumentId + " AND ShopID=" + ShopId;
dbUtil.sqlExecute(sql, conn);
sql = "DELETE FROM docdetailtemp WHERE DocumentID=" + DocumentId + " AND ShopID=" + ShopId;
dbUtil.sqlExecute(sql, conn);
return(true);
}
else
{
reader.Close();
sql = " INSERT INTO document (DocumentID, ShopID, " +
" DocumentTypeID, DocumentYear, DocumentMonth, DocumentNumber, DocumentDate, InputBy, " +
" UpdateBy, DocumentStatus, Remark, DocumentIDRef, DocIDRefShopID, ProductLevelID, InsertDate, UpdateDate) " +
" VALUES(" + DocumentId + ", " + ShopId + ", " + DocumentTypeId + ", " +
DocumentYear + ", " + DocumentMonth + ", " + DocumentNumber + ", '" +
DocumentDate.ToString("yyyy'-'MM'-'dd", dateProvider) + "', " +
InputBy + ", " + UpdateBy + ", 1, '" + Remark + "', " + DocumentIdRef + ", " + DocIdRefShopId + ", " + ProductLevelId + ", '" +
DateTime.Now.ToString("yyyy'-'MM'-'dd HH':'mm':'ss", dateProvider) + "', '" +
DateTime.Now.ToString("yyyy'-'MM'-'dd HH':'mm':'ss", dateProvider) + "')";
try
{
int exec = dbUtil.sqlExecute(sql, conn);
if (exec > 0)
{
// add maxdocumentnumber
AddMaxDocumentNumber(dbUtil, conn);
return(true);
}
}
catch
{
return(false);
}
}
return(false);
}
public List <Vendors> GetVendors(CDBUtil dbUtil, MySqlConnection conn, int vendorGroupId)
{
string strSql = "SELECT a.*, b.*, c.ProductLevelName FROM vendors a JOIN vendorgroup b " +
" ON (a.VendorGroupID=b.VendorGroupID AND a.ShopID=b.ShopID) " +
" JOIN productlevel c ON (a.ShopID=c.ProductLevelID) WHERE a.Deleted=0 and b.Deleted=0";
if (vendorGroupId > 0)
{
strSql += " AND b.VendorGroupID=" + vendorGroupId + " AND b.ShopID=" + ShopId;
}
//if(ShopId != 1) strSql += " AND a.ShopID = " + ShopId; // hq เห็นหมด
MySqlDataReader reader = dbUtil.sqlRetrive(strSql, conn);
List <Vendors> listVendor = new List <Vendors>();
while (reader.Read())
{
Vendors vendor = new Vendors();
if (reader["VendorID"] != DBNull.Value)
{
vendor.VendorId = reader.GetInt32("VendorID");
}
if (reader["VendorGroupID"] != DBNull.Value)
{
vendor.VendorGroupId = reader.GetInt32("VendorGroupID");
}
if (reader["ShopID"] != DBNull.Value)
{
vendor.ShopId = reader.GetInt32("ShopID");
}
if (reader["VendorCode"] != DBNull.Value)
{
vendor.VendorCode = reader.GetString("VendorCode");
}
if (reader["VendorName"] != DBNull.Value)
{
vendor.VendorName = reader.GetString("VendorName");
}
if (reader["VendorFirstName"] != DBNull.Value)
{
vendor.VendorFirstName = reader.GetString("VendorFirstName");
}
if (reader["VendorLastName"] != DBNull.Value)
{
vendor.VendorLastName = reader.GetString("VendorLastName");
}
if (reader["VendorAddress1"] != DBNull.Value)
{
vendor.VendorAddress1 = reader.GetString("VendorAddress1");
}
if (reader["VendorAddress2"] != DBNull.Value)
{
vendor.VendorAddress2 = reader.GetString("VendorAddress2");
}
if (reader["VendorCity"] != DBNull.Value)
{
vendor.VendorCity = reader.GetString("VendorCity");
}
if (reader["VendorProvince"] != DBNull.Value)
{
vendor.VendorProvice = reader.GetInt32("VendorProvince");
}
if (reader["VendorZipCode"] != DBNull.Value)
{
vendor.VendorZipCode = reader.GetString("VendorZipCode");
}
if (reader["VendorTelephone"] != DBNull.Value)
{
vendor.VendorTel = reader.GetString("VendorTelephone");
}
if (reader["VendorMobile"] != DBNull.Value)
{
vendor.VendorMobile = reader.GetString("VendorMobile");
}
if (reader["VendorFax"] != DBNull.Value)
{
vendor.VendorFax = reader.GetString("VendorFax");
}
if (reader["VendorEmail"] != DBNull.Value)
{
vendor.VendorEmail = reader.GetString("VendorEmail");
}
if (reader["VendorAdditional"] != DBNull.Value)
{
vendor.VendorAdditional = reader.GetString("VendorAdditional");
}
if (reader["InsertDate"] != DBNull.Value)
{
vendor.InsertDate = reader.GetDateTime("InsertDate");
}
if (reader["VendorFirstName"] != DBNull.Value)
{
vendor.VendorFirstName = reader.GetString("VendorFirstName");
}
if (reader["InputBy"] != DBNull.Value)
{
vendor.InputBy = reader.GetInt32("InputBy");
}
if (reader["UpdateDate"] != DBNull.Value)
{
vendor.UpdateDate = reader.GetDateTime("UpdateDate");
}
if (reader["VendorTermOfPayment"] != DBNull.Value)
{
vendor.VendorTermOfPayMent = reader.GetInt32("VendorTermOfPayment");
}
if (reader["VendorCreditDay"] != DBNull.Value)
{
vendor.VendorCreditDay = reader.GetInt32("VendorCreditDay");
}
if (reader["VendorGroupCode"] != DBNull.Value)
{
vendor.VendorGroupCode = reader.GetString("VendorGroupCode");
}
if (reader["VendorGroupName"] != DBNull.Value)
{
vendor.VendorGroupName = reader.GetString("VendorGroupName");
}
if (reader["ProductLevelName"] != DBNull.Value)
{
vendor.VendorInvName = reader.GetString("ProductLevelName");
}
listVendor.Add(vendor);
}
reader.Close();
return(listVendor);
}