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);
}
//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 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);
}
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 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 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);
}
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 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 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 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);
}
protected bool CopyAdjustDocdetailTemp(CDBUtil dbUtil, MySqlConnection conn, int documentId, int documentTypeId, int shopId, int staffId, string remark)
{
dbUtil.sqlExecute(" DELETE FROM docdetail WHERE DocumentID=" + documentId + " AND ShopID=" + shopId, conn);
string sql = " INSERT INTO docdetail (DocDetailID, DocumentID, ShopID, ProductID, ProductUnit, ProductAmount, " +
" ProductTaxType, UnitName, UnitSmallAmount, UnitID) " +
" SELECT DocDetailID, DocumentID, ShopID, ProductID, ProductUnit, ProductAmount, " +
" ProductTaxType, UnitName, UnitSmallAmount, UnitID " +
" FROM docdetailtemp WHERE DocumentID=" + documentId + " AND ShopID=" + shopId;
if (dbUtil.sqlExecute(sql, conn) > 0)
{
dbUtil.sqlExecute("UPDATE document SET DocumentStatus=2, UpdateBy=" + staffId + ", 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=" + documentId + " AND ShopID=" + shopId + " AND DocumentTypeID=" + documentTypeId, conn);
dbUtil.sqlExecute("DELETE FROM docdetailtemp WHERE DocumentID=" + documentId + " AND ShopID=" + shopId, conn);
return(true);
}
return(false);
}
public bool UpdateVendor(CDBUtil dbUtil, MySqlConnection conn)
{
string sql = " UPDATE vendors SET " +
" VendorName='" + VendorName + "', VendorCode='" + VendorCode + "', VendorFirstName='" + VendorFirstName + "', " +
" VendorLastName='" + VendorLastName + "', VendorAddress1='" + VendorAddress1 + "', VendorAddress2='" + VendorAddress2 + "', " +
" VendorCity='" + VendorCity + "', VendorProvince=" + VendorProvice + ", VendorZipCode='" + VendorZipCode + "', VendorTelephone='" + VendorTel + "', " +
" VendorMobile='" + VendorMobile + "', VendorFax='" + VendorFax + "', VendorEmail='" + VendorEmail + "'," +
" VendorAdditional='" + VendorAdditional + "', DefaultTaxType=" + DefaultTaxType + ", InsertDate='" + DateTime.Now.ToString("yyyy'-'MM'-'dd", dateProvider) + "', InputBy=" + InputBy + ", " +
" UpdateDate='" + DateTime.Now.ToString("yyyy'-'MM'-'dd", dateProvider) + "', VendorTermOfPayment=" + VendorTermOfPayMent + ", VendorCreditDay=" + VendorCreditDay +
" WHERE VendorID=" + VendorId + " AND VendorGroupID=" + VendorGroupId + " AND ShopID=" + ShopId;
if (dbUtil.sqlExecute(sql, conn) > 0)
{
return(true);
}
return(false);
}
public bool CancelStockCountDocument(CDBUtil dbUtil, MySqlConnection conn)
{
string sql = "";
sql = " UPDATE Document SET DocumentStatus = 99, UpdateBy= " + _staffId +
" WHERE DocumentID=" + DocumentId +
" AND ShopID = " + ShopId + " AND DocumentTypeID=" + DocumentTypeId;
dbUtil.sqlExecute(sql, conn);
DocDetail docDetail = new DocDetail();
docDetail.DocumentId = DocumentId;
docDetail.ShopId = ShopId;
docDetail.DocumentTypeId = DocumentTypeId;
docDetail.DeleteDocdetailTemp(dbUtil, conn);
return(true);
}
public bool AddVendor(CDBUtil dbUtil, MySqlConnection conn)
{
GetMaxVendorId(dbUtil, conn);
string sql = " INSERT INTO vendors (VendorID, VendorGroupID, ShopID, VendorName, VendorCode, " +
" VendorFirstName, VendorLastName, VendorAddress1, VendorAddress2, VendorCity, VendorProvince, " +
" VendorZipCode, VendorTelephone, VendorMobile, VendorFax, VendorEmail, VendorAdditional, " +
" DefaultTaxType, InsertDate, InputBy, UpdateDate, VendorTermOfPayment, VendorCreditDay) " +
" VALUES (" +
VendorId + ", " + VendorGroupId + ", " + ShopId + ", '" + VendorName + "', '" + VendorCode + "', '" +
VendorFirstName + "', '" + VendorLastName + "', '" + VendorAddress1 + "', '" + VendorAddress2 + "', '" + VendorCity + "', " +
VendorProvice + ", '" + VendorZipCode + "', '" + VendorTel + "', '" + VendorMobile + "', '" + VendorFax + "', '" +
VendorEmail + "', '" + VendorAdditional + "', " + DefaultTaxType + ", '" + DateTime.Now.ToString("yyyy'-'MM'-'dd HH:mm:ss", dateProvider) + "', " +
InputBy + ", '" + DateTime.Now.ToString("yyyy'-'MM'-'dd HH:mm:ss", dateProvider) + "', " +
VendorTermOfPayMent + ", " + VendorCreditDay +
" )";
if (dbUtil.sqlExecute(sql, conn) > 0)
{
return(true);
}
return(false);
}
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);
}