public static void InsertSub(Purchaseitems v, DAL dalsession)
{
string q = " INSERT INTO " + PURCHASEITEMS.PURCHASEITEMS_TBL + " ( ";
q += " " + PURCHASEITEMS.PURCHASE_ID + " \r\n ";
q += "," + PURCHASEITEMS.PURCHASE_NO + " \r\n ";
q += "," + PURCHASEITEMS.PRODUCT_ID + " \r\n ";
q += "," + PURCHASEITEMS.QTY + " \r\n ";
q += "," + PURCHASEITEMS.PRICE + " \r\n ";
q += "," + PURCHASEITEMS.CGST_PERCENT + " \r\n ";
q += "," + PURCHASEITEMS.SGST_PERCENT + " \r\n ";
q += "," + PURCHASEITEMS.IGST_PERCENT + " \r\n ";
q += "," + PURCHASEITEMS.REFERED_ID + " \r\n ";
q += "," + PURCHASEITEMS.LOCKED + " \r\n ";
q += " ) VALUES ( ";
q += " @" + PURCHASEITEMS.PURCHASE_ID + "@ \r\n ";
q += ", @" + PURCHASEITEMS.PURCHASE_NO + "@ \r\n ";
q += ", @" + PURCHASEITEMS.PRODUCT_ID + "@ \r\n ";
q += ", @" + PURCHASEITEMS.QTY + "@ \r\n ";
q += ", @" + PURCHASEITEMS.PRICE + "@ \r\n ";
q += ", @" + PURCHASEITEMS.CGST_PERCENT + "@ \r\n ";
q += ", @" + PURCHASEITEMS.SGST_PERCENT + "@ \r\n ";
q += ", @" + PURCHASEITEMS.IGST_PERCENT + "@ \r\n ";
q += ", @" + PURCHASEITEMS.REFERED_ID + "@ \r\n ";
q += ", @" + PURCHASEITEMS.LOCKED + "@ \r\n ";
q += " ) \r\n ";
q = AttachParams(q, v);
dalsession.Execute(q);
}
private static List <Purchaseitems> EntityList(string q)
{
List <Purchaseitems> list = new List <Purchaseitems>();
using (IDataReader redr = new DAL().Listreader(q))
{
while (redr.Read() == true)
{
Purchaseitems obj = new Purchaseitems()
{
Purchaseitems_id = redr[PURCHASEITEMS.PURCHASEITEMS_ID].ToString(),
Purchase_id = redr[PURCHASEITEMS.PURCHASE_ID].ToString(),
Purchase_no = redr[PURCHASEITEMS.PURCHASE_NO].ToString(),
//Po_id = redr[PO.PO_NO].ToString(),
//Dc_id = redr[DC.DC_NO].ToString(),
//Dcitems_id = redr[PURCHASEITEMS.DCITEMS_ID].ToString(),
Product_id = redr[PRODUCT.PRODUCT_NAME].ToString(),
Qty = ConvertTO.Decimal(redr[PURCHASEITEMS.QTY].ToString()),
Price = ConvertTO.Decimal(redr[PURCHASEITEMS.PRICE].ToString()),
Cgst_percent = ConvertTO.Decimal(redr[PURCHASEITEMS.CGST_PERCENT].ToString()),
Sgst_percent = ConvertTO.Decimal(redr[PURCHASEITEMS.SGST_PERCENT].ToString()),
Igst_percent = ConvertTO.Decimal(redr[PURCHASEITEMS.IGST_PERCENT].ToString()),
Refered_id = redr[PURCHASEITEMS.REFERED_ID].ToString(),
Locked = redr[PURCHASEITEMS.LOCKED].ToString(),
};
list.Add(obj);
}
}
return(list);
}
private static string AttachParams(string q, Purchaseitems v)
{
q = q.Replace("@" + PURCHASEITEMS.PURCHASE_ID + "@", "" + ConvertTO.SqlString(v.Purchase_id) + "");
q = q.Replace("@" + PURCHASEITEMS.PURCHASE_NO + "@", "" + ConvertTO.SqlString(v.Purchase_no) + "");
q = q.Replace("@" + PURCHASEITEMS.PRODUCT_ID + "@", "" + ConvertTO.SqlString(v.Product_id) + "");
q = q.Replace("@" + PURCHASEITEMS.QTY + "@", "" + ConvertTO.SqlString(v.Qty + ""));
q = q.Replace("@" + PURCHASEITEMS.PRICE + "@", "" + ConvertTO.SqlString(v.Price + ""));
q = q.Replace("@" + PURCHASEITEMS.CGST_PERCENT + "@", "" + ConvertTO.SqlString(v.Cgst_percent + ""));
q = q.Replace("@" + PURCHASEITEMS.SGST_PERCENT + "@", "" + ConvertTO.SqlString(v.Sgst_percent + ""));
q = q.Replace("@" + PURCHASEITEMS.IGST_PERCENT + "@", "" + ConvertTO.SqlString(v.Igst_percent + ""));
q = q.Replace("@" + PURCHASEITEMS.REFERED_ID + "@", "" + ConvertTO.SqlString(v.Refered_id) + "");
q = q.Replace("@" + PURCHASEITEMS.LOCKED + "@", "" + ConvertTO.SqlString(v.Locked) + "");
return(q);
}
public static List <Purchaseitems> FKNo(string id, DAL DAL)
{
string q = " SELECT " + PURCHASEITEMS.PURCHASEITEMS_TBL + ".* \r\n";
q += ", " + PRODUCT.PRODUCT_TBL + "." + PRODUCT.PRODUCT_NAME + " \r\n";
q += " FROM " + PURCHASEITEMS.PURCHASEITEMS_TBL + " \r\n";
q += " INNER JOIN " + PRODUCT.PRODUCT_TBL + "\r\n";
q += " ON(" + PURCHASEITEMS.PURCHASEITEMS_TBL + "." + PURCHASEITEMS.PRODUCT_ID + " = " + PRODUCT.PRODUCT_TBL + "." + PRODUCT.PRODUCT_ID + " )\r\n";
q += " WHERE " + PURCHASEITEMS.PURCHASEITEMS_TBL + "." + PURCHASE.PURCHASE_ID + " = '" + id + "' \r\n";
q += " ORDER BY " + PURCHASEITEMS.PURCHASEITEMS_TBL + "." + PURCHASEITEMS.PURCHASEITEMS_ID + ";\r\n";
//string q = " SELECT * FROM " + PURCHASEITEMS.PURCHASEITEMS_TBL + " \r\n";
//q += " WHERE " + PURCHASEITEMS.PURCHASEITEMS_TBL + "." + PURCHASE.PURCHASE_ID + " = '" + id + "' \r\n";
//q += " ORDER BY " + PURCHASEITEMS.PURCHASEITEMS_TBL + "." + PURCHASEITEMS.PURCHASEITEMS_ID + ";\r\n";
List <Purchaseitems> list = new List <Purchaseitems>();
using (IDataReader redr = new DAL().Listreader(q))
{
while (redr.Read() == true)
{
Purchaseitems obj = new Purchaseitems()
{
Purchaseitems_id = redr[PURCHASEITEMS.PURCHASEITEMS_ID].ToString(),
Purchase_id = redr[PURCHASEITEMS.PURCHASE_ID].ToString(),
Purchase_no = redr[PURCHASEITEMS.PURCHASE_NO].ToString(),
//Po_id = redr[PURCHASEITEMS.PO_ID].ToString(),
//Dc_id = redr[PURCHASEITEMS.DC_ID].ToString(),
//Dcitems_id = redr[PURCHASEITEMS.DCITEMS_ID].ToString(),
Product_id = redr[PRODUCT.PRODUCT_NAME].ToString(),
Qty = ConvertTO.Decimal(redr[PURCHASEITEMS.QTY].ToString()),
Price = ConvertTO.Decimal(redr[PURCHASEITEMS.PRICE].ToString()),
Cgst_percent = ConvertTO.Decimal(redr[PURCHASEITEMS.CGST_PERCENT].ToString()),
Sgst_percent = ConvertTO.Decimal(redr[PURCHASEITEMS.SGST_PERCENT].ToString()),
Igst_percent = ConvertTO.Decimal(redr[PURCHASEITEMS.IGST_PERCENT].ToString()),
Refered_id = redr[PURCHASEITEMS.REFERED_ID].ToString(),
Locked = redr[PURCHASEITEMS.LOCKED].ToString(),
};
list.Add(obj);
}
}
return(list);
}
public static void Insert(List <Purchaseitems> list, DAL dalsession)
{
for (int i = 0; i < list.Count; i++)
{
Purchaseitems obj = new Purchaseitems()
{
Purchase_id = list[i].Purchase_id,
Purchase_no = list[i].Purchase_no,
Product_id = list[i].Product_id,
Qty = list[i].Qty,
Price = list[i].Price,
Cgst_percent = list[i].Cgst_percent,
Sgst_percent = list[i].Sgst_percent,
Igst_percent = list[i].Igst_percent,
Refered_id = list[i].Refered_id,
Locked = list[i].Locked
};
InsertSub(obj, dalsession);
}
}
private static List <Purchaseitems> GetOldItemsData(int id, string OldDB)
{
string q = "USE " + OldDB + "; ";
q += " SELECT " + PURCHASEITEMS.PURCHASEITEMS_TBL + ".* \r\n";
q += ", " + PRODUCT.PRODUCT_TBL + "." + PRODUCT.PRODUCT_NAME + " \r\n";
q += " FROM " + PURCHASEITEMS.PURCHASEITEMS_TBL + " \r\n";
q += " INNER JOIN " + PRODUCT.PRODUCT_TBL + "\r\n";
q += " ON(" + PURCHASEITEMS.PURCHASEITEMS_TBL + "." + PURCHASEITEMS.PRODUCT_ID + " = " + PRODUCT.PRODUCT_TBL + "." + PRODUCT.PRODUCT_ID + " )\r\n";
q += " WHERE " + PURCHASEITEMS.PURCHASEITEMS_TBL + "." + PURCHASE.PURCHASE_ID + " = '" + id + "' \r\n";
q += " ORDER BY " + PURCHASEITEMS.PURCHASEITEMS_TBL + "." + PURCHASEITEMS.PURCHASEITEMS_ID + ";\r\n";
List <Purchaseitems> list = new List <Purchaseitems>();
using (IDataReader redr = new DAL().Listreader(q))
{
while (redr.Read() == true)
{
Purchaseitems obj = new Purchaseitems()
{
Purchaseitems_id = "",
Purchase_id = "",
Product_id = (redr[PRODUCT.PRODUCT_NAME].ToString()),
Sizes_id = "1",
Colours_id = "1",
Qty = ConvertTO.Decimal(redr[PURCHASEITEMS.QTY].ToString()),
Price = ConvertTO.Decimal(redr[PURCHASEITEMS.PRICE].ToString()),
Cgst_percent = ConvertTO.Decimal(redr[PURCHASEITEMS.CGST_PERCENT].ToString()),
Sgst_percent = ConvertTO.Decimal(redr[PURCHASEITEMS.SGST_PERCENT].ToString()),
Igst_percent = ConvertTO.Decimal(redr[PURCHASEITEMS.IGST_PERCENT].ToString()),
Refered_id = "0",
Locked = "0",
};
list.Add(obj);
}
return(list);
}
}
private static void InsertData(Purchase obj, List <Purchaseitems> items, string newDB)
{
DAL dalsession;
try
{
dalsession = new DAL();
dalsession.BeginTransaction();
List <Purchaseitems> list_new = new List <Purchaseitems>();
if (obj != null)
{
if (CheckData(obj, newDB) != true)
{
Purchase obj_new = new Purchase
{
Uniqueno = obj.Uniqueno,
Company_id = obj.Company_id,
Acy_id = obj.Acy_id,
Order_id = "1",
Purchase_no = obj.Purchase_no,
Entry_no = obj.Entry_no,
Purchase_date = obj.Purchase_date,
Party_id = CParty_exten.GetId_Name(obj.Party_id),
Taxtype_id = CTaxtype_exten.GetId_Name(obj.Taxtype_id),
Salestype_id = "1",
Total_qty = obj.Total_qty,
Total_taxable_amount = obj.Total_taxable_amount,
Total_cgst = obj.Total_cgst,
Total_sgst = obj.Total_sgst,
Total_igst = obj.Total_igst,
Gsttotal = obj.Gsttotal,
Ledger_id = CLedger_exten.GetName_Id(obj.Ledger_id),
Additional = obj.Additional,
Rounds = obj.Rounds,
Grandtotal = obj.Grandtotal,
Refered_amt = obj.Refered_amt,
Locked = obj.Locked,
Notes = obj.Notes,
Active_id = obj.Active_id,
User_id = "1"
};
//switch (obj.Company_id)
//{
// case "1":
// obj_new.Company_id = "1";
// obj_new.Acy_id = "1";
// break;
// case "3":
// obj_new.Company_id = "1";
// obj_new.Acy_id = "2";
// break;
// case "4":
// obj_new.Company_id = "1";
// obj_new.Acy_id = "3";
// break;
//}
obj_new.Uniqueno = obj_new.Acy_id + "~" + obj_new.Company_id + "~" + obj_new.Purchase_no;
CPurchase.Insert(obj_new, dalsession);
var vId = DAL.GetIdentity(dalsession);
for (int k = 0; k < items.Count; k++)
{
Purchaseitems v = new Purchaseitems()
{
Purchase_id = vId,
Product_id = CProduct_exten.GetId_Name(items[k].Product_id),
Sizes_id = CSizes_exten.GetId_Name(items[k].Sizes_id),
Colours_id = "1",
Qty = items[k].Qty,
Price = items[k].Price,
Cgst_percent = items[k].Cgst_percent,
Sgst_percent = items[k].Sgst_percent,
Igst_percent = items[k].Igst_percent,
Refered_id = items[k].Refered_id,
Locked = items[k].Locked
};
list_new.Add(v);
}
CPurchaseitems.Insert(list_new, dalsession);
dalsession.Commit();
}
}
}
catch (Exception ex)
{
MessageBox.Show("error : " + ex);
}
}
private List <Purchaseitems> Collectitems(string vId)
{
List <Purchaseitems> list = new List <Purchaseitems>();
for (int r = 0; r < editgrid.RowCount; r++)
{
Purchaseitems obj = new Purchaseitems();
if (
(editgrid[PURCHASEITEMS.PRODUCT_ID, r].Value + "") == "" ||
(editgrid[PURCHASEITEMS.QTY, r].Value + "") == "" ||
(editgrid[PURCHASEITEMS.PRICE, r].Value + "") == ""
)
{
//==LEAVE IT(the empty row).
}
else
{
obj.Purchaseitems_id = "";
obj.Purchase_id = vId;
obj.Purchase_no = txt_purchase_no.Text;
//if (Customise.Purchase_poid_dcid == true)
//{
// if (((editgrid[PURCHASEITEMS.PO_ID, r].Value + "") == "") || ((editgrid[PURCHASEITEMS.PO_ID, r].Value + "") == Core.Without_Po) || ((editgrid[PURCHASEITEMS.PO_ID, r].Value + "") == Core._Without_po_))
// {
// obj.Po_id = "1";
// obj.Poitems_id = "1";
// }
// else
// {
// obj.Po_id = CPo_exten.GetId_Name(editgrid[PURCHASEITEMS.PO_ID, r].Value + "");
// obj.Poitems_id = editgrid[PURCHASEITEMS.POITEMS_ID, r].Value + "";
// }
//}
//if (Customise.Purchase_pono_dcno == true)
//{
// obj.Po_id = (editgrid[PURCHASEITEMS.PO_ID, r].Value + "");
// obj.Poitems_id = editgrid[PURCHASEITEMS.POITEMS_ID, r].Value + "";
//}
//if (Customise.Purchase_poid_dcid == true)
//{
// if (((editgrid[PURCHASEITEMS.DC_ID, r].Value + "") == "") || ((editgrid[PURCHASEITEMS.DC_ID, r].Value + "") == Core.Without_Dc) || ((editgrid[PURCHASEITEMS.DC_ID, r].Value + "") == "0"))
// {
// obj.Dc_id = "1";
// obj.Dcitems_id = "1";
// }
// else
// {
// obj.Dc_id = CDc_exten.GetId_Name(editgrid[PURCHASEITEMS.DC_ID, r].Value + "");
// obj.Dcitems_id = (editgrid[PURCHASEITEMS.DCITEMS_ID, r].Value + "");
// if (obj.Dcitems_id == "") { obj.Dcitems_id = "1"; }
// }
//}
//if (Customise.Purchase_pono_dcno == true)
//{
// obj.Dc_id = (editgrid[PURCHASEITEMS.DC_ID, r].Value + "");
// obj.Dcitems_id = (editgrid[PURCHASEITEMS.DCITEMS_ID, r].Value + "");
//}
obj.Product_id = CProduct_exten.GetId_Name(Checks.Escape(editgrid[PURCHASEITEMS.PRODUCT_ID, r].Value + ""));
obj.Qty = ConvertTO.Decimal(editgrid[PURCHASEITEMS.QTY, r].Value + "");
obj.Price = ConvertTO.Decimal(editgrid[PURCHASEITEMS.PRICE, r].Value + "");
obj.Cgst_percent = ConvertTO.Decimal(editgrid[PURCHASEITEMS.CGST_PERCENT, r].Value + "");
obj.Sgst_percent = ConvertTO.Decimal(editgrid[PURCHASEITEMS.SGST_PERCENT, r].Value + "");
obj.Igst_percent = ConvertTO.Decimal(editgrid[PURCHASEITEMS.IGST_PERCENT, r].Value + "");
obj.Refered_id = "";
obj.Locked = Core.Unlocked;
list.Add(obj);
}
}
if (list.Count != 0)
{
return(list);
}
return(null);
}