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 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);
}
}
void SaveFileDetails(string SchoolNo, string admNo, string fileName)
{
site = new DBSite();
// save adminNo and full file name to database
string qry = "INSERT INTO tblTC(SchoolNo, AdmNo, UploadedFileName, UserId , SubUserID, FYear)"
+ " VALUES ("
+ "'" + SchoolNo + "'"
+ ", '" + admNo + "'"
+ ", '" + fileName + "'";
qry += ", " + util.GetUserInsertQry(Util_BLL.User);
qry += " )";
site.Execute(qry);
}
//----------------- Save Tax --------------------------------
public void SaveInfo(DBSite site, TaxEntity tax)
{
string qry = " INSERT INTO "
+ " tblTaxMaster"
+ "( "
+ "TaxName"
+ ", TaxPercent"
+ ", TaxAmount"
+ ", UserId, subuserId, FYear )"
+ " VALUES( '" + tax.TaxName + "'"
+ ", " + tax.TaxPercent
+ ", " + tax.TaxAmount
+ ", " + util.GetUserInsertQry(Util_BLL.User) //------ user insert query ----------
+ ")";
site.Execute(qry);
}
public void SaveMarks(DBSite site, List <ExamMarksEntity> exams, bool isUpdate, bool changeExam)
{
string qry = "";
foreach (ExamMarksEntity exam in exams)
{
if (!isUpdate && !isDuplicate(site, exam))
{
qry = " INSERT INTO tblExamMarks ("
+ " StudentID, ExamID, SubjectID, MarksObtained, IsPresent "
+ ", ClassID, SectionID "
+ " , UserId, SubUserId, FYear )"
+ " VALUES ( "
+ " " + exam.StudentMasterId
+ ", " + exam.ExamId
+ ", " + exam.SubjectId
+ ", '" + exam.MarksObtained + "'"
+ ", " + exam.IsPresent
+ ", " + exam.classId
+ ", " + exam.SectionId;
qry += ", " + util.GetUserInsertQry(Util_BLL.User);
qry += " ) ";
}
else
{
qry = " UPDATE tblExamMarks "
+ " SET "
+ " MarksObtained = '" + exam.MarksObtained + "'"
+ " , IsPresent = " + exam.IsPresent;
if (changeExam)
{
qry += " , ExamID = " + exam.ExamId;
}
qry += " WHERE ExamMarksID = " + exam.ExamMarksId;
}
site.Execute(qry);
}
}
public void SaveProductLedger(DBSite site, SalePurchaseEntity salePurchase, int ledgerNumber)
{
string qry = "";
for (int i = 0; i < salePurchase.SalePurchaseDetails.Count; i++)
{
int productLedgerNumber = util.GetAutoNumber(site, "ProductLedgerNumber");
int theLocation = -1;
if (salePurchase.transactionType == TransactionType.Sales)
{
theLocation = GetLocationId(site, "OUT");
}
else
{
theLocation = GetLocationId(site, "IN");
}
qry = " INSERT INTO tblProductLedger(LedgerNumber, ProductLedgerNumber, drcr, BillDate, BillNumber, AccountId, ProductID, BoughtQty, Discount, rate, SourceId, LocationID, UserID, subuserId, FYear) "
+ " VALUES ( "
+ ledgerNumber
+ ", " + productLedgerNumber
+ ", 'C'"
+ ", '" + salePurchase.BillDate + "'"
+ ", '" + salePurchase.BillNumber + "'"
+ ", '" + util.GetLOVId(salePurchase.party) + "'"
+ ", " + salePurchase.SalePurchaseDetails[i].productAutoId
+ ", " + salePurchase.SalePurchaseDetails[i].qty
+ ", '" + salePurchase.SalePurchaseDetails[i].discount + " '"
+ ", " + salePurchase.SalePurchaseDetails[i].rate
+ ", " + salePurchase.SaleType
+ ", " + theLocation
//+ ", " + salePurchase.SalePurchaseDetails[i].locationId
+ ", " + util.GetUserInsertQry(Util_BLL.User)
+ " ) ";
site.Execute(qry);
qry = " INSERT INTO tblProductLedger(LedgerNumber, ProductLedgerNumber, drcr, BillDate, BillNumber, AccountId, ProductID, SoldQty, Discount, rate, SourceId, LocationID, UserID, subuserId, FYear) "
+ " VALUES ( "
+ ledgerNumber
+ ", " + productLedgerNumber
+ ", 'D'"
+ ", '" + salePurchase.BillDate + "'"
+ ", '" + salePurchase.BillNumber + "'"
+ ", '" + Modes.SaleAccount + "'"
+ ", " + salePurchase.SalePurchaseDetails[i].productAutoId
+ ", " + salePurchase.SalePurchaseDetails[i].qty
+ ", '" + salePurchase.SalePurchaseDetails[i].discount + " '"
+ ", " + salePurchase.SalePurchaseDetails[i].rate
+ ", " + salePurchase.SaleType
+ ", " + theLocation
//+ ", " + salePurchase.SalePurchaseDetails[i].locationId
+ ", " + util.GetUserInsertQry(Util_BLL.User)
+ " ) ";
site.Execute(qry);
string tax_qry = "";
for (int j = 0; j < salePurchase.SalePurchaseDetails[i].SalePurchaseDetailsTaxes.Count; j++)
{
tax_qry = " INSERT INTO tblTax(LedgerNumber, ProductLedgerNumber, TaxMasterId, TaxAmount, UserID, subUserId, FYear) "
+ " VALUES ( "
+ ledgerNumber
+ ", " + productLedgerNumber
+ ", " + salePurchase.SalePurchaseDetails[i].SalePurchaseDetailsTaxes[j].TaxId
+ ", " + salePurchase.SalePurchaseDetails[i].SalePurchaseDetailsTaxes[j].TaxAmount
+ ", " + util.GetUserInsertQry(Util_BLL.User)
+ " ) ";
site.Execute(tax_qry);
}
}
}
public void SaveSalePurchase(DBSite site, SalePurchaseEntity salePurchase, bool isOpeningBalance)
{
Util_BLL util = new Util_BLL();
/**
*
* // payment made If GM.isFees = 2 Then
* Dim tmp As String
* tmp = mode
* mode = party
* party = tmp
* End If
*
**/
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;
}
if (salePurchase.transactionType == TransactionType.Sales)
{
mode = Modes.SaleAccount;
}
int ledgerNumber = util.GetAutoNumber(site, "LedgerNumber");
// opening balance
string opening_qry = "";
if (isOpeningBalance)
{
string credit_debit = "Debit";
if (salePurchase.drcr == DrCr.Debit)
{
credit_debit = "Debit";
}
else if (salePurchase.drcr == DrCr.Credit)
{
credit_debit = "Credit";
}
opening_qry = " INSERT INTO tblLedger ("
+ " LedgerNumber, Ldate, AccountMasterId, modeId, Particulars, " + credit_debit + ", sourceId, UserId, SubUserId, FYear )"
+ " VALUES ( "
+ ledgerNumber
+ ", '" + salePurchase.BillDate + "' "
+ ", " + party
+ ", " + mode
+ ", '" + salePurchase.particulars + "' "
+ ", " + salePurchase.SalePurchaseAmount
+ ", " + salePurchase.transactionType
+ ", " + util.GetUserInsertQry(Util_BLL.User)
+ " ) ";
site.Execute(opening_qry);
}
// sales - party
string party_qry = " INSERT INTO tblLedger ("
+ " LedgerNumber, drcr, Ldate, AccountMasterId, modeId, BillNumber, Particulars, credit, Discount, sourceId, UserId, SubuserId, FYear )"
+ " VALUES ( "
+ ledgerNumber
+ ", 'C' "
+ ", '" + salePurchase.BillDate + "' "
+ ", " + party
+ ", " + mode
+ ", '" + util.GetLOVName(salePurchase.BillNumber) + "' "
+ ", '" + salePurchase.particulars + "' "
+ ", " + salePurchase.SalePurchaseAmount
+ ", '" + salePurchase.discount + "'"
+ ", " + salePurchase.transactionType
+ ", " + util.GetUserInsertQry(Util_BLL.User)
+ " ) ";
// sales - party
string mode_qry = " INSERT INTO tblLedger ("
+ " LedgerNumber, drcr, Ldate, AccountMasterId, modeId, BillNumber, Particulars, debit, Discount, sourceId, UserId, SubuserId, FYear )"
+ " VALUES ( "
+ ledgerNumber
+ ", 'D' "
+ ", '" + salePurchase.BillDate + "' "
+ ", " + mode
+ ", " + party
+ ", '" + util.GetLOVName(salePurchase.BillNumber) + "' "
+ ", '" + salePurchase.particulars + "' "
+ ", " + salePurchase.SalePurchaseAmount
+ ", '" + salePurchase.discount + "'"
+ ", " + salePurchase.transactionType
+ ", " + util.GetUserInsertQry(Util_BLL.User)
+ " ) ";
site.Execute(party_qry);
site.Execute(mode_qry);
if (salePurchase.transactionType != TransactionType.PaymentMade && salePurchase.transactionType != TransactionType.PaymentRcd)
{
string tax_qry = "";
for (int i = 0; i < salePurchase.SalePurchaseTaxes.Count; i++)
{
tax_qry = " INSERT INTO tblTax(LedgerNumber, TaxMasterId, TaxAmount, UserID, SubuserId, FYear) "
+ " VALUES ( "
+ ledgerNumber
+ ", " + salePurchase.SalePurchaseTaxes[i].TaxId
+ ", " + salePurchase.SalePurchaseTaxes[i].TaxAmount
+ ", " + util.GetUserInsertQry(Util_BLL.User)
+ " ) ";
site.Execute(tax_qry);
}
SaveProductLedger(site, salePurchase, ledgerNumber);
}
}
public void SaveStore(DBSite site, StockMovement store, int update = 0)
{
string qry = "";
int productLedgerNumber = 0;
if (update == 1)
{
productLedgerNumber = store.ProductLedgerNumber;
}
else
{
productLedgerNumber = util.GetAutoNumber(site, "ProductLedgerNumber");
}
foreach (ProductDetail pd in store.ProductDetails)
{
qry = " INSERT INTO tblProductLedger(ProductLedgerNumber, drcr";
if (pd.bought_qty > 0)
{
qry = qry + " , BoughtQty ";
}
else
{
qry = qry + " , SoldQty ";
}
qry = qry + ", AccountId, BillDate, ProductID, SourceId, LocationID, Particulars, ToNarration, ProductId2, BoughtQty2, SoldQty2, locationId2, UserID, subuserId, FYear) "
+ " VALUES ( "
+ productLedgerNumber;
if (pd.bought_qty > 0)
{
qry = qry + ", 'C'"
+ ", " + pd.bought_qty;
}
else
{
qry = qry + ", 'D'"
+ ", " + pd.sold_qty;
}
qry = qry + ", '" + pd.AccountId + "'"
+ ", '" + store.MovementDate + "'"
+ ", " + pd.ProductId
+ ", " + store.TransactionTypeId
+ ", " + pd.LocationId
+ ", '" + store.Narration + "' "
+ ", '" + pd.ToNarration + "' "
+ ", " + pd.ProductId2
+ ", " + pd.bought_qty2
+ ", " + pd.sold_qty2
+ ", " + pd.LocationId2
+ ", " + util.GetUserInsertQry(Util_BLL.User)
+ " ) ";
site.Execute(qry);
}
}
