//-------------- Edit Issuse Information --------------------------------
public void EditIssueInformation(DBSite site, SalePurchaseDetail from_product, SalePurchaseDetail to_product, string product_ledger_number)
{
string qry = " ";
qry = " UPDATE tblProductLedger SET "
+ " BillDate = '" + from_product.MovementDate + " '"
+ ", ProductId = " + from_product.productAutoId
+ ", SoldQty = " + from_product.qty
+ ", LocationId = " + from_product.locationId
+ Util_BLL.GetUserWhereCondition(Util_BLL.User);
qry += " AND ProductLedgerNumber = " + product_ledger_number
+ " AND SourceId = " + TransactionType.StockMovement
+ " AND drcr = 'C'";
site.Execute(qry);
qry = " UPDATE tblProductLedger SET "
+ " BillDate = '" + to_product.MovementDate + " '"
+ ", ProductId = " + to_product.productAutoId
+ ", BoughtQty = " + to_product.qty
+ ", LocationId = " + to_product.locationId
+ Util_BLL.GetUserWhereCondition(Util_BLL.User);
qry += " AND ProductLedgerNumber = " + product_ledger_number
+ " AND SourceId = " + TransactionType.StockMovement
+ " AND drcr = 'D'";
site.Execute(qry);
}
//-------------------- Update Payment Information ----------------------------------------------
public void UpdatePaymentInformation(DBSite site, SalePurchaseEntity salePurchase, string ledgerIdNumber)
{
string ledger_id = util.GetLedgerId(ledgerIdNumber);
string ledger_number = util.GetLedgerNumber(ledgerIdNumber);
// 444
int party = Int32.Parse(util.GetLOVId(salePurchase.party));
int mode = Int32.Parse(util.GetLOVId(salePurchase.mode));
if (salePurchase.transactionType == TransactionType.PaymentMade)
{
int tmp;
tmp = Int32.Parse(util.GetLOVId(salePurchase.mode));
mode = Int32.Parse(util.GetLOVId(salePurchase.party));
party = tmp;
}
// sales - party
string party_qry = " UPDATE tblLedger SET "
+ " LDate = '" + salePurchase.BillDate + "' "
+ ", AccountMasterId= " + mode
+ ", modeid= " + party
+ ", BillNumber= '" + util.GetLOVName(salePurchase.BillNumber) + "' "
+ ", Particulars= '" + salePurchase.particulars + "' "
+ ", debit = " + salePurchase.SalePurchaseAmount
+ ", CheckNumber= '" + salePurchase.CheckNumber + "' "
+ ", subUserId = " + Util_BLL.User.Subusers[0].SubuserId
+ Util_BLL.GetUserWhereCondition(Util_BLL.User)
// + " AND LedgerId=" + ledger_id
+ " AND LedgerNumber=" + ledger_number
+ " AND drcr = 'D'";
// sales - mode
string mode_qry = " UPDATE tblLedger SET "
+ " LDate = '" + salePurchase.BillDate + "' "
+ ", AccountMasterId= " + party
+ ", modeId = " + mode
+ ", BillNumber= '" + util.GetLOVName(salePurchase.BillNumber) + "' "
+ ", Particulars= '" + salePurchase.particulars + "' "
+ ", credit = " + salePurchase.SalePurchaseAmount
+ ", CheckNumber= '" + salePurchase.CheckNumber + "' "
+ ", subUserId = " + Util_BLL.User.Subusers[0].SubuserId
+ Util_BLL.GetUserWhereCondition(Util_BLL.User)
+ " AND LedgerNumber=" + ledger_number
+ " AND drcr = 'C'";
site.Execute(party_qry);
site.Execute(mode_qry);
}
//- ----------------------- Save Issue Information -------------------------------------------------------------------
public void SaveIssueInformation(DBSite site, SalePurchaseDetail from_product, SalePurchaseDetail to_product)
{
int productLedgerNumber = util.GetAutoNumber(site, "ProductLedgerNumber");
string qry = "";
qry = " INSERT INTO tblProductLedger( ProductLedgerNumber, drcr , BillDate, ProductID, Soldqty, LocationID, ProductID2, BoughtQty2, LocationID2, SourceId,UserID,subuserId, FYear) "
+ " VALUES ( "
+ productLedgerNumber + " "
+ ", 'C' "
+ ", '" + from_product.MovementDate + "'"
+ ", " + from_product.productAutoId
+ ", " + from_product.qty
+ ", " + from_product.locationId
+ ", " + to_product.productAutoId
+ ", " + to_product.qty
+ ", " + to_product.locationId
+ ", " + TransactionType.StockMovement
+ ", " + util.GetUserInsertQry(Util_BLL.User) //--- current user
+ " ) ";
site.Execute(qry);
qry = " INSERT INTO tblProductLedger( ProductLedgerNumber, drcr , BillDate , ProductID, BoughtQty, LocationID, ProductID2, SoldQty2, LocationID2, SourceId, UserID, subuserId, FYear) "
+ " VALUES ( "
+ productLedgerNumber
+ ", 'D' "
+ ", '" + to_product.MovementDate + "'"
+ ", " + to_product.productAutoId
+ ", " + to_product.qty
+ ", " + to_product.locationId
+ ", " + from_product.productAutoId
+ ", " + from_product.qty
+ ", " + from_product.locationId
+ ", " + TransactionType.StockMovement
+ ", " + util.GetUserInsertQry(Util_BLL.User) //------- surrent user --------
+ " ) ";
site.Execute(qry);
}
public void DeleteStudent(DBSite site, string stIds)
{
string qry = " DELETE FROM tblStudentMaster ";
qry += " WHERE StudentMasterId IN (" + stIds + ")";
site.Execute(qry);
// delete from Attendance table also
qry = " DELETE FROM tblAttendance ";
qry += " WHERE StudentMasterId IN (" + stIds + ")";
site.Execute(qry);
}
public void DeleteExam(DBSite site, string ids)
{
string qry = "DELETE FROM tblExamMaster";
qry += Util_BLL.GetUserWhereCondition(Util_BLL.User);
qry += " AND ExamMasterID IN (" + ids + ")";
site.Execute(qry);
qry = "DELETE FROM tblExamMarks";
qry += Util_BLL.GetUserWhereCondition(Util_BLL.User);
qry += " AND ExamId IN (" + ids + ")";
site.Execute(qry);
}
//------------ Edit User Info ----------------------------------------
public void EditUserInfo(DBSite site, User user, string user_id)
{
string qry = " UPDATE tblUserInfo SET "
+ " UserName ='******'"
+ ", Email ='" + user.EmailId + "'"
+ ", UserPassword ='******'"
+ ", CreationDate='" + user.CreationDate + "'"
+ ", AmountPaid = " + user.AmountPaid
+ ", StartDate ='" + user.StartDate + "'"
+ ", EndDate ='" + user.EndDate + "'"
+ ", Address ='" + user.Address + "'"
+ ", City ='" + user.City + "'"
+ ", Country ='" + user.Country + "'"
+ ", Phone ='" + user.Phone + "'"
+ ", Mobile ='" + user.Mobile + "'"
+ ", TinNumber ='" + user.TinNumber + "'"
+ ", SalesTaxNumber ='" + user.SalesTaxNumber + "'"
+ ", CSTNumber ='" + user.CSTNumber + "'"
+ ", UserType = 1"
+ ", Remarks ='" + user.Remarks + "'"
+ ", NumberOfSubusers=" + user.NumberOFSubusers
+ " WHERE UserInfoId=" + user_id;
site.Execute(qry); //---------- update user information -----------
qry = " DELETE FROM tblUserPermissions"
+ " WHERE UserId=" + user_id;
site.Execute(qry); //----- delete all permissions with current user -----
//---------- then insert all the permissions now granted---------
foreach (Permission permission in user.Permissions)
{
qry = " INSERT INTO tblUserPermissions"
+ "("
+ "UserId"
+ ", PermissionId"
+ ")"
+ " VALUES("
+ user_id
+ ", "
+ permission.PermissionId
+ ")";
site.Execute(qry);
}
}
public void SaveAttendance(DBSite site, List <AttendanceEntity> attdList)
{
string qry = "";
foreach (AttendanceEntity atd in attdList)
{
qry = " INSERT INTO tblAttendance ("
+ " YearNo, MonthNo, DayNo, HolydayID, StudentMasterID, InTime, OutTime, Status, IsPosted, IsSMSSent, UserId, SubUserId, FYear )"
+ " VALUES ( "
+ atd.YearNo
+ ", " + atd.MonthNo
+ "," + atd.DayNo
+ "," + atd.StudentId
+ ", '" + atd.InTime + "'"
+ ", '" + atd.OutTime + "'"
+ ", " + atd.Status
+ ", " + atd.IsPosted
+ ", " + atd.IsSMSSent;
qry += ", " + util.GetUserInsertQry(Util_BLL.User);
qry += " ) ";
site.Execute(qry);
}
}
public void SaveProductMasterData(DBSite site, ProductMasterEntity pme)
{
string qry = "INSERT INTO tblProductMaster( ProductID, ProductName, OpeningBalance, ProductDate, CostPrice, SellingPrice, UOM, CategoryID";
qry += ", AccountId, LocationId, ReOrderQty ,ProductDescription, UserID, SubuserId , FYear) VALUES(";
qry += "'" + pme.ProductId + "'";
qry += ",'" + pme.ProductName + "'";
qry += ", " + pme.OpeningBalance;
qry += ", '" + pme.productDate + "'";
qry += ", " + pme.CostPrice;
qry += ", " + pme.SellingPrice;
qry += ", " + pme.UOM;
qry += ", " + pme.CategoryID;
qry += ", " + pme.AccountId;
qry += ", " + pme.LocationId;
qry += ", " + pme.ReOrderQty;
qry += ", '" + pme.ProductDescription + "'";
qry += ", " + util.GetUserInsertQry(Util_BLL.User);
//qry += ", " + Util_BLL.User.UserId;
//qry += ", " + Util_BLL.User.fYear;
qry += ")";
site.Execute(qry);
}
public void SaveStudent(DBSite site, StudenEntity st)
{
string qry = "INSERT INTO tblStudentMaster(StudentName, AdmissionNo, ClassId, SectionID, MobileF, MobileM, DOB, Email";
qry += ", IdCardNo, FatherName, MotherName, UserID, SubuserId , FYear) VALUES(";
qry += "'" + st.StudentName + "'";
qry += ", '" + st.AdmNo + "'";
qry += ", " + st.PresentClass;
qry += ", " + st.SectionId;
qry += ", '" + st.MobileNoF + "'";
qry += ", '" + st.MobileNoM + "'";
qry += ", '" + st.DOB + "'";
qry += ", '" + st.Email + "'";
qry += ", '" + st.IdCardNo + "'";
qry += ", '" + st.FatherName + "'";
qry += ", '" + st.MotherName + "'";
qry += ", " + util.GetUserInsertQry(Util_BLL.User);
qry += ")";
site.Execute(qry);
}
public void SaveAccountMasterData(DBSite site, AccountMasterEntity ame)
{
string qry = "INSERT INTO tblAccountMaster(AccountName, CreationDate, OpeningBalance, DRCR, ";
qry += " Address, City, Phone, Mobile, Email, Remarks, GroupId, UserID, SubuserId ,FYear) VALUES(";
qry += "'" + ame.AccountName + "'";
qry += ", '" + ame.CreationDate + "'";
qry += ", " + ame.OpeningBalance;
qry += ", " + ame.DRCR;
qry += ", '" + ame.Address + "'";
qry += ", '" + ame.City + "'";
qry += ", '" + ame.Phone + "'";
qry += ", '" + ame.Mobile + "'";
qry += ", '" + ame.Email + "'";
qry += ", '" + ame.Remarks + "'";
qry += ", " + ame.GroupId + ",";
qry += util.GetUserInsertQry(Util_BLL.User);
//qry += ", " + UserBLL.userID;
//qry += ", " + UserBLL.fYear;
qry += " )";
site.Execute(qry);
}
public void DeleteClasses(DBSite site, string ids)
{
string qry = "DELETE FROM tblStudentMaster";
qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); // --------get user where condition -----------------
qry += " AND ClassID IN (" + ids + ")";
site.Execute(qry);
qry = "DELETE FROM tblClassMaster";
qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); // --------get user where condition -----------------
qry += " AND ClassMasterID IN (" + ids + ")";
site.Execute(qry);
}
public void DeleteProduct(DBSite site, string ids)
{
string qry = "DELETE FROM tblProductMaster";
qry += " WHERE ProductMasterId IN (" + ids + ")";
site.Execute(qry);
}
public void changeClass(DBSite site, int classId, string studentIds)
{
string qry = " UPDATE tblStudentMaster SET ";
qry += "ClassId =" + classId;
qry += " WHERE StudentMasterId IN (" + studentIds + ")";
site.Execute(qry);
}
public void DeleteStore(DBSite site, string product_ledger_numbers)
{
string qry = " DELETE FROM ";
qry += " tblProductLedger ";
qry += " WHERE ProductLedgerNumber IN (" + product_ledger_numbers + ")";
site.Execute(qry);
}
public void DeleteRecords(DBSite dbSite, string selectedAccountType, AccountType act, string typeID)
{
string qry = "DELETE FROM " + AccountType.GetTableName(act, selectedAccountType);
qry += " WHERE " + AccountType.GetTypeIdName(act, selectedAccountType) + " IN ( " + typeID + ")";
dbSite.Execute(qry);
}
public void DeleteSalary(DBSite site, string bsIds)
{
string qry = " DELETE FROM tblSalary ";
qry += " WHERE SalaryID IN (" + bsIds + ")";
site.Execute(qry);
}
public void DeleteUser(DBSite site, string user_id)
{
string qry = " DELETE FROM tblUserInfo"
+ " WHERE UserInfoId IN (" + user_id + ")";
site.Execute(qry); //---- delete all user's from user information table ---------
qry = " DELETE FROM tblSubuser WHERE UserId IN (" + user_id + ")";
site.Execute(qry); //-------- delete all subusers of all selected users ------------------
qry = " DELETE FROM tblUserPermissions"
+ " WHERE UserId IN (" + user_id + ")";
site.Execute(qry); //----------- delete all permissions from User Permission table for all selected user's and their corressponding subusers--------
}
public void DeleteSubject(DBSite site, string ids)
{
string qry = "DELETE FROM tblSubjectMaster";
qry += Util_BLL.GetUserWhereCondition(Util_BLL.User);
qry += " AND SubjectMasterID IN (" + ids + ")";
site.Execute(qry);
}
public void DeleteTerm(DBSite site, string ids)
{
String qry = "DELETE FROM tblTerm";
qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); // --------get user where condition -----------------
qry += " AND TermID IN (" + ids + ")";
site.Execute(qry);
}
public void DeleteRecords(DBSite site, string ids)
{
string qry = "DELETE FROM tblAccountMaster";
qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); // --------get user where condition -----------------
qry += " AND AccountMasterId IN (" + ids + ")";
site.Execute(qry);
}
public void DeleteInfo(DBSite site, string trn_ids)
{
string qry = " DELETE FROM tblStockTransactionMaster "
+ " WHERE UserId=" + Util_BLL.User.UserId
+ " AND FYear=" + Util_BLL.User.fYear
+ " AND TransactionId IN (" + trn_ids + ")";
site.Execute(qry);
}
//----------- Delete Tax Info -------------------------------------------
public void DeleteInfo(DBSite site, string tax_ids)
{
string qry = " DELETE FROM tblTaxMaster "
+ " WHERE UserId=" + Util_BLL.User.UserId
+ " AND FYear=" + Util_BLL.User.fYear
+ " AND TaxId IN (" + tax_ids + ")";
site.Execute(qry);
}
public void UpdateStore(DBSite site, StockMovement store)
{
string qry = "DELETE FROM tblProductLedger WHERE productLedgerNumber = " + store.ProductLedgerNumber;
site.Execute(qry);
// update
SaveStore(site, store, 1);
}
public void EditSubuserInfo_stock(DBSite site, SubuserEntity subuser, string subuser_id)
{
string qry = " UPDATE tblSubuser SET "
+ "SubuserName = '******'"
+ ", SubuserCreationDate = '" + subuser.CreationDate + "'"
+ ", SubuserPassword = '******'"
+ ", EmailId = '" + subuser.EmailId + "'"
+ ", Designation = '" + subuser.Designation + "'"
+ ", Address = '" + subuser.Address + "'"
+ ", City = '" + subuser.City + "'"
+ ", Mobile = '" + subuser.Mobile + "'"
+ ", Active = " + subuser.UserState;
qry += " WHERE SubuserId=" + subuser_id;
site.Execute(qry); // ---- update subuser information ---------------
qry = " DELETE FROM tblUserPermissions"
+ " WHERE UserId=" + Util_BLL.User.UserId
+ " AND SubuserId=" + subuser_id;
site.Execute(qry); //------ Delete all permissions to selected Subuser ------
//----- update permissions to Subuser --------------------
foreach (Permission permission in subuser.Permissions)
{
qry = " INSERT INTO tblUserPermissions"
+ "("
+ "UserId"
+ ", SubuserId"
+ ", PermissionId"
+ ")"
+ " VALUES("
+ Util_BLL.User.UserId //------ user id of Current User --------
+ ", " + subuser_id
+ ", " + permission.PermissionId
+ ")";
site.Execute(qry);
}
}
public void UpdateSectionFrm(DBSite site, SectionMasterEntity sec)
{
string qry = "UPDATE tblSectionMaster SET ";
qry += " SectionName ='" + sec.SectionName + "'";
qry += ", SectionOrder=" + sec.SectionOrder + "";
qry += Util_BLL.GetUserWhereCondition(Util_BLL.User);
qry += " AND SectionMasterID=" + sec.SectionMasterId;
site.Execute(qry);
}
//-------------- Delete Payment information -----------------------------------------------------
public void DeletePaymentEntry(DBSite site, string ledger_number)
{
string qry = " DELETE FROM ";
qry += " tblLedger ";
qry += " WHERE LedgerNumber IN (" + ledger_number + ")";
qry += "AND UserId = " + Util_BLL.User.UserId;
qry += "AND FYear = " + Util_BLL.User.fYear;
site.Execute(qry);
}
public void EditInfo(DBSite site, StockTransactionType st, string trn_id)
{
string qry = " UPDATE tblStockTransactionMaster SET"
+ " TransactionName='" + st.StockTransactionName + "'"
+ ", Flow=" + st.StockTransactionFlow
+ " WHERE UserId=" + Util_BLL.User.UserId
+ " AND FYear=" + Util_BLL.User.fYear
+ " AND TransactionId=" + trn_id;
site.Execute(qry);
}
public void UpdateClassFrm(DBSite site, ClassMasterEntity cls)
{
string qry = "UPDATE tblClassMaster SET ";
qry += " ClassName ='" + cls.ClassName + "'";
qry += ", ClassOrder=" + cls.ClassOrder + "";
qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); //------------- gwt user where condition --------------
qry += " AND ClassMasterID=" + cls.ClassMasterId;
site.Execute(qry);
}
//---------------- Get Issue Information -------------------------------------------------------------
//------------ Delete Issue Info --------------------------------
public void DeleteIssueInfo(DBSite site, string product_ledger_number)
{
string qry = " DELETE FROM ";
qry += " tblProductLedger ";
qry += " WHERE ProductLedgerNumber IN (" + product_ledger_number + ")";
qry += "AND UserId = " + Util_BLL.User.UserId;
qry += "AND FYear = " + Util_BLL.User.fYear;
qry += "AND SourceId = " + TransactionType.StockMovement;
site.Execute(qry);
}
public void SaveClassMaster(DBSite site, SectionMasterEntity sec)
{
string qry = "INSERT INTO tblSectionMaster(SectionName, SectionOrder ";
qry += " , UserID, FYear) VALUES(";
qry += "'" + sec.SectionName + "'";
qry += ", " + sec.SectionOrder;
qry += ", " + util.GetUserInsertQryMaster(Util_BLL.User);
qry += " )";
site.Execute(qry);
}