public DocDetail(CDBUtil dbUtil, MySqlConnection conn, int documentId, int shopId, int productId,
int materialId, int productUnit, decimal productAmount, decimal productDiscount, decimal productTax,
decimal productPricePerUnit, decimal markUp, decimal pricePerUnitBeforeMark, int productTaxType,
decimal productTaxIn, int staffId, decimal productDiscountAmount, string unitName, decimal unitSmallAmount,
int unitId, int unitSmallId, decimal productNetPrice, decimal otherDiscount, int adjustLinkGroup)
{
_documentId = documentId;
_shopId = shopId;
_productId = productId;
_materialId = materialId;
_productUnit = productUnit;
_productAmount = productAmount;
_productDiscount = productDiscount;
_productTax = productTax;
_productPricePerUnit = productPricePerUnit;
_markUp = markUp;
_pricePerUnitBeforeMark = pricePerUnitBeforeMark;
_productTaxType = productTaxType;
_productTaxIn = productTaxIn;
_staffId = staffId;
_productDiscountAmount = productDiscountAmount;
_unitName = unitName;
_unitSmallAmount = unitSmallAmount;
_unitId = unitId;
_unitSmallId = unitSmallId;
_productNetPrice = productNetPrice;
_otherDiscount = otherDiscount;
_adjustLinkGroup = adjustLinkGroup;
GetLastDocdetailID(dbUtil, conn);
}
public List <Document> GetStockCountDiffByEachMaterial(CDBUtil dbUtil, MySqlConnection conn, DateTime dateFrom,
DateTime dateTo, int materialID, int invId, int langId)
{
List <Document> documentList = new List <Document>();
string sql = " SELECT a.DocumentID, a.ApproveDate, b.Productid, SUM(b.ProductAmount * c.MovementInStock) AS DiffAmount, " +
" c.DocumentTypeHeader, c.DocumentTypeName " +
" 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.shopid=" + invId + " AND a.DocumentDate BETWEEN {d '" + dateFrom.ToString("yyyy'-'MM'-'dd", dateProvider) + "'} " +
" AND {d '" + dateTo.ToString("yyyy'-'MM'-'dd", dateProvider) + "'} AND a.DocumentTypeID IN(31,32) AND b.ProductID= " + materialID +
" AND c.LangID=" + langId + " GROUP BY a.documentdate, b.ProductID, b.ProductAmount ";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Document document = new Document();
document.DocumentId = reader.GetInt32("DocumentID");
document.DocumentDate = reader.GetDateTime("ApproveDate");
document.ProductAmount = reader.GetDecimal("DiffAmount");
documentList.Add(document);
}
reader.Close();
return(documentList);
}
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 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 ApproveStockCount(CDBUtil dbUtil, MySqlConnection conn, int shopId, int staffId, string remark, List <Material> materialList)
{
if (materialList.Count > 0)
{
// Gen Diff Doc
AdjustDocument adjustDocument = new AdjustDocument(this.DocumentDate);
adjustDocument.CreateAdjustWeeklyDocument(dbUtil, conn, this.DocumentId, materialList, remark, shopId, staffId);
}
dbUtil.sqlExecute(" DELETE FROM docdetail WHERE DocumentID=" + this.DocumentId + " AND ShopID=" + shopId, conn);
string sql = " INSERT INTO docdetail (DocDetailID, DocumentID, ShopID, ProductID, ProductUnit, ProductAmount, ProductDiscount," +
" ProductTaxType, UnitName, UnitSmallAmount, UnitID) " +
" SELECT DocDetailID, DocumentID, ShopID, ProductID, ProductUnit, ProductAmount, ProductDiscount," +
" ProductTaxType, UnitName, UnitSmallAmount, UnitID " +
" FROM docdetailtemp WHERE DocumentID=" + this.DocumentId + " AND ShopID=" + shopId;
if (dbUtil.sqlExecute(sql, conn) > 0)
{
dbUtil.sqlExecute("UPDATE document SET DocumentStatus=2, ApproveBy=" + staffId + ", " +
" UpdateDate='" + DateTime.Now.ToString("yyyy'-'MM'-'dd HH':'mm':'ss", dateProvider) + "', " +
" ApproveDate='" + DateTime.Now.ToString("yyyy'-'MM'-'dd HH':'mm':'ss", dateProvider) + "', " +
" remark='" + remark + "' " +
" WHERE DocumentID=" + this.DocumentId + " AND ShopID=" + shopId + " AND DocumentTypeID=30", conn);
dbUtil.sqlExecute("DELETE FROM docdetailtemp WHERE DocumentID=" + this.DocumentId + " AND ShopID=" + shopId, conn);
return(true);
}
return(false);
}
protected DataTable GetMaterialDept(CDBUtil dbUtil, MySqlConnection conn, int productLevelId,
int shopId, string countType)
{
string sql = "";
DataTable dtMaterialDept = new DataTable();
sql = "SELECT a.MaterialDeptID, a.MaterialDeptName, " +
" b.materialgroupid, b.MaterialGroupName " +
" FROM materialdept a " +
" LEFT JOIN materialgroup b " +
" ON a.MaterialGroupID = b.MaterialGroupID ";
//sql = " SELECT a.*, b.*, c.*, d.*, e.*, f.*, g.* FROM materialgroup g " +
// " INNER JOIN materialdept a " +
// " ON g.MaterialGroupID=a.MaterialGroupID " +
// " INNER JOIN materials b " +
// " ON a.MaterialDeptID=b.MaterialDeptID " +
// " INNER JOIN unitsmall c " +
// " ON b.UnitSmallID=c.UnitSmallID " +
// " INNER JOIN unitratio d " +
// " ON c.UnitSmallID = d.UnitLargeID " +
// " INNER JOIN unitlarge e " +
// " ON d.UnitLargeID = e.UnitLargeID " +
// " INNER JOIN " + countType + " f " +
// " ON b.MaterialID=f.MaterialID " +
// " WHERE b.Deleted = 0 AND f.ProductLevelID=" + productLevelId +
// " AND f.ShopID=" + shopId + " GROUP BY g.MaterialGroupID ORDER BY b.MaterialCode, b.MaterialDeptID";
return(dtMaterialDept = dbUtil.List(sql, conn));
}
protected DataTable GetMaterials(CDBUtil dbUtil, MySqlConnection conn, int productLevelId,
int shopId, string countType)
{
string sql = "";
DataTable dtMaterials = new DataTable();
//sql = " SELECT a.*, b.*, c.*, d.*, e.*, f.* FROM materialdept a " +
// " LEFT JOIN materials b " +
// " ON a.MaterialDeptID=b.MaterialDeptID " +
// " LEFT JOIN unitsmall c " +
// " ON b.UnitSmallID=c.UnitSmallID " +
// " LEFT JOIN unitratio d " +
// " ON c.UnitSmallID = d.UnitLargeID " +
// " LEFT JOIN unitlarge e " +
// " ON d.UnitLargeID = e.UnitLargeID " +
// " LEFT JOIN " + countType + " f " +
// " ON b.MaterialID=f.MaterialID " +
// " WHERE b.Deleted = 0 AND f.ProductLevelID=" + productLevelId +
// " AND f.ShopID=" + shopId + " ORDER BY b.MaterialCode, b.MaterialDeptID ";
sql = " SELECT a.*, b.*, c.*, d.*, e.* FROM materials a " +
" INNER JOIN unitsmall b " +
" ON a.UnitSmallID=b.UnitSmallID " +
" INNER JOIN unitratio c " +
" ON b.UnitSmallID = c.UnitLargeID " +
" INNER JOIN unitlarge d " +
" ON c.UnitLargeID = d.UnitLargeID " +
" INNER JOIN " + countType + " e " +
" ON a.MaterialID=e.MaterialID " +
" WHERE a.Deleted = 0 AND e.ProductLevelID=" + productLevelId +
" AND e.ShopID=" + shopId + " ORDER BY a.MaterialCode, a.MaterialDeptID ";
return(dtMaterials = dbUtil.List(sql, conn));
}
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 DataTable GetMaterials2(CDBUtil dbUtil, MySqlConnection conn,
int productLevelId, int invId, string countConfigTableName)
{
DateTime dateFrom = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
DateTime dateTo = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
DataTable dtStockCountColumn = StockCardModule.StockCountColumn(dbUtil, conn);
DataTable dtStockCountData = new DataTable();
if (countConfigTableName == "dailystockmaterial")
{
dtStockCountData =
StockCardModule.DailyStockCountViewDetail(dbUtil, conn, dtStockCountColumn,
invId, DateTime.Now, 1, 1, 1, 1, "1");
}
else if (countConfigTableName == "weeklystockmaterial")
{
dtStockCountData =
StockCardModule.WeeklyStockCountViewDetail(dbUtil, conn, dtStockCountColumn,
invId, dateFrom, dateTo, 1, 1, 1, 1, "1");
}
else if (countConfigTableName == "monthlystockmaterial")
{
dtStockCountData =
StockCardModule.MonthlyStockCountViewDetail(dbUtil, conn, dtStockCountColumn,
invId, dateFrom, dateTo, 1, 1, 1, 1, "1");
}
return(dtStockCountData);
}
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 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);
}
//public bool IfHaveCountStockDailyDocument(CDBUtil dbUtil, MySqlConnection conn, int shopId)
//{
// string sql = "SELECT * FROM document WHERE DocumentID=" + DocumentId + " AND shopid=" + shopId
// + " AND documenttypeid=24 AND documentstatus=1";
// MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
// if (reader.Read())
// {
// reader.Close();
// return true;
// }
// else
// reader.Close();
// return false;
//}
public virtual bool AddAdjustDocument(CDBUtil dbUtil, MySqlConnection conn)
{
string sql = " INSERT INTO document (DocumentID, ShopID, DocumentTypeID, DocumentYear, DocumentMonth, " +
" DocumentNumber, DocumentDate, InputBy, UpdateBy, ApproveBy, DocumentStatus, DocumentIDRef, " +
" DocIDRefShopID, ProductLevelID, ToInvID, FromInvID, Remark, InsertDate, UpdateDate, ApproveDate) " +
" VALUES(" + DocumentId + ", " + ShopId + ", " + DocumentTypeId + ", " +
DocumentYear + ", " + DocumentMonth + ", " + DocumentNumber + ", '" +
DocumentDate.ToString("yyyy'-'MM'-'dd", dateProvider) + "', " +
InputBy + ", " + UpdateBy + ", " + ApproveBy + ", " + DocumentStatus + ", " + DocumentIdRef + ", " +
DocIdRefShopId + ", " + ProductLevelId + ", " + ToInvId + ", " + FromInvId + ", '" + Remark + "', '" +
DateTime.Now.ToString("yyyy'-'MM'-'dd HH':'mm':'ss", dateProvider) + "', '" +
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)
{
AddMaxDocumentNumber(dbUtil, conn);
return(true);
}
}
catch
{
return(false);
}
return(false);
}
// 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 bool DeleteVendor(CDBUtil dbUtil, MySqlConnection conn, int vendorId)
{
string sql = "";
sql = "UPDATE vendors SET Deleted=1 WHERE VendorID=" + vendorId;
dbUtil.sqlExecute(sql, conn);
return(true);
}
public bool DeleteDocdetailTemp(CDBUtil dbUtil, MySqlConnection conn)
{
dbUtil.sqlExecute("DELETE FROM docdetailtemp WHERE " +
" DocumentID=" + DocumentId + " AND ShopID=" + ShopId +
" AND DocumentTypeID=" + DocumentTypeId, conn);
return(true);
}
public bool DeleteVendorGroup(CDBUtil dbUtil, MySqlConnection conn, int vendorGroupId)
{
string sql = "";
sql = " UPDATE vendorgroup SET Deleted=1 WHERE VendorGroupID=" + vendorGroupId + " AND ShopID=" + ShopId;
dbUtil.sqlExecute(sql, conn);
return(true);
}
public bool UpdateDocument(CDBUtil dbUtil, MySqlConnection conn)
{
string sql = "UPDATE document SET UpdateDate='" + DateTime.Now.ToString("yyyy'-'MM'-'dd HH':'mm':'ss", dateProvider) + "', " +
" Remark = '" + Remark + "', UpdateBy=" + UpdateBy +
" WHERE DocumentID=" + DocumentId + " AND ShopID=" + ShopId;
dbUtil.sqlExecute(sql, conn);
return(true);
}
//protected void AddMaxDocumentID(CDBUtil dbUtil, MySqlConnection conn)
//{
// bool isMaxDocumentID = false;
// string sql = "";
// sql = "SELECT MaxDocumentID FROM maxdocumentid WHERE ShopID=" + this.ShopId + " AND IsDocumentOrBatch=1 ";
// MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
// if (reader.Read())
// {
// if (reader["MaxDocumentID"] != DBNull.Value)
// if (reader.GetInt32("MaxDocumentID") > this.DocumentId)
// isMaxDocumentID = true;
// }
// reader.Close();
// if (isMaxDocumentID == false)
// {
// sql = " UPDATE maxdocumentid SET MaxDocumentID=" + this.DocumentId + " WHERE ShopID=" + this.ShopId + " AND IsDocumentOrBatch=1";
// dbUtil.sqlExecute(sql, conn);
// }
//}
public void GetLastDocumentId(CDBUtil dbUtil, MySqlConnection conn)
{
int documentid = 0;
if (POSInventoryPOROModule.POSInventoryPOROModule.GetNewDocumentID(dbUtil, conn, this.ShopId, ref documentid, ref resultText))
{
this.DocumentId = documentid;
}
}
public virtual bool SaveCountData(CDBUtil dbUtil, MySqlConnection conn,
int staffId, List <Material> materialList)
{
// Create document if no document
// product data
Product product = new Product();
Material material = new Material();
material.DocumentTypeID = DocumentTypeId;
material.DocumentTypeIdStr = DocumentTypeIdStr;
// document data
Document document = new Document();
document.ShopId = this.ShopId;
document.DocumentTypeId = this.DocumentTypeId;
document.DocumentYear = this.DocumentDate.Year;
document.DocumentMonth = this.DocumentDate.Month;
document.GetLastDocumentNumber(dbUtil, conn);
document.DocumentDate = this.DocumentDate;
if (this.Remark != null)
{
document.Remark = this.Remark.Replace("'", string.Empty).Replace("/", string.Empty).Replace("\\", string.Empty);
}
document.InputBy = staffId;
document.UpdateBy = staffId;
document.DocumentStatus = 1;
document.ProductLevelId = this.ShopId;
document.InsertDate = this.DocumentDate;
document.GetLastDocumentId(dbUtil, conn);
this.DocumentId = document.DocumentId;
if (document.AddDocument(dbUtil, conn))
{
this.DocumentId = document.DocumentId;
int[] materialId = new int[materialList.Count];
decimal[] materialCurrAmount = new decimal[materialList.Count];
decimal[] materialAmount = new decimal[materialList.Count];
int[] unitLargeId = new int[materialList.Count];
for (int i = 0; i < materialList.Count; i++)
{
materialId[i] = materialList[i].MaterialId;
materialCurrAmount[i] = materialList[i].MaterialCurrAmount;
materialAmount[i] = materialList[i].MaterialAmount;
unitLargeId[i] = materialList[i].MaterialUnitLargeId;
}
if (!POSInventoryPOROModule.POSInventoryPOROModule.StockCount_AddDocDetail(dbUtil,
conn, document.DocumentId, document.ShopId, materialId, materialCurrAmount,
materialAmount, unitLargeId, true, ref refResultText))
{
return(false);
}
return(true);
}
return(false);
}
public bool DeleteDocDetail(CDBUtil dbUtil, MySqlConnection conn)
{
if (dbUtil.sqlExecute(" DELETE FROM docdetail WHERE DocDetailID = " +
DocDetailId + " AND DocumentID=" + DocumentId, conn) > 0)
{
return(true);
}
return(true);
}
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 virtual bool AddDocDetailTemp(CDBUtil dbUtil, MySqlConnection conn)
{
string sql = " INSERT INTO docdetailtemp (DocDetailID, DocumentID, ShopID, DocumentTypeID, ProductID, " +
" ProductUnit, ProductAmount, ProductTaxType, UnitName, UnitSmallAmount, UnitID) " +
" VALUES(" + DocDetailId + ", " + DocumentId + ", " + ShopId + ", " + DocumentTypeId + ", " + ProductId + ", " +
ProductUnit + ", " + ProductAmount + ", " + ProductTaxType + ", '" + UnitName + "', " + UnitSmallAmount + ", " + UnitId + ")";
int exec = dbUtil.sqlExecute(sql, conn);
return(true);
}
public bool UpdateStockCount(CDBUtil dbUtil, MySqlConnection conn, int docId, int shopId, int updateStaffId)
{
Document document = new Document();
document.ShopId = shopId;
document.DocumentId = docId;
document.Remark = Remark;
document.UpdateBy = updateStaffId;
return(document.UpdateDocument(dbUtil, conn));
}
//public void GetLastDocumentIdFromTableDocument(CDBUtil dbUtil, MySqlConnection conn)
//{
// MySqlDataReader reader = dbUtil.sqlRetrive("SELECT MAX(DocumentID) AS MaxDocumentID FROM document WHERE ShopID="
// + ShopId, conn);
// if (reader.Read())
// {
// if (reader["MaxDocumentID"] != DBNull.Value)
// this.DocumentId = Convert.ToInt32(reader["MaxDocumentID"]) + 1;
// }
// else
// {
// this.DocumentId = 1;
// }
// reader.Close();
//}
protected void AddMaxDocumentNumber(CDBUtil dbUtil, MySqlConnection conn)
{
dbUtil.sqlExecute("DELETE FROM maxdocumentnumber " +
" WHERE ShopID=" + this.ShopId + " AND DocType= " + this.DocumentTypeId +
" AND DocumentYear= " + this.DocumentYear +
" AND DocumentMonth=" + this.DocumentMonth, conn);
dbUtil.sqlExecute(" INSERT INTO maxdocumentnumber " +
" VALUES(" + this.ShopId + ", " + this.DocumentTypeId + ", " +
" 0, " + this.DocumentYear + ", " + this.DocumentMonth + ", " +
this.DocumentNumber + ")", conn);
}
public bool UpdateVendorGroup(CDBUtil dbUtil, MySqlConnection conn)
{
string sql = " UPDATE vendorgroup SET ShopID=" + ShopId + ", VendorGroupCode='" + VendorGroupCode + "', " +
" VendorGroupName='" + VendorGroupName + "' WHERE VendorGroupID=" + VendorGroupId +
" AND ShopID=" + ShopId;
if (dbUtil.sqlExecute(sql, conn) > 0)
{
return(true);
}
return(false);
}
public bool AddVendorGroup(CDBUtil dbUtil, MySqlConnection conn)
{
GetMaxVendorGroupId(dbUtil, conn);
string sql = " INSERT INTO vendorgroup (VendorGroupID, ShopID, VendorGroupCode, VendorGroupName) " +
" VALUES (" + VendorGroupId + ", " + ShopId + ", '" + VendorGroupCode + "', '" + VendorGroupName + "')";
if (dbUtil.sqlExecute(sql, conn) > 0)
{
return(true);
}
return(false);
}
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 Document(CDBUtil dbUtil, MySqlConnection conn, int shopId, int vendorId,
int vendorGroupId, int vendorShopId, int documentTypeId, int documentYear,
int documentMonth, DateTime documentDate, int inputBy,
int updateBy, int approveBy, int voidBy, int receiptBy, int documentStatus,
int invoiceRef, int documentIdRef, int docIdRefShopId, int productLevelId,
int toInvId, int fromInvId, int isSmallUnit, string remark, int termOfPayment,
DateTime dueDate, int documentHeader, int creditDay, DateTime insertDate,
DateTime updateDate, DateTime approveDate, DateTime cancelDate, int newSend,
int currentStaffAccess, decimal otherPercentDiscount,
decimal otherAmountDiscount, int documentVatType, decimal vatPercent)
{
_shopID = shopId;
_vendorID = vendorId;
_vendorGroupID = vendorGroupId;
_vendorShopID = vendorShopId;
_documentTypeID = documentTypeId;
_documentYear = documentYear;
_documentMonth = documentMonth;
_documentDate = documentDate;
_inputBy = inputBy;
_updateBy = updateBy;
_approveBy = approveBy;
_voidBy = voidBy;
_receiveBy = receiptBy;
_documentStatus = documentStatus;
_invoiceRef = invoiceRef;
_documentIDRef = documentIdRef;
_docIDRefShopID = docIdRefShopId;
_productLevelID = productLevelId;
_toInvID = toInvId;
_fromInvID = fromInvId;
_isSmallUnit = isSmallUnit;
_remark = remark;
_termOfPayment = termOfPayment;
_dueDate = dueDate;
_documentHeader = documentHeader;
_creditDay = creditDay;
_insertDate = insertDate;
_updateDate = updateDate;
_approveDate = approveDate;
_cancelDate = cancelDate;
_newSend = newSend;
_currentAccessStaff = currentStaffAccess;
_otherPercentDiscount = otherPercentDiscount;
_otherAmountDiscount = otherAmountDiscount;
_documentVATType = documentVatType;
_vatPercent = vatPercent;
GetLastDocumentId(dbUtil, conn);
GetLastDocumentNumber(dbUtil, conn);
}
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);
}
