public JsonResult SaveOrder(BillVm O)
{
bool status = false;
if (ModelState.IsValid)
{
//Order order = new Order { OrderNo = O.OrderNo, OrderDate = O.OrderDate, Description = O.Description };
BillRegister billregister = new BillRegister {
BillDate = O.billdate, Remarks = O.remarks, memberId = O.memberid, paid = "N"
};
db.BillRegisters.Add(billregister);
db.SaveChanges();
foreach (var i in O.BillDetail)
{
//
// i.TotalAmount =
Billdetail billdetail = new Billdetail();
billdetail.BillID = billregister.BillID;
billdetail.HeaderId = i.headerid;
billdetail.Amount = i.amount;
db.Billdetail.Add(billdetail);
db.SaveChanges();
}
status = true;
}
else
{
status = false;
}
return(new JsonResult {
Data = new { status = status }
});
}
private BillRegister GetDateByBillNumber(IDataReader reader)
{
BillRegister billRegister = new BillRegister();
billRegister.RegisterConsignment = new Consignment();
billRegister.Date = GetStringFromDataReader(reader, "Date");
billRegister.BillNo = GetIntegerFromDataReader(reader, "BillNo");
billRegister.PaxName = GetStringFromDataReader(reader, "Pax");
billRegister.PartyName = GetStringFromDataReader(reader, "PartyName");
billRegister.VisaCountry = GetStringFromDataReader(reader, "VisaCountry");
billRegister.Visa = GetDecimalFromDataReader(reader, "VisaCharge");
billRegister.Attestation = GetDecimalFromDataReader(reader, "AttestationCharge");
billRegister.Token = GetDecimalFromDataReader(reader, "TokenCharge");
billRegister.Vfs = GetDecimalFromDataReader(reader, "VFS/BLS/TTS");
billRegister.Handling = GetDecimalFromDataReader(reader, "Handling");
billRegister.Courior = GetDecimalFromDataReader(reader, "Courier");
billRegister.Delivery = GetDecimalFromDataReader(reader, "Delivery");
billRegister.Convenience = GetDecimalFromDataReader(reader, "Drop") + GetDecimalFromDataReader(reader, "Cargo_Pick");
billRegister.Urgent = GetDecimalFromDataReader(reader, "Urgent");
billRegister.Draft = GetDecimalFromDataReader(reader, "Draft");
billRegister.Insurance = GetDecimalFromDataReader(reader, "Insurance");
billRegister.OtherCharge = GetDecimalFromDataReader(reader, "OtherCharges");
billRegister.ServiceCharge = GetDecimalFromDataReader(reader, "ServiceCharge");
billRegister.ServiceTax = GetDecimalFromDataReader(reader, "S_Tax");
billRegister.Amount = GetDecimalFromDataReader(reader, "Amount");
billRegister.RegisterConsignment.CG_ID = GetStringFromDataReader(reader, "ConsimentId");
billRegister.RegisterConsignment.CgNoOfPass = GetIntegerFromDataReader(reader, "Passport");
billRegister.RegisterConsignment.CgCorporate = GetStringFromDataReader(reader, "Corporate");
return(billRegister);
}
public List <BillRegister> ReadBillRegisterInformationByDate(DateTime?frDate, DateTime?toDate, int?AgentId, int?LocId)
{
//if (LocId != 0)
//{
// queryBill = "Select CONVERT(VARCHAR(20), BILL.BILL_DATE, 103) AS 'Date',CONVERT(VARCHAR,BILL.BILL_ID,20) AS 'BillNo',UPPER(ISNULL(AGENT.TALLY_ACNAME, AGENT.AGENT_NAME)) AS 'PartyName', UPPER(Bill.PAXS) as Pax,left(UPPER(COUNTRY.COUNTRY_NAME),3) AS 'VisaCountry', SUM(ISNULL(VISA.FEE,0)) AS 'VisaCharge', SUM(ISNULL(ATTEST.FEE,0)) AS 'AttestationCharge', SUM(ISNULL(Token.FEE,0)) as 'TokenCharge', SUM(ISNULL(VFS.FEE,0)) as 'VFS/BLS/TTS', SUM(ISNULL(HANDLING.FEE,0)) as 'Handling', SUM(ISNULL(Courier.FEE,0)) as 'Courier', SUM(ISNULL(Draft.FEE,0)) as 'Draft', SUM(ISNULL(Delivery.FEE,0)) as 'Delivery', SUM(ISNULL(Dropc.FEE,0)) as 'Drop', SUM(ISNULL(Pickup.FEE,0)) as 'Cargo_Pick', SUM(ISNULL(Urgent.FEE,0)) as 'Urgent', SUM(ISNULL(Insurance.FEE,0)) as 'Insurance', (CustomBillTotal.TotalAmount+Bill.ServiceCharge +(Bill.servicecharge*Bill.ServiceTax)/100 - (Bill.servicecharge + sum(isnull(VFS.FEE,0)) + SUM(ISNULL(Visa.Fee,0))+ sum(isnull(ATTEST.FEE,0)) + sum(isnull(Token.FEE,0))+ sum(isnull(Handling.FEE,0))+ sum(isnull(Courier.FEE,0)) + sum(isnull(Delivery.FEE,0))+ sum(isnull(Dropc.FEE,0))+ sum(isnull(Pickup.FEE,0)) + sum(isnull(Urgent.FEE,0))+ sum(isnull(Draft.FEE,0)) + sum(isnull(Insurance.FEE,0)) +(Bill.servicecharge*Bill.ServiceTax)/100)) as 'OtherCharges', Bill.servicecharge As 'ServiceCharge', (BILL.SERVICETAX) as 'S_Tax', (CustomBillTotal.TotalAmount+Bill.ServiceCharge + Bill.ServiceTax) as 'Amount' FROM Bill INNER JOIN CONSIGNMENT ON CONSIGNMENT.CG_ID=BILL.FK_CG_ID INNER JOIN AGENT ON AGENT.AGENT_ID=CONSIGNMENT.FK_AGENT_ID INNER JOIN COUNTRY ON COUNTRY.COUNTRY_ID=CONSIGNMENT.CG_VISACOUNTRY LEFT JOIN (Select SUM(ISNULL(tem.FEE*tem.NoOfVisa,0)) As TotalAmount,BillID as FK_BillMSTR_ID from (Select BD.FK_BILL_ID as BillID,'' as NoOfVisa,(BD.CHARGE_PERUNIT*BD.UNITS) as FEE, '' as Description FROM BILL_DETAILS BD UNION Select VD.FK_BillMSTR_ID as BillID,VD.NoOfVisa as NoOfVisa,VISAFEES as FEE,VD.Description as Description FROM dbo.BILL_VISAFEES VD )tem Group by tem.BillID) AS CustomBillTotal ON CustomBillTotal.FK_BillMSTR_ID=BILL.BILL_ID LEFT JOIN (Select BD.FK_BillMSTR_ID AS BillID,SUM(ISNULL(BD.VisaFees*NoOfVisa,0)) AS FEE from Bill_VisaFees BD Where BD.Description Like '%Attest%' Group by BD.FK_BillMSTR_ID) AS ATTEST ON BILL.BILL_ID=ATTEST.BillID LEFT JOIN (Select VD.FK_BillMSTR_ID as BillID,SUM(ISNULL(VISAFEES*NoOfVisa,0)) as FEE FROM BILL_VISAFEES VD WHERE Description Like '%Visa%' GROUP BY VD.FK_BillMSTR_ID) AS VISA ON VISA.BillID=BILL.BILL_ID LEFT JOIN (Select BD.FK_BillMSTR_ID AS BILLID,SUM(ISNULL(BD.VisaFees*NoOfVisa,0)) AS FEE from Bill_VisaFees BD Where BD.Description Like '%Token%' Group by BD.FK_BillMSTR_ID) as Token ON BILL.BILL_ID=TOKEN.BILLID LEFT JOIN (Select VD.FK_BillMSTR_ID as BillID,SUM(ISNULL(VISAFEES*NoOfVisa,0)) as FEE FROM BILL_VISAFEES VD Where VD.Description Like '%VFS/BLS/TTS%' Group by VD.FK_BillMSTR_ID) as VFS ON VFS.BILLID=BILL.BILL_ID LEFT JOIN (Select VD.FK_BillMSTR_ID as BillID,SUM(ISNULL(VISAFEES*NoOfVisa,0)) as FEE FROM BILL_VISAFEES VD Where VD.Description Like '%Handling%' Group by VD.FK_BillMSTR_ID) AS HANDLING ON HANDLING.BILLID=BILL.BILL_ID LEFT JOIN (Select VD.FK_BillMSTR_ID as BillID,SUM(ISNULL(VISAFEES*NoOfVisa,0)) as FEE FROM BILL_VISAFEES VD Where VD.Description Like '%Courier%' Group by VD.FK_BillMSTR_ID) AS Courier ON COURIER.BILLID=BILL.BILL_ID LEFT JOIN (Select VD.FK_BillMSTR_ID as BillID,SUM(ISNULL(VISAFEES,0)) as FEE FROM BILL_VISAFEES VD Where VD.Description Like '%Delivery%' Group by VD.FK_BillMSTR_ID) AS Delivery ON Delivery.BILLID=BILL.BILL_ID LEFT JOIN (Select VD.FK_BillMSTR_ID as BillID,SUM(ISNULL(VISAFEES,0)) as FEE FROM dbo.BILL_VISAFEES VD Where VD.Description Like '%Dropc%' Group by VD.FK_BillMSTR_ID) AS Dropc ON Dropc.BillId=BILL.BILL_ID LEFT join (Select VD.FK_BillMSTR_ID as BillID,SUM(ISNULL(VISAFEES*NoOfVisa,0)) as FEE FROM BILL_VISAFEES VD Where VD.Description Like '%Pickup%' Group by VD.FK_BillMSTR_ID) AS Pickup ON Pickup.BILLID=BILL.BILL_ID LEFT JOIN (Select VD.FK_BillMSTR_ID as BillID,SUM(ISNULL(VISAFEES,0)) as FEE FROM dbo.BILL_VISAFEES VD Where VD.Description Like '%Urgent%' Group by VD.FK_BillMSTR_ID) AS Urgent ON Urgent.BILLID=BILL.BILL_ID LEFT JOIN (Select VD.FK_BillMSTR_ID as BillID,SUM(ISNULL(VISAFEES*NoOfVisa,0)) as FEE FROM BILL_VISAFEES VD Where VD.Description Like '%Draft%' Group by VD.FK_BillMSTR_ID) AS Draft ON Draft.BILLID=BILL.BILL_ID LEFT JOIN (Select VD.FK_BillMSTR_ID as BillID,SUM(ISNULL(VISAFEES,0)) as FEE FROM dbo.BILL_VISAFEES VD Where VD.Description Like '%Insurance%' Group by VD.FK_BillMSTR_ID) AS Insurance ON Insurance.BILLID=BILL.BILL_ID Where Bill.ISCANCELLED <>'Y' AND CAST(CONVERT(VARCHAR(10), BILL.BILL_DATE, 111) AS DATETIME)>=CAST(CONVERT(VARCHAR(20),'" + fromDate.ToShortDateString() + "' , 131)AS DATETIME) AND CAST(CONVERT(VARCHAR(10), BILL.BILL_DATE, 111) AS DATETIME)<=CAST(CONVERT(VARCHAR(20),'" + toDate.ToShortDateString() + "' , 131) AS DATETIME) AND AGENT.AGENT_ID = " + AgentId + " and BILL.LocationId=" + LocId + " Group by BILL.BILL_DATE,Bill.Bill_id,Bill.PAXS,AGENT.TALLY_ACNAME,AGENT.AGENT_NAME, VISA.FEE,Token.FEE,VFS.FEE,HANDLING.FEE,Courier.FEE,Delivery.FEE,Dropc.FEE, Pickup.FEE,Urgent.FEE,Draft.FEE,Insurance.FEE, COUNTRY.COUNTRY_NAME,Bill.servicecharge,Bill.TotalAmt,Bill.ServiceTax,CustomBillTotal.TotalAmount ORDER BY BILL.BILL_ID asc";
//}
//else
//{
// queryBill = "Select CONVERT(VARCHAR(20), BILL.BILL_DATE, 103) AS 'Date',CONVERT(VARCHAR,BILL.BILL_ID,20) AS 'BillNo',UPPER(ISNULL(AGENT.TALLY_ACNAME, AGENT.AGENT_NAME)) AS 'PartyName', UPPER(Bill.PAXS) as Pax,left(UPPER(COUNTRY.COUNTRY_NAME),3) AS 'VisaCountry', SUM(ISNULL(VISA.FEE,0)) AS 'VisaCharge', SUM(ISNULL(ATTEST.FEE,0)) AS 'AttestationCharge', SUM(ISNULL(Token.FEE,0)) as 'TokenCharge', SUM(ISNULL(VFS.FEE,0)) as 'VFS/BLS/TTS', SUM(ISNULL(HANDLING.FEE,0)) as 'Handling', SUM(ISNULL(Courier.FEE,0)) as 'Courier', SUM(ISNULL(Draft.FEE,0)) as 'Draft', SUM(ISNULL(Delivery.FEE,0)) as 'Delivery', SUM(ISNULL(Dropc.FEE,0)) as 'Drop', SUM(ISNULL(Pickup.FEE,0)) as 'Cargo_Pick', SUM(ISNULL(Urgent.FEE,0)) as 'Urgent', SUM(ISNULL(Insurance.FEE,0)) as 'Insurance', (CustomBillTotal.TotalAmount+Bill.ServiceCharge +(Bill.servicecharge*Bill.ServiceTax)/100 - (Bill.servicecharge + sum(isnull(VFS.FEE,0)) + SUM(ISNULL(Visa.Fee,0))+ sum(isnull(ATTEST.FEE,0)) + sum(isnull(Token.FEE,0))+ sum(isnull(Handling.FEE,0))+ sum(isnull(Courier.FEE,0)) + sum(isnull(Delivery.FEE,0))+ sum(isnull(Dropc.FEE,0))+ sum(isnull(Pickup.FEE,0)) + sum(isnull(Urgent.FEE,0))+ sum(isnull(Draft.FEE,0)) + sum(isnull(Insurance.FEE,0)) +(Bill.servicecharge*Bill.ServiceTax)/100)) as 'OtherCharges', Bill.servicecharge As 'ServiceCharge', (BILL.SERVICETAX) as 'S_Tax', (CustomBillTotal.TotalAmount+Bill.ServiceCharge + Bill.ServiceTax) as 'Amount' FROM Bill INNER JOIN CONSIGNMENT ON CONSIGNMENT.CG_ID=BILL.FK_CG_ID INNER JOIN AGENT ON AGENT.AGENT_ID=CONSIGNMENT.FK_AGENT_ID INNER JOIN COUNTRY ON COUNTRY.COUNTRY_ID=CONSIGNMENT.CG_VISACOUNTRY LEFT JOIN (Select SUM(ISNULL(tem.FEE*tem.NoOfVisa,0)) As TotalAmount,BillID as FK_BillMSTR_ID from (Select BD.FK_BILL_ID as BillID,'' as NoOfVisa,(BD.CHARGE_PERUNIT*BD.UNITS) as FEE, '' as Description FROM BILL_DETAILS BD UNION Select VD.FK_BillMSTR_ID as BillID,VD.NoOfVisa as NoOfVisa,VISAFEES as FEE,VD.Description as Description FROM dbo.BILL_VISAFEES VD )tem Group by tem.BillID) AS CustomBillTotal ON CustomBillTotal.FK_BillMSTR_ID=BILL.BILL_ID LEFT JOIN (Select BD.FK_BillMSTR_ID AS BillID,SUM(ISNULL(BD.VisaFees*NoOfVisa,0)) AS FEE from Bill_VisaFees BD Where BD.Description Like '%Attest%' Group by BD.FK_BillMSTR_ID) AS ATTEST ON BILL.BILL_ID=ATTEST.BillID LEFT JOIN (Select VD.FK_BillMSTR_ID as BillID,SUM(ISNULL(VISAFEES*NoOfVisa,0)) as FEE FROM BILL_VISAFEES VD WHERE Description Like '%Visa%' GROUP BY VD.FK_BillMSTR_ID) AS VISA ON VISA.BillID=BILL.BILL_ID LEFT JOIN (Select BD.FK_BillMSTR_ID AS BILLID,SUM(ISNULL(BD.VisaFees*NoOfVisa,0)) AS FEE from Bill_VisaFees BD Where BD.Description Like '%Token%' Group by BD.FK_BillMSTR_ID) as Token ON BILL.BILL_ID=TOKEN.BILLID LEFT JOIN (Select VD.FK_BillMSTR_ID as BillID,SUM(ISNULL(VISAFEES*NoOfVisa,0)) as FEE FROM BILL_VISAFEES VD Where VD.Description Like '%VFS/BLS/TTS%' Group by VD.FK_BillMSTR_ID) as VFS ON VFS.BILLID=BILL.BILL_ID LEFT JOIN (Select VD.FK_BillMSTR_ID as BillID,SUM(ISNULL(VISAFEES*NoOfVisa,0)) as FEE FROM BILL_VISAFEES VD Where VD.Description Like '%Handling%' Group by VD.FK_BillMSTR_ID) AS HANDLING ON HANDLING.BILLID=BILL.BILL_ID LEFT JOIN (Select VD.FK_BillMSTR_ID as BillID,SUM(ISNULL(VISAFEES*NoOfVisa,0)) as FEE FROM BILL_VISAFEES VD Where VD.Description Like '%Courier%' Group by VD.FK_BillMSTR_ID) AS Courier ON COURIER.BILLID=BILL.BILL_ID LEFT JOIN (Select VD.FK_BillMSTR_ID as BillID,SUM(ISNULL(VISAFEES,0)) as FEE FROM BILL_VISAFEES VD Where VD.Description Like '%Delivery%' Group by VD.FK_BillMSTR_ID) AS Delivery ON Delivery.BILLID=BILL.BILL_ID LEFT JOIN (Select VD.FK_BillMSTR_ID as BillID,SUM(ISNULL(VISAFEES,0)) as FEE FROM dbo.BILL_VISAFEES VD Where VD.Description Like '%Dropc%' Group by VD.FK_BillMSTR_ID) AS Dropc ON Dropc.BillId=BILL.BILL_ID LEFT join (Select VD.FK_BillMSTR_ID as BillID,SUM(ISNULL(VISAFEES*NoOfVisa,0)) as FEE FROM BILL_VISAFEES VD Where VD.Description Like '%Pickup%' Group by VD.FK_BillMSTR_ID) AS Pickup ON Pickup.BILLID=BILL.BILL_ID LEFT JOIN (Select VD.FK_BillMSTR_ID as BillID,SUM(ISNULL(VISAFEES,0)) as FEE FROM dbo.BILL_VISAFEES VD Where VD.Description Like '%Urgent%' Group by VD.FK_BillMSTR_ID) AS Urgent ON Urgent.BILLID=BILL.BILL_ID LEFT JOIN (Select VD.FK_BillMSTR_ID as BillID,SUM(ISNULL(VISAFEES*NoOfVisa,0)) as FEE FROM BILL_VISAFEES VD Where VD.Description Like '%Draft%' Group by VD.FK_BillMSTR_ID) AS Draft ON Draft.BILLID=BILL.BILL_ID LEFT JOIN (Select VD.FK_BillMSTR_ID as BillID,SUM(ISNULL(VISAFEES,0)) as FEE FROM dbo.BILL_VISAFEES VD Where VD.Description Like '%Insurance%' Group by VD.FK_BillMSTR_ID) AS Insurance ON Insurance.BILLID=BILL.BILL_ID Where Bill.ISCANCELLED <>'Y' AND CAST(CONVERT(VARCHAR(10), BILL.BILL_DATE, 111) AS DATETIME)>=CAST(CONVERT(VARCHAR(20),'" + fromDate.ToShortDateString() + "' , 131)AS DATETIME) AND CAST(CONVERT(VARCHAR(10), BILL.BILL_DATE, 111) AS DATETIME)<=CAST(CONVERT(VARCHAR(20),'" + toDate.ToShortDateString() + "' , 131) AS DATETIME) AND AGENT.AGENT_ID = " + AgentId + " Group by BILL.BILL_DATE,Bill.Bill_id,Bill.PAXS,AGENT.TALLY_ACNAME,AGENT.AGENT_NAME, VISA.FEE,Token.FEE,VFS.FEE,HANDLING.FEE,Courier.FEE,Delivery.FEE,Dropc.FEE, Pickup.FEE,Urgent.FEE,Draft.FEE,Insurance.FEE, COUNTRY.COUNTRY_NAME,Bill.servicecharge,Bill.TotalAmt,Bill.ServiceTax,CustomBillTotal.TotalAmount ORDER BY BILL.BILL_ID asc";
//}
List <BillRegister> lstBillregister = new List <BillRegister>();
if (frDate != null && toDate != null)
{
DbCommand dbCommand = myDataBase.GetStoredProcCommand(DBConstant.READ_DATABY_DATE);
dbCommand.CommandTimeout = 1500;
string FromDate = frDate.Value.ToString("MM/dd/yyyy");
string todateDate = toDate.Value.ToString("MM/dd/yyyy");
myDataBase.AddInParameter(dbCommand, "in_FromDateA", DbType.String, FromDate);
myDataBase.AddInParameter(dbCommand, "in_ToDateB", DbType.String, todateDate);
if (AgentId != 0)
{
myDataBase.AddInParameter(dbCommand, "@inAgentId", DbType.Int32, AgentId);
}
else
{
myDataBase.AddInParameter(dbCommand, "@inAgentId", DbType.Int32, DBNull.Value);
}
using (IDataReader reader = myDataBase.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var billregisterDom = GenrateRecord(reader);
lstBillregister.Add(billregisterDom);
}
}
return(lstBillregister);
}
if (AgentId != null)
{
BillRegister billregisterDom = new BillRegister();
DbCommand dbCommand = myDataBase.GetStoredProcCommand(DBConstant.READ_DATABY_AGENTID);
dbCommand.CommandTimeout = 1500;
myDataBase.AddInParameter(dbCommand, "@in_Agent_Id", DbType.Int32, AgentId);
using (IDataReader reader = myDataBase.ExecuteReader(dbCommand))
{
while (reader.Read())
{
billregisterDom = GenrateRecord(reader);
lstBillregister.Add(billregisterDom);
}
}
return(lstBillregister);
}
return(lstBillregister);
}
public async Task <ActionResult> DeleteConfirmed(int id)
{
BillRegister billRegister = await db.BillRegisters.FindAsync(id);
db.BillRegisters.Remove(billRegister);
await db.SaveChangesAsync();
return(RedirectToAction("Index"));
}
public ActionResult Create(FormCollection form)
{
int temp = 0;
// Thread.Sleep(1000);
if (ModelState.IsValid)
{
List <MemberCompanyInfo> memberinfo = db.MemberCompanyInfo.Where(s => s.status != "P").ToList();
List <MonthlyBillSetUP> billsetup = db.MonthlyBillSetUPs.ToList();
// List<MemberCompanyInfo> memberinfo = db.MemberCompanyInfo.SqlQuery("Select * from MemberCompanyInfo Where status='P'").ToList();
// List<MonthlyBillSetUP> billsetup = db.MonthlyBillSetUPs.ToList();
foreach (var member in memberinfo)
{
BillRegister billregister = new BillRegister();
billregister.BillDate = Convert.ToDateTime(form["BillDate"]);
billregister.memberId = member.memberId;
billregister.paid = "N";
billregister.Remarks = form["Remarks"].ToString();
db.BillRegisters.Add(billregister);
db.SaveChanges();
Billdetail billdetail = new Billdetail();
foreach (var bs in billsetup)
{
billdetail.BillID = billregister.BillID;
billdetail.Amount = bs.Amount;
billdetail.HeaderId = bs.HeaderId;
db.Billdetail.Add(billdetail);
db.SaveChanges();
}
temp = 1;
}
}
// var result = new {
// res="Data Saved"
//};
//var result1 = new
//{
// res = "Error"
//};
//if (temp == 1)
//{
// return Json(result);
//}
//else
//{
// return Json(result1);
//}
return(View());
}
public List <BillRegister> BillRegisterByDate(DateTime?fromDate, DateTime?toDate, int?agentId, int?LocId)
{
List <BillRegister> lstBillRegister = new List <BillRegister>();
if (fromDate != null && toDate != null && agentId != null)
{
DbCommand dbCommand = myDataBase.GetStoredProcCommand(DBConstant.READ_BILLBY_BILLDATE);
string FromDate = fromDate.Value.ToString("MM/dd/yyyy");
string todateDate = toDate.Value.ToString("MM/dd/yyyy");
myDataBase.AddInParameter(dbCommand, "@in_from_date", DbType.DateTime, FromDate);
myDataBase.AddInParameter(dbCommand, "@in_to_date", DbType.DateTime, todateDate);
if (agentId != 0)
{
myDataBase.AddInParameter(dbCommand, "@inAgentId", DbType.Int32, agentId);
}
else
{
myDataBase.AddInParameter(dbCommand, "@inAgentId", DbType.Int32, DBNull.Value);
}
using (IDataReader reader = myDataBase.ExecuteReader(dbCommand))
while (reader.Read())
{
var billregisterDom = READ(reader);
lstBillRegister.Add(billregisterDom);
}
return(lstBillRegister);
}
if (agentId != null)
{
BillRegister billregisterDom = new BillRegister();
DbCommand dbCommand = myDataBase.GetStoredProcCommand(DBConstant.READ_BillRegisterBY_AGENTID);
myDataBase.AddInParameter(dbCommand, "@inAgentId", DbType.Int32, agentId);
if (LocId != 0)
{
myDataBase.AddInParameter(dbCommand, "@LocationId", DbType.Int32, LocId);
}
else
{
myDataBase.AddInParameter(dbCommand, "@LocationId", DbType.Int32, DBNull.Value);
}
using (IDataReader reader = myDataBase.ExecuteReader(dbCommand))
{
while (reader.Read())
{
billregisterDom = READ(reader);
lstBillRegister.Add(billregisterDom);
}
}
return(lstBillRegister);
}
return(lstBillRegister);
}
public async Task <ActionResult> Edit([Bind(Include = "BillID,BillDate,memberId,Remarks,paid")] BillRegister billRegister)
{
if (ModelState.IsValid)
{
db.Entry(billRegister).State = EntityState.Modified;
await db.SaveChangesAsync();
return(RedirectToAction("Index"));
}
ViewBag.memberId = new SelectList(db.MemberCompanyInfo, "memberId", "Organization_Name", billRegister.memberId);
return(View(billRegister));
}
// GET: BillRegisters/Details/5
public async Task <ActionResult> Details(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
BillRegister billRegister = await db.BillRegisters.FindAsync(id);
if (billRegister == null)
{
return(HttpNotFound());
}
return(View(billRegister));
}
public ActionResult GetUnpaidBIllById(int memberid)
{
BillRegister bill = db.BillRegisters.Find(memberid);
if (bill == null)
{
return(new HttpNotFoundResult());
}
var result = new
{
billid = bill.BillID,
billdate = bill.BillID.ToString() + '-' + bill.BillDate
};
return(Json(result, JsonRequestBehavior.AllowGet));
}
// GET: BillRegisters/Edit/5
public async Task <ActionResult> Edit(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
BillRegister billRegister = await db.BillRegisters.FindAsync(id);
if (billRegister == null)
{
return(HttpNotFound());
}
ViewBag.memberId = new SelectList(db.MemberCompanyInfo, "memberId", "Organization_Name", billRegister.memberId);
ViewBag.BillDetail = db.Billdetail.Include(p => p.Header).Where(s => s.BillID == id).ToList();
ViewBag.HeaderId = new SelectList(db.Headers.Where(s => s.HeaderType == "Income"), "HeaderId", "HeaderName", "Select Head");
return(View(billRegister));
}
public BillRegister READ(IDataReader reader)
{
BillRegister billRegister = new BillRegister();
billRegister.RegisterBill = new Bill();
billRegister.RegisterConsignment = new Consignment();
//billRegister.RegisterBill.Version = GetIntegerFromDataReader(reader, "Versioning");
billRegister.RegisterBill.BillId = GetIntegerFromDataReader(reader, "BILL_ID");
billRegister.RegisterConsignment.CgNoOfPass = GetIntegerFromDataReader(reader, "CG_NOOFPASS");
billRegister.RegisterConsignment.CgCorporate = GetStringFromDataReader(reader, "CG_CORPORATE");
billRegister.RegisterConsignment.ConsignmentId = GetIntegerFromDataReader(reader, "FK_CG_ID");
billRegister.Amount = GetDecimalFromDataReader(reader, "TotalAmt");
billRegister.RegisterBill.BillDate = GetDateFromReader(reader, "BILLDATE");
billRegister.RegisterBill.Paxs = GetStringFromDataReader(reader, "PAXS");
billRegister.VisaCountry = GetStringFromDataReader(reader, "COUNTRY_NAME");
return(billRegister);
}
public List <BillRegister> ReadBillRegisterBYFromBillNo(int fromBill, int toBill, int AgentId, int?LocId)
{
List <BillRegister> lst = new List <BillRegister>();
DbCommand dbCommand = myDataBase.GetStoredProcCommand(DBConstant.READ_BillBY_BILLID);
myDataBase.AddInParameter(dbCommand, "@in_fromBillId", DbType.String, fromBill);
myDataBase.AddInParameter(dbCommand, "@in_toBillId", DbType.String, toBill);
if (LocId != 0)
{
myDataBase.AddInParameter(dbCommand, "@LocationId", DbType.Int32, LocId);
}
else
{
myDataBase.AddInParameter(dbCommand, "@LocationId", DbType.Int32, DBNull.Value);
}
using (IDataReader reader = myDataBase.ExecuteReader(dbCommand))
{
while (reader.Read())
{
BillRegister billRegister = new BillRegister();
billRegister.RegisterBill = new Bill();
billRegister.RegisterConsignment = new Consignment();
billRegister.RegisterBill.Version = GetIntegerFromDataReader(reader, "Versioning");
billRegister.RegisterBill.BillId = GetIntegerFromDataReader(reader, "BILL_ID");
billRegister.RegisterConsignment.CgNoOfPass = GetIntegerFromDataReader(reader, "CG_NOOFPASS");
billRegister.RegisterConsignment.CgCorporate = GetStringFromDataReader(reader, "CG_CORPORATE");
billRegister.RegisterConsignment.ConsignmentId = GetIntegerFromDataReader(reader, "FK_CG_ID");
billRegister.Amount = GetDecimalFromDataReader(reader, "TotalAmt");
billRegister.RegisterBill.BillDate = GetDateFromReader(reader, "BILLDATE");
billRegister.RegisterBill.Paxs = GetStringFromDataReader(reader, "PAXS");
billRegister.VisaCountry = GetStringFromDataReader(reader, "COUNTRY_NAME");
lst.Add(billRegister);
}
}
return(lst);
}
public bool UpdateBillEntries(BillRegister argBReg, List<BillAbstract> argBAbs, List<BillEntry> argBEntry, List<BillLabourTypeTransBO> argBLbrType, List<BillMaterialRecovery> argBMatRec, List<BillIOWTransUpdate> argBIOWTrans, List<BIMeasurementBO> argBIMsrment, List<BillIOWMeasurementBOUpdate> argBIOWMsrment, List<BillFormateTDSNewBO> argBFTDS, DataTable argBLbrTrNew, string argBillType, int argCCId, int argContId, int argBdetId, int argrevId, List<BillWBSTrans> argWBSTrans, List<BillWBSMeasurementTransBO> argWBSMsrTrans, List<DPERegTrans> argDPE, List<BillAdvanceBO> argAdvance, DataTable dtQtrans, List<BillTurnKeyEntry> argTurnKeyTrans, List<BillLBRResourceTransBO> argLbrRes, DataTable argWOTransQual, List<BillMSteelSelection> oSteelSel, List<BillLSWBSTrans> argLSWBS, bool argInternal, List<BillLabourAdvance> argBLbrAdv, List<BillServiceTaxNewBO> argBFST, DataTable dtLbrAdj, string argDBName, List<BillLSVTrans> argBillLSV, List<BillLSTransBO> argBillLSTrans, DataTable argResQualTran, DataTable argResRateQ, List<LabourTransBO> argItemLbr, List<LabourTransBO> argLabourItem, List<LabourTransBO> argLabourSum, List<BillRetentionBO> argRetention, DataTable argMatAdvance, List<BillVATBO> argVAT)
{
#region Variables
bool bUpdate = false;
int iBillRegId = 0;
int iBillTransId = 0;
int iBillIOWTransId = 0;
int iBillWBSTransId = 0;
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenWPMDB();
SqlTransaction tran = conn.BeginTransaction();
string sRefDate = "";
string sSql = "";
#endregion
try
{
#region Update Register
sRefDate = String.Format("{0:dd-MMM-yyyy}", argBReg.RefDate);
//Insert BillRegister
SqlCommand Command = new SqlCommand("Update_BillRegister", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@BillRegisterId", argBReg.BillRegisterId);
Command.Parameters.AddWithValue("@Edate", argBReg.EDate);
Command.Parameters.AddWithValue("@FDate", argBReg.FrmDate);
Command.Parameters.AddWithValue("@TDate", argBReg.ToDate);
Command.Parameters.AddWithValue("@VNo", argBReg.VNo);
Command.Parameters.AddWithValue("@CCBVNo", argBReg.CCBVNo);
Command.Parameters.AddWithValue("@BillNo", argBReg.BillNo);
Command.Parameters.AddWithValue("@ContractorID", argBReg.ContractorID);
Command.Parameters.AddWithValue("@BillType", argBReg.BillType);
Command.Parameters.AddWithValue("@BillDetID", argBReg.BillDetId);
Command.Parameters.AddWithValue("@BillAmount", argBReg.BillAmount);
Command.Parameters.AddWithValue("@Narration", argBReg.Narration);
Command.Parameters.AddWithValue("@CostCentreId", argBReg.CostCentreId);
Command.Parameters.AddWithValue("@LRoundAmt", argBReg.LRoundAmt);
Command.Parameters.AddWithValue("@LNetAmt", argBReg.LNetAmt);
Command.Parameters.AddWithValue("@IOWAmount", argBReg.IOWAmount);
Command.Parameters.AddWithValue("@LabourAmount", argBReg.LabourAmount);
Command.Parameters.AddWithValue("@BillUpdateType", Convert.ToChar(argBReg.BillUpdateType));
Command.Parameters.AddWithValue("@WORegisterId", argBReg.WORegisterID);
Command.Parameters.AddWithValue("@CreatedUser", argBReg.CreatedUser);
Command.Parameters.AddWithValue("@CreatedDate", argBReg.CreatedDate);
Command.Parameters.AddWithValue("@ModifiedUser", argBReg.ModifiedUser);
Command.Parameters.AddWithValue("@ModifiedDate", argBReg.ModifiedDate);
Command.Parameters.AddWithValue("@CompanyBVNo", argBReg.CompanyBVNo);
Command.ExecuteNonQuery(); Command.Dispose();
Command.Dispose();
iBillRegId = argBReg.BillRegisterId;
int iReady = 0;
if (argBReg.Ready == true) { iReady = 1; }
//Update WorkAccountId in BillRegister
sSql = "Update BillRegister Set WorkAccountId=" + argBReg.WorkAccountId + ",FormatType='" + argBReg.FormatType + "', " +
"CostOption='" + argBReg.CostOption + "',RefDate='" + sRefDate + "',RefNo='" + argBReg.RefNo + "',QualType = " + argBReg.QualType + ",Ready = " + iReady + ",LabourUsedType = '" + argBReg.LabourUsedType + "' Where BillRegisterId = " + iBillRegId;
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose();
Command.Dispose();
//Update AdjustAmt in WORegister,for Advance option WOAdvAmt Validation
//if (argInternal == true)
//{
// if (dtLbrAdj.Rows.Count > 0)
// {
// sSql = "Delete LabourAdvAdjustment Where BillRegisterId=" + iBillRegId + " ";
// Command = new SqlCommand(sSql, conn, tran);
// Command.ExecuteNonQuery(); Command.Dispose();
// foreach (DataRow dr in dtLbrAdj.Rows)
// {
// decimal AdvAmount = Convert.ToDecimal(CommFun.IsNullCheck(dr["CAmount"], CommFun.datatypes.vartypenumeric));
// if (AdvAmount > 0)
// {
// sSql = "Insert Into LabourAdvAdjustment (BillRegisterId,LabourId,Amount,RegId) " +
// "Values (" + iBillRegId + "," + dr["LabourId"] + ",'" + AdvAmount + "'," + dr["RegId"] + ") ";
// Command = new SqlCommand(sSql, conn, tran);
// Command.ExecuteNonQuery(); Command.Dispose();
// }
// }
// }
//}
//}
#endregion
#region Qualifiers
//Delete BillRateQ
sSql = "Delete from BillRateQ where BillRegisterId=" + iBillRegId ;
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose();
Command.Dispose();
//Bill Qualifier
if (dtQtrans != null)
{
for (int q = 0; q < dtQtrans.Rows.Count; q++)
{
sSql = "Insert Into BillRateQ (BillRegisterID,QualifierId,Expression,ExpPer,Add_Less_Flag,SurCharge,EDCess,Amount,ExpValue,ExpPerValue,SurValue,EDValue,HEDCess,HEDValue,NetPer) " +
" Values (" + iBillRegId + "," + Convert.ToInt32(CommFun.IsNullCheck(dtQtrans.Rows[q]["QualifierId"], CommFun.datatypes.vartypenumeric)) + ",'" + dtQtrans.Rows[q]["Expression"] + "', " +
" " + Convert.ToDecimal(CommFun.IsNullCheck(dtQtrans.Rows[q]["ExpPer"], CommFun.datatypes.vartypenumeric)) + ",'" + dtQtrans.Rows[q]["Add_Less_Flag"] + "', " +
" " + Convert.ToDecimal(CommFun.IsNullCheck(dtQtrans.Rows[q]["SurCharge"], CommFun.datatypes.vartypenumeric)) + "," + Convert.ToDecimal(CommFun.IsNullCheck(dtQtrans.Rows[q]["EDCess"], CommFun.datatypes.vartypenumeric)) + ", " +
" " + Convert.ToDecimal(CommFun.IsNullCheck(dtQtrans.Rows[q]["Amount"], CommFun.datatypes.vartypenumeric)) + "," + Convert.ToDecimal(CommFun.IsNullCheck(dtQtrans.Rows[q]["ExpValue"], CommFun.datatypes.vartypenumeric)) + "," +
" " + Convert.ToDecimal(CommFun.IsNullCheck(dtQtrans.Rows[q]["ExpPerValue"], CommFun.datatypes.vartypenumeric)) + "," + Convert.ToDecimal(CommFun.IsNullCheck(dtQtrans.Rows[q]["SurValue"], CommFun.datatypes.vartypenumeric)) + ", " +
" " + Convert.ToDecimal(CommFun.IsNullCheck(dtQtrans.Rows[q]["EDValue"], CommFun.datatypes.vartypenumeric)) + "," + Convert.ToDecimal(CommFun.IsNullCheck(dtQtrans.Rows[q]["HEDPer"], CommFun.datatypes.vartypenumeric)) + ", " +
" " + Convert.ToDecimal(CommFun.IsNullCheck(dtQtrans.Rows[q]["HEDValue"], CommFun.datatypes.vartypenumeric)) + "," + Convert.ToDecimal(CommFun.IsNullCheck(dtQtrans.Rows[q]["NetPer"], CommFun.datatypes.vartypenumeric)) + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose();
Command.Dispose();
}
}
//if (argWOTransQual != null)
//{
// for (int q = 0; q < argWOTransQual.Rows.Count; q++)
// {
// sSql = "Insert Into BillRateQ (BillRegisterID,QualifierId,Expression,ExpPer,Add_Less_Flag,SurCharge,EDCess,Amount,ExpValue,ExpPerValue,SurValue,EDValue,TypeId,HEDCess,HEDValue,NetPer) " +
// " Values (" + iBillRegId + "," + Convert.ToInt32(CommFun.IsNullCheck(argWOTransQual.Rows[q]["QualifierId"], CommFun.datatypes.vartypenumeric)) + ",'" + argWOTransQual.Rows[q]["Expression"] + "', " +
// " " + Convert.ToDecimal(CommFun.IsNullCheck(argWOTransQual.Rows[q]["ExpPer"], CommFun.datatypes.vartypenumeric)) + ",'" + argWOTransQual.Rows[q]["Add_Less_Flag"] + "', " +
// " " + Convert.ToDecimal(CommFun.IsNullCheck(argWOTransQual.Rows[q]["SurCharge"], CommFun.datatypes.vartypenumeric)) + "," + Convert.ToDecimal(CommFun.IsNullCheck(argWOTransQual.Rows[q]["EDCess"], CommFun.datatypes.vartypenumeric)) + ", " +
// " " + Convert.ToDecimal(CommFun.IsNullCheck(argWOTransQual.Rows[q]["Amount"], CommFun.datatypes.vartypenumeric)) + "," + Convert.ToDecimal(CommFun.IsNullCheck(argWOTransQual.Rows[q]["ExpValue"], CommFun.datatypes.vartypenumeric)) + "," +
// " " + Convert.ToDecimal(CommFun.IsNullCheck(argWOTransQual.Rows[q]["ExpPerValue"], CommFun.datatypes.vartypenumeric)) + "," + Convert.ToDecimal(CommFun.IsNullCheck(argWOTransQual.Rows[q]["SurValue"], CommFun.datatypes.vartypenumeric)) + ", " +
// " " + Convert.ToDecimal(CommFun.IsNullCheck(argWOTransQual.Rows[q]["EDValue"], CommFun.datatypes.vartypenumeric)) + "," + Convert.ToInt32(CommFun.IsNullCheck(argWOTransQual.Rows[q]["AbsTypeId"], CommFun.datatypes.vartypenumeric)) + "," +
// " " + Convert.ToDecimal(CommFun.IsNullCheck(argWOTransQual.Rows[q]["HEDPer"], CommFun.datatypes.vartypenumeric)) + "," + Convert.ToDecimal(CommFun.IsNullCheck(argWOTransQual.Rows[q]["HEDValue"], CommFun.datatypes.vartypenumeric)) + ", " +
// " " + Convert.ToDecimal(CommFun.IsNullCheck(argWOTransQual.Rows[q]["NetPer"], CommFun.datatypes.vartypenumeric)) + ")";
// Command = new SqlCommand(sSql, conn, tran);
// Command.ExecuteNonQuery(); Command.Dispose();
// Command.Dispose();
// }
//}
#endregion
#region Delete Tables to Insert New
//Delete BillLSTrans Table....
sSql = "Update LabourStrengthMaster Set ConvertBill =0 Where LabourStrengthID in (Select LabourStrengthID from BillLSTrans where BillRegisterID = " + iBillRegId + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
sSql = "Delete from BillLSTrans where BillRegisterID = " + iBillRegId;
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
sSql = "Update DPERegister Set ConvertBill=0 Where DPERegisterId in (Select DPERegisterId from BillDPETrans Where BillRegisterId = " + iBillRegId + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
//Delete BillLSTrans Table....
sSql = "Delete from BillDPETrans where BillRegisterID = " + iBillRegId;
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
//Delete BillLSWBSTrans Table....
sSql = "Delete from BillLSWBSTrans where BillRegisterId = " + iBillRegId;
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
sSql = "Delete from BillMaterialAdvanceTrans Where BillRegisterId = " + iBillRegId;
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
sSql = "Delete from BillVATTrans Where BillRegisterId = " + iBillRegId;
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
//Delete BillTypeTrans
Command = new SqlCommand("Delete from BillTypeTrans where BillRegisterId=" + iBillRegId + "", conn, tran);
Command.ExecuteNonQuery(); Command.Dispose();
Command.Dispose();
//Delete BillLabourTrans
Command = new SqlCommand("Delete from BillLabourTrans Where BillRegisterId=" + iBillRegId + "", conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
//Delete BillItemLabourTrans
Command = new SqlCommand("Delete from BillItemLabourTrans Where BillTransId in (Select BillTransID from BillTrans Where BillRegisterId = " + iBillRegId + ")", conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
Command = new SqlCommand("Delete from BillWBSLabourTrans Where BillTransId in (Select BillTransID from BillTrans Where BillRegisterId = " + iBillRegId + ")", conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
//Delete BillIOWLabourTrans
Command = new SqlCommand("DELETE FROM BillIOWLabourTrans WHERE BillTransId IN (SELECT BillTransID FROM BillTrans WHERE BillRegisterId = " + iBillRegId + ")", conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
//Delete BillTurnKeyTrans
sSql="Delete from BillTurnKeyTrans where BillRegisterId=" + iBillRegId ;
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
//Delete BillTurnKeyTrans
sSql = "Delete from BillServiceTaxTrans where BillRegId=" + iBillRegId;
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
//Delete BillMLabourTrans
sSql = "Delete from BillMLabourTrans where BillRegisterId=" + iBillRegId;
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
//Delete BillFormatTrans
Command = new SqlCommand("Delete from BillFormatTrans where BillRegisterId=" + iBillRegId + "", conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
//Delete BillIOWTrans
Command = new SqlCommand("DELETE FROM BillIOWTrans WHERE BillTransId IN (SELECT BillTransID FROM BillTrans WHERE BillRegisterId = " + iBillRegId + ")", conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
//Delete LabourBillTrans
Command = new SqlCommand("Delete from LabourBillTrans where BillRegisterId=" + iBillRegId + "", conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
//Delete TDSBillFormatTrans
Command = new SqlCommand("Delete from TDSBillFormatTrans where BillRegisterId=" + iBillRegId + "", conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
//Delete BillMaterialRecovery
Command = new SqlCommand("Delete from BillMaterialRecovery where BillRegId=" + iBillRegId + "", conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
//Delete BillLabourType
Command = new SqlCommand("Delete from BillLabourType where BillRegisterId=" + iBillRegId + "", conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
//BillLabourAdvance
Command = new SqlCommand("Delete from BillLabourAdvance where BillRegisterId=" + iBillRegId + "", conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
//Delete BillWBSTrans
Command = new SqlCommand("DELETE FROM BillWBSTrans WHERE BillTransId IN (SELECT BillTransID FROM BillTrans WHERE BillRegisterId = " + iBillRegId + " And BillType='I')", conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
//Delete BillWBSMeasurement
Command = new SqlCommand("DELETE FROM BillMeasurement WHERE WBSWise='Y' And BillTransId IN (SELECT BillTransID FROM BillTrans WHERE BillRegisterId = " + iBillRegId + ")", conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
//Delete BillMeasurement
Command = new SqlCommand("DELETE FROM BillMeasurement WHERE WBSWise='N' And BillTransId IN (SELECT BillTransID FROM BillTrans WHERE BillRegisterId = " + iBillRegId + ")", conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
//Delete BillLbrResourceTrans
sSql = "DELETE FROM BillLbrResourceTrans WHERE BillTransId IN (SELECT BillTransID FROM BillTrans WHERE BillRegisterId = " + iBillRegId + " And BillType='L')";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
sSql = "Delete from BillResRate_Q Where BillTransID in (Select BillTransId from BillTrans Where BillRegisterId = " + iBillRegId + ")";
Command = new SqlCommand(sSql, conn, tran); Command.ExecuteNonQuery(); Command.Dispose();
sSql = "Delete from BillResourceTrans Where BillTransID in (Select BillTransId from BillTrans Where BillRegisterId = " + iBillRegId + ")";
Command = new SqlCommand(sSql, conn, tran); Command.ExecuteNonQuery(); Command.Dispose();
//Delete BillTrans
Command = new SqlCommand("Delete from BillTrans where BillRegisterId=" + iBillRegId + "", conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
sSql = "Delete from BillLabourTypeWBSLSTrans Where BillLSWBSTransId in (Select BillLSWBSTransId from BillLabourTypeWBSTrans Where BillLabourTypeTransId in(Select BillLabourTypeTransId from BillLabourTypeTrans Where BillRegisterId = " + iBillRegId + "))";
Command = new SqlCommand(sSql, conn, tran); Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
sSql = "Delete from BillLabourTypeWBSLSTrans Where BillLabourTypeTransId in(Select BillLabourTypeTransId from BillLabourTypeTrans Where BillRegisterId = " + iBillRegId + ")";
Command = new SqlCommand(sSql, conn, tran); Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
sSql = "Delete from BillLabourTypeWBSTrans Where BillLabourTypeTransId in(Select BillLabourTypeTransId from BillLabourTypeTrans Where BillRegisterId = " + iBillRegId + ")";
Command = new SqlCommand(sSql, conn, tran); Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
sSql = "Delete from BillLabourTypeTrans Where BillRegisterId = " + iBillRegId;
Command = new SqlCommand(sSql, conn, tran); Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
sSql = "Delete from BillRetentionAdjustment Where AdjBillRegisterId = " + iBillRegId;
Command = new SqlCommand(sSql, conn, tran); Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
//if (CommFun.sCompDBName!= null)
//{
// sSql = "DELETE FROM [" + CommFun.sCompDBName + "].dbo.EntryTrans WHERE RefId=" + iBillRegId + " AND RefType='WB'";
// Command = new SqlCommand(sSql, conn, tran);
// Command.ExecuteNonQuery(); Command.Dispose();
//}
#endregion
//if (argBReg.FormatType == "A")
// {
// sSql = "Update WOPaymentTermsNew Set AdjustAmount=" + argBReg.BillAmount + " where TermsId=" + argTermsId + " and WORegisterId = " + argBReg.WORegisterID;
// Command = new SqlCommand(sSql, conn, tran);
// Command.ExecuteNonQuery(); Command.Dispose();
// }
foreach (DataRow dRow in argMatAdvance.Rows)
{
sSql = "Insert into BillMaterialAdvanceTrans(BillRegisterId,TypeId,ResourceId,Qty,Rate,Amount,Per,AdvanceAmt) " +
"Values(" + iBillRegId + "," + Convert.ToInt32(dRow["TypeId"]) + ", " + Convert.ToInt32(dRow["ResourceId"]) + "," + Convert.ToDecimal(dRow["Qty"]) + "," + Convert.ToDecimal(dRow["Rate"]) + "," + Convert.ToDecimal(dRow["Amount"]) + "," + Convert.ToDecimal(dRow["Per"]) + ", " + Convert.ToDecimal(dRow["AdvanceAmt"]) + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose();
}
foreach (LabourTransBO objLbr in argLabourSum)
{
sSql = "Insert into BillLabourTrans(BillRegisterId,LabourTypeId,Qty,Rate,Amount,MinRate,AppRate) " +
"Values(" + iBillRegId + "," + objLbr.LabourTypeID + "," + objLbr.Qty + "," + objLbr.Rate + "," + objLbr.Amount + "," + objLbr.MinRate + ", " + objLbr.AppRate + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose();
}
if (argBReg.FormatType == "R")
{
foreach (BillRetentionBO obj in argRetention)
{
if (Convert.ToDecimal(CommFun.IsNullCheck(obj.CurAmount, CommFun.datatypes.vartypenumeric)) > 0)
{
sSql = "Insert into BillRetentionAdjustment(BillRegisterId,AdjBillRegisterId,Amount) " +
"Values(" + obj.BillRegisterId + "," + iBillRegId + "," + obj.CurAmount + ")";
Command = new SqlCommand(sSql, conn, tran); Command.ExecuteNonQuery(); Command.Dispose();
}
}
}
//insert AdjustAdvance Amount
if (argBReg.FormatType == "B")
{
sSql = "Delete AdvAdjustment Where BillRegisterId=" + iBillRegId + " ";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose();
Command.Dispose();
foreach (BillAdvanceBO obj in argAdvance)
{
decimal AdvAmount = Convert.ToDecimal(CommFun.IsNullCheck(obj.CAmount, CommFun.datatypes.vartypenumeric));
//decimal HAmount = Convert.ToDecimal(CommFun.IsNullCheck(dr["DedAmount"], CommFun.datatypes.vartypenumeric));
if (AdvAmount > 0)
{
sSql = "Insert Into AdvAdjustment (BillRegisterId,WORegisterId,Amount,RegId,FormatTypeId) " +
"Values (" + iBillRegId + "," + obj.WORegisterId + "," + obj.CAmount + "," + obj.RegId + ", " + obj.FormatTypeId + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose();
Command.Dispose();
//sSql = "Update WOPaymentTermsNew Set AdjustAmount=AdjustAmount+" + AdvAmount + "-" + HAmount + " " +
// "Where WORegisterId=" + Convert.ToInt32(CommFun.IsNullCheck(dr["WORegisterId"].ToString(), CommFun.datatypes.vartypenumeric));
//Command = new SqlCommand(sSql, conn, tran);
//Command.ExecuteNonQuery(); Command.Dispose();
//Command.Dispose();
}
}
}
#region Insert BillLabours
////Insert BillTypeTrans
//for (int p = 0; p < argBillType.Count; p++)
//{
// Command = new SqlCommand("Insert Into BillTypeTrans (BillRegisterId,BillType) values (" + iBillRegId + ",'" + argBillType[p].ToString() + "')", conn, tran);
// Command.ExecuteNonQuery(); Command.Dispose();
//}
//List<BillLabourTrans> argBLbrTrNew-BillLabourTrans
//for (int u = 0; u <argLbrTrnNew.Rows.Count; u++)
//{
// Command = new SqlCommand("Insert_BillLabourTrans", conn, tran);
// Command.CommandType = CommandType.StoredProcedure;
// Command.Parameters.Clear();
// Command.Parameters.AddWithValue("@BillRegisterId", iBillRegId);
// Command.Parameters.AddWithValue("@LabourTypeId", argLbrTrnNew.Rows[u]["TypeId"].ToString());
// Command.Parameters.AddWithValue("@Qty", argLbrTrnNew.Rows[u]["Qty"].ToString());
// Command.Parameters.AddWithValue("@Rate", argLbrTrnNew.Rows[u]["Rate"].ToString());
// Command.Parameters.AddWithValue("@Amount", argLbrTrnNew.Rows[u]["Amount"].ToString());
// Command.Parameters.AddWithValue("@MinRate", argLbrTrnNew.Rows[u]["MinWage"].ToString());
// Command.Parameters.AddWithValue("@AppRate", argLbrTrnNew.Rows[u]["AppRate"].ToString());
// Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
//}
//Insert LabourBillTrans
//List<LabourBillTrans> argLbrTrnNew -Wages Format
for (int u = 0; u <argBLbrTrNew.Rows.Count; u++)
{
Command = new SqlCommand("Insert_LabourBillTrans", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@BillRegisterId", iBillRegId);
Command.Parameters.AddWithValue("@TypeId", argBLbrTrNew.Rows[u]["TypeId"].ToString());
Command.Parameters.AddWithValue("@Amount", argBLbrTrNew.Rows[u]["Amount"].ToString());
Command.Parameters.AddWithValue("@Formula", argBLbrTrNew.Rows[u]["Formula"].ToString());
Command.ExecuteNonQuery(); Command.Dispose();
}
if (argDPE != null)
{
List<DPERegTrans> oDpe = argDPE;
if (oDpe.Count > 0)
{
foreach (DPERegTrans objDPE in oDpe)
{
//Insert BillDPETrans
sSql = "Insert into BillDPETrans(BillRegisterID,DPERegisterID,TypeId) Values(" + iBillRegId + "," + objDPE.DPERegId + "," + objDPE.TypeId + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose();
//Update DPERegister Set ConvertBill=1 Where DPERegisterId ="++""
Command = new SqlCommand("Update DPERegister Set ConvertBill=1 Where DPERegisterId =" + objDPE.DPERegId + "", conn, tran);
Command.ExecuteNonQuery(); Command.Dispose();
//CommFun.ActualCompUpdate(objDPE.DPERegId, argCCId, "D", false, argrevId, argBReg.ContractorID, argDBName, conn, tran);
}
//InsertRateQ()
}
}
#endregion
#region Bill FormatTrans
foreach (BillLSTransBO obj in argBillLSTrans)
{
sSql = "Insert into BillLSTrans(LabourStrengthID,BillRegisterID,TypeId) " +
"Values(" + obj.LabourStrengthID + "," + iBillRegId + ", " + obj.TypeId + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
sSql = "Update LabourStrengthMaster Set ConvertBill=1 Where LabourStrengthID = " + obj.LabourStrengthID;
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
}
//Insert BillFormateTrans List<BillAbstract> argBAbs
foreach (BillAbstract objAbs in argBAbs)
{
Command = new SqlCommand("Insert_BillFormatTrans", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@BillRegisterId", iBillRegId);
Command.Parameters.AddWithValue("@TypeId", objAbs.TypeId);
Command.Parameters.AddWithValue("@Amount", objAbs.Amount);
Command.Parameters.AddWithValue("@AccountId", objAbs.AccountId);
Command.Parameters.AddWithValue("@Formula", objAbs.Formula);
Command.Parameters.AddWithValue("@Add_Less_Flag", objAbs.Sign);
Command.Parameters.AddWithValue("@QualMId", objAbs.QualMId);
Command.ExecuteNonQuery(); Command.Dispose();
//Qualifier TypeId in BillFormatTrans
sSql = "Update BillFormatTrans Set QTypeId=" + objAbs.QTypeId + ",FormatType='" + objAbs.BFormatType + "',QualifierId = " + objAbs.QualifierId + ",TaxPercentage = " + objAbs.TaxPer + " Where BillRegisterId=" + iBillRegId + " and TypeId=" + objAbs.TypeId;
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose();
Command.Dispose();
if (objAbs.FormatTypeId == 8)
{
List<BillVATBO> oVATsel = argVAT.FindAll(delegate(BillVATBO del) { return (del.TypeId == objAbs.TypeId); });
if (oVATsel.Count > 0)
{
foreach (BillVATBO objVAT in oVATsel)
{
sSql = "Insert into BillVATTrans(BillRegisterId,TypeId,Expression,ExpValue,VATPer,NetValue) " +
"Values(" + iBillRegId + "," + objAbs.TypeId + ",'" + objVAT.Expression + "'," + objVAT.ExpValue + ", " + objVAT.Per + "," + objVAT.NetTax + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose();
}
}
}
if (objAbs.FormatTypeId == 1)
{
//Insert Bill TurnKey Trans argTurnKeyTrans
List<BillTurnKeyEntry> oBTsel = argTurnKeyTrans.FindAll(delegate(BillTurnKeyEntry del) { return (del.BAbsTypeId == objAbs.TypeId); });
if (oBTsel.Count > 0)
{
foreach (BillTurnKeyEntry objBTKTrns in oBTsel)
{
//if (objBTKTrns.WORegId == 0)
//{
// sSql = "Insert INTO BillTurnKeyTrans(BillRegisterId,TypeId,WORegisterId,DPERegisterId,AnalysisId,CumPercentage,CurPercentage,CurAmount,PrevPercentage,CumQty,WOAllotted) " +
// "Values(" + iBillRegId + "," + objBTKTrns.BAbsTypeId + ",0,"+objBTKTrns.DPERegId+"," + objBTKTrns.AnalysisId + "," + objBTKTrns.CumulativeQty + ", " +
// " " + objBTKTrns.CurrentQty + "," + objBTKTrns.CurrentAmount + "," + objBTKTrns.PreviousQty + "," + objBTKTrns.CurrentQty + "," + objBTKTrns.WOQty + ")";
//}
//else
//{
// sSql = "Insert INTO BillTurnKeyTrans(BillRegisterId,TypeId,WORegisterId,DPERegisterId,AnalysisId,CumPercentage,CurPercentage,CurAmount,CumAmount,WOAllotted,PrevPercentage,CumQty) " +
// "Values(" + iBillRegId + "," + objBTKTrns.BAbsTypeId + "," + objBTKTrns.WORegId + ","+objBTKTrns.DPERegId+"," + objBTKTrns.AnalysisId + "," + objBTKTrns.CumulativeQty + ", " +
// " " + objBTKTrns.CurrentQty + "," + objBTKTrns.CurrentAmount + "," + objBTKTrns.CumAmount + "," + objBTKTrns.WOQty + "," + objBTKTrns.PreviousQty + "," + objBTKTrns.CurrentQty + ")";
//}
sSql = "Insert INTO BillTurnKeyTrans(BillRegisterId,TypeId,WORegisterId,DPERegisterId,AnalysisId,CumPercentage,CurPercentage,CurAmount,CumAmount,WOAllotted,PrevPercentage,CumQty) " +
"Values(" + iBillRegId + "," + objBTKTrns.BAbsTypeId + "," + objBTKTrns.WORegId + "," + objBTKTrns.DPERegId + "," + objBTKTrns.AnalysisId + "," + objBTKTrns.CumulativeQty + ", " +
" " + objBTKTrns.CurrentQty + "," + objBTKTrns.CurrentAmount + "," + objBTKTrns.CumAmount + "," + objBTKTrns.WOQty + "," + objBTKTrns.PreviousQty + "," + objBTKTrns.CurrentQty + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose();
Command.Dispose();
}
//Bill TurnKey DPE TRans
//if (argDPE != null)
//{
// List<DPERegTrans> oDpe = argDPE.FindAll(delegate(DPERegTrans del) { return (del.TypeId == objAbs.TypeId); });
// if (oDpe.Count > 0)
// {
// foreach (DPERegTrans objDPE in oDpe)
// {
// //Insert BillDPETrans
// Command = new SqlCommand("Insert_BillDPETrans", conn, tran);
// Command.CommandType = CommandType.StoredProcedure;
// Command.Parameters.Clear();
// Command.Parameters.AddWithValue("@BillRegisterID", iBillRegId);
// Command.Parameters.AddWithValue("@DPERegisterID", objDPE.DPERegId);
// Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
// //Update DPERegister Set ConvertBill=1 Where DPERegisterId ="++""
// Command = new SqlCommand("Update DPERegister Set ConvertBill=1 Where DPERegisterId =" + objDPE.DPERegId + "", conn, tran);
// Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
// CommFun.ActualCompUpdate(objDPE.DPERegId, argCCId, "D", false, argrevId, argBReg.ContractorID,argDBName, conn, tran);
// }
// //InsertRateQ()
// }
//}
}
//Insert BillTrans
List<BillEntry> osel = argBEntry.FindAll(delegate(BillEntry del)
{
return (del.BAbsTypeId == objAbs.TypeId);
});
if (osel.Count > 0)
{
foreach (BillEntry objBTrns in osel)
{
Command = new SqlCommand("Insert_BillTrans", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@BillTransID", objBTrns.BillTransId);
Command.Parameters["@BillTransID"].Direction = ParameterDirection.Output;
Command.Parameters.AddWithValue("@BillRegisterID", iBillRegId);
Command.Parameters.AddWithValue("@TypeId", objBTrns.BAbsTypeId);
Command.Parameters.AddWithValue("@IOW_ID", objBTrns.IOW_ID);
Command.Parameters.AddWithValue("@Comp_ID", objBTrns.ResourceId);
Command.Parameters.AddWithValue("@UnitID", objBTrns.Unit);
if (objBTrns.Sign.ToString() == "-")
{
Command.Parameters.AddWithValue("@Qty", objBTrns.Qty * (-1));
Command.Parameters.AddWithValue("@Amount", objBTrns.Amount*(-1));
}
else
{
Command.Parameters.AddWithValue("@Qty", objBTrns.Qty);
Command.Parameters.AddWithValue("@Amount", objBTrns.Amount);
}
Command.Parameters.AddWithValue("@Rate", objBTrns.Rate);
Command.Parameters.AddWithValue("@AnalysisHeadId", objBTrns.AnalysisHeadId);
Command.Parameters.AddWithValue("@BillType", argBReg.BillType);
Command.Parameters.AddWithValue("@MType", objBTrns.MType);
Command.Parameters.AddWithValue("@FullRate", objBTrns.FullRate);
Command.Parameters.AddWithValue("@PerPart", objBTrns.PerPart);
Command.Parameters.AddWithValue("@PartRate", objBTrns.PartRate);
Command.Parameters.AddWithValue("@MUnitID", objBTrns.MUOM_ID);
Command.Parameters.AddWithValue("@UFactor", objBTrns.UFactor);
Command.Parameters.AddWithValue("@DetSpec", objBTrns.DetSpec);
Command.Parameters.AddWithValue("@WORegisterID", objBTrns.WORegisterID);
Command.Parameters.AddWithValue("@RateType", objBTrns.RateType);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
iBillTransId = (int)Command.Parameters["@BillTransID"].Value;
//insert BillItem Labour Trans
int rsrceId2 = 0;
if (objBTrns.ResourceId == 0) { rsrceId2 = objBTrns.IOW_ID; }
else if (objBTrns.IOW_ID == 0) { rsrceId2 = objBTrns.ResourceId; }
//List<BillItemLbrStrnthTransBO> oSItmLbrTrns = argBLbrItem.FindAll(delegate(BillItemLbrStrnthTransBO del) { return del.BAbsTypeId == objBTrns.BAbsTypeId && del.ItemTransId == rsrceId2; });
//if (oSItmLbrTrns.Count > 0)
//{
// foreach (BillItemLbrStrnthTransBO objItem in oSItmLbrTrns)
// {
// Command = new SqlCommand("Insert_BillItemLabourTrans", conn, tran);
// Command.CommandType = CommandType.StoredProcedure;
// Command.Parameters.Clear();
// Command.Parameters.AddWithValue("@BillTransID", iBillTransId);
// Command.Parameters.AddWithValue("@LabourTypeID", objItem.LbrTypeId);
// Command.Parameters.AddWithValue("@Qty", objItem.Qtty);
// Command.Parameters.AddWithValue("@Rate", objItem.Rate);
// Command.Parameters.AddWithValue("@Amount", objItem.Amount);
// Command.Parameters.AddWithValue("@AppRate", objItem.AppRate);
// Command.Parameters.AddWithValue("@MinRate", objItem.MinRate);
// Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
// }
//}
List<LabourTransBO> oChk = argLabourItem.FindAll(delegate(LabourTransBO del) { return del.AbsTypeId == objAbs.TypeId && del.RowId == objBTrns.RowId; });
foreach (LabourTransBO objLbr in oChk)
{
sSql = "Insert into BillItemLabourTrans(BillTransId,LabourTypeId,Qty,Rate,Amount,MinRate,AppRate) " +
"Values(" + iBillTransId + "," + objLbr.LabourTypeID + "," + objLbr.Qty + "," + objLbr.Rate + "," + objLbr.Amount + "," + objLbr.MinRate + ", " + objLbr.AppRate + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose();
Command.Dispose();
}
if (objBTrns.Type == "I")
{
if (argBReg.QualType == 1)
{
DataView dv;
DataTable dtR = new DataTable();
//IOW Resource Trans
if (argResQualTran != null)
{
if (argResQualTran.Rows.Count > 0)
{
dv = new DataView(argResQualTran);
dv.RowFilter = "WOTransRowId=" + objBTrns.RowId;
dtR = dv.ToTable();
}
else
{ dtR.Rows.Clear(); }
}
int iResSel = 0;
for (int l = 0; l < dtR.Rows.Count; l++)
{
if (Convert.ToBoolean(dtR.Rows[l]["Select"]) == true) { iResSel = 1; }
sSql = "Insert Into BillResourceTrans (BillTransId,TypeId,ResourceId,QRate,CoffQty,IOWQty,Amount,NetAmount,IOWId,BillQTypeId,ResSel) " +
"Values(" + iBillTransId + "," + Convert.ToInt32(dtR.Rows[l]["TypeId"]) + "," + Convert.ToInt32(dtR.Rows[l]["ResourceId"]) + ", " +
"" + Convert.ToDecimal(dtR.Rows[l]["QRate"]) + "," + Convert.ToDecimal(dtR.Rows[l]["CoffQty"]) + "," + Convert.ToDecimal(dtR.Rows[l]["IOWQty"]) + "," + Convert.ToDecimal(dtR.Rows[l]["WOAmount"]) + "," + Convert.ToDecimal(dtR.Rows[l]["NetAmount"]) + "," + Convert.ToInt32(dtR.Rows[l]["IOWId"]) + "," + Convert.ToInt32(dtR.Rows[l]["WOQTypeId"]) + "," + iResSel + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
}
//Insert QTypeTrans
if (argResRateQ != null)
{
if (argResRateQ.Rows.Count > 0)
{
dv = new DataView(argResRateQ);
dv.RowFilter = "TransRowId=" + objBTrns.RowId;
dtR = dv.ToTable();
}
else
{
dtR.Rows.Clear();
}
}
for (int q = 0; q < dtR.Rows.Count; q++)
{
sSql = "Insert Into BillResRate_Q (BillRegisterID,QualifierId,Expression,ExpPer,Add_Less_Flag,SurCharge,EDCess,Amount,ExpValue,ExpPerValue," +
"SurValue,EDValue,HEDCess,HEDValue,NetPer,QType,BillTransID,ResourceID,TaxablePer,TaxableValue) " +
" Values (" + iBillRegId + "," + Convert.ToInt32(dtR.Rows[q]["QualifierId"]) + ",'" + dtR.Rows[q]["Expression"].ToString() + "', " +
" " + Convert.ToDecimal(dtR.Rows[q]["ExpPer"]) + ",'" + dtR.Rows[q]["Add_Less_Flag"].ToString() + "', " +
" " + Convert.ToDecimal(dtR.Rows[q]["SurCharge"]) + "," + Convert.ToDecimal(dtR.Rows[q]["EDCess"]) + ", " +
" " + Convert.ToDecimal(dtR.Rows[q]["Amount"]) + "," + Convert.ToDecimal(dtR.Rows[q]["ExpValue"]) + "," +
" " + Convert.ToDecimal(dtR.Rows[q]["ExpPerValue"]) + "," + Convert.ToDecimal(dtR.Rows[q]["SurValue"]) + ", " +
" " + Convert.ToDecimal(dtR.Rows[q]["EDValue"]) + "," + Convert.ToDecimal(dtR.Rows[q]["HEDPer"]) + "," +
" " + Convert.ToDecimal(dtR.Rows[q]["HEDValue"]) + "," + Convert.ToDecimal(dtR.Rows[q]["NetPer"]) + "," +
" '" + (dtR.Rows[q]["QType"]) + "'," + iBillTransId + ", " +
" " + Convert.ToInt32(dtR.Rows[q]["ResourceId"]) + "," +
" " + Convert.ToDecimal(dtR.Rows[q]["TaxablePer"]) + "," + Convert.ToDecimal(dtR.Rows[q]["TaxableValue"]) + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
}
}
//if (argBReg.BillType == "I")
//{
// if (argSplit != "Y")
// {
// }
//}
//WBSTrans
List<BillWBSTrans> Chk = argWBSTrans.FindAll(delegate(BillWBSTrans del) { return del.TypeId == objAbs.TypeId && del.RowId == objBTrns.RowId; });
if (Chk.Count > 0)
{
foreach (BillWBSTrans objWBS in Chk)
{
if (objWBS.Qty != 0)
{
decimal dWBSQty = objWBS.Qty;
decimal dWBSAmt = objWBS.Amount;
if (objBTrns.Sign.ToString() == "-") { dWBSQty = dWBSQty * (-1); dWBSAmt = dWBSAmt * (-1);}
sSql = "INSERT INTO BillWBSTrans (BillTransId,AnalysisId,Qty,MType,Rate,Amount) " +
"VALUES (" + iBillTransId + "," + objWBS.AnalysisHeadId + "," + dWBSQty + "," + objWBS.MType + "," + objWBS.Rate + "," + dWBSAmt + ")Select SCOPE_IDENTITY();";
Command = new SqlCommand(sSql, conn, tran);
iBillWBSTransId = Convert.ToInt32(Command.ExecuteScalar());
Command.Dispose();
List<LabourTransBO> lChk = argItemLbr.FindAll(delegate(LabourTransBO del) { return del.AbsTypeId == objAbs.TypeId && del.RowId == objBTrns.RowId && del.AnalysisId == objWBS.AnalysisHeadId; });
foreach (LabourTransBO objLbr in lChk)
{
sSql = "Insert into BillWBSLabourTrans(BillWBSTransId,BillTransId,LabourTypeId,Qty,Rate,Amount,MinRate,AppRate,AnalysisId) " +
"Values(" + iBillWBSTransId + "," + iBillTransId + "," + objLbr.LabourTypeID + "," + objLbr.Qty + "," + objLbr.Rate + "," + objLbr.Amount + "," + objLbr.MinRate + ", " + objLbr.AppRate + "," + objWBS.AnalysisHeadId + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose();
Command.Dispose();
}
//WBSRow LabourTrans
//List<BillIOWLbrTrans> oIOWLbr = argBIOWLbrTrans.FindAll(delegate(BillIOWLbrTrans del) { return ((del.IOWRowId == objWBS.WBSRowId) && (del.BEntryRowId == objBTrns.RowId)); });
//List<BillIOWLbrTrans> oIOWLbr = argBIOWLbrTrans.FindAll(delegate(BillIOWLbrTrans del) { return ((del.DPEItemTransId == rsrceId2) && (del.AnalysisId == objWBS.AnalysisHeadId)); });
//if (oIOWLbr.Count > 0)
//{
// foreach (BillIOWLbrTrans objIOWLbr in oIOWLbr)
// {
// Command = new SqlCommand("Insert_BillIOWLabourTrans", conn, tran);
// Command.CommandType = CommandType.StoredProcedure;
// Command.Parameters.Clear();
// Command.Parameters.AddWithValue("@BillIOWTransID", iBillIOWTransId);
// Command.Parameters.AddWithValue("@BillTransId", iBillTransId);
// Command.Parameters.AddWithValue("@LabourTypeId", objIOWLbr.LbrTypeId);
// Command.Parameters.AddWithValue("@Qty", objIOWLbr.Qtty);
// Command.Parameters.AddWithValue("@Rate", objIOWLbr.Rate);
// Command.Parameters.AddWithValue("@Amount", objIOWLbr.Amount);
// Command.Parameters.AddWithValue("@AppRate", objIOWLbr.AppRate);
// Command.Parameters.AddWithValue("@MinRate", objIOWLbr.MinRate);
// Command.Parameters.AddWithValue("@AnalysisId", objIOWLbr.AnalId);
// Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
// }
//}
// WBSMeasurementTrans
if (objWBS.MType == 0 || objWBS.MType == 2)
{
List<BillWBSMeasurementTransBO> wbsMsr = argWBSMsrTrans.FindAll(
delegate(BillWBSMeasurementTransBO del)
{
return ((del.TypeId == objAbs.TypeId) && (del.TransRowId == objBTrns.RowId) && (del.AHeadId == objWBS.AnalysisHeadId));
});
if (wbsMsr.Count > 0)
{
foreach (BillWBSMeasurementTransBO objIMsr in wbsMsr)
{
Command = new SqlCommand("Insert_BillWBSMeasurment", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@BillTransId", iBillTransId);
Command.Parameters.AddWithValue("@BillIOWTransId", objIMsr.ItemTransId);
Command.Parameters.AddWithValue("@IOWId", objIMsr.AHeadId);
Command.Parameters.AddWithValue("@RowId", objIMsr.RowId);
Command.Parameters.AddWithValue("@SlNo", objIMsr.SlNo);
Command.Parameters.AddWithValue("@Description", objIMsr.Description);
Command.Parameters.AddWithValue("@No1", objIMsr.No1);
Command.Parameters.AddWithValue("@No2", objIMsr.No2);
Command.Parameters.AddWithValue("@Length", objIMsr.Length);
Command.Parameters.AddWithValue("@Breadth", objIMsr.Breadth);
Command.Parameters.AddWithValue("@Depth", objIMsr.Depth);
Command.Parameters.AddWithValue("@Qty", objIMsr.Qty);
Command.Parameters.AddWithValue("@Sign", objIMsr.Sign);
Command.Parameters.AddWithValue("@MBookNO", objIMsr.MBookNO);
Command.Parameters.AddWithValue("@PageNo", objIMsr.PageNo);
Command.Parameters.AddWithValue("@LevelNo", objIMsr.LevelNo);
Command.Parameters.AddWithValue("@ParentID", objIMsr.ParentID);
Command.Parameters.AddWithValue("@SteelId", objIMsr.SteelId);
Command.Parameters.AddWithValue("@Factor", objIMsr.Factor);
if (CommFun.IsNullCheck(objIMsr.FNo1, CommFun.datatypes.vartypestring) != null)
Command.Parameters.AddWithValue("@FNo1", CommFun.IsNullCheck(objIMsr.FNo1, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@FNo1", "");
if (CommFun.IsNullCheck(objIMsr.FNo2, CommFun.datatypes.vartypestring) != null)
Command.Parameters.AddWithValue("@FNo2", CommFun.IsNullCheck(objIMsr.FNo2, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@FNo2", "");
if (Convert.ToDecimal(CommFun.IsNullCheck(objIMsr.FLength, CommFun.datatypes.vartypenumeric)) != 0)
Command.Parameters.AddWithValue("@FLength", CommFun.IsNullCheck(objIMsr.FLength, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@FLength", 0);
if (Convert.ToDecimal(CommFun.IsNullCheck(objIMsr.FBreadth, CommFun.datatypes.vartypenumeric)) != 0)
Command.Parameters.AddWithValue("@FBreadth", CommFun.IsNullCheck(objIMsr.FBreadth, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@FBreadth", 0);
if (Convert.ToDecimal(CommFun.IsNullCheck(objIMsr.FDepth, CommFun.datatypes.vartypenumeric)) != 0)
Command.Parameters.AddWithValue("@FDepth", CommFun.IsNullCheck(objIMsr.FDepth, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@FDepth", 0);
Command.Parameters.AddWithValue("@BillType", CommFun.IsNullCheck(objBTrns.Type.ToString(), CommFun.datatypes.vartypestring));
if (CommFun.IsNullCheck(objIMsr.SubFormula, CommFun.datatypes.vartypestring) != null)
Command.Parameters.AddWithValue("@SubFormula", CommFun.IsNullCheck(objIMsr.SubFormula, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@SubFormula", "");
if (CommFun.IsNullCheck(objIMsr.WBSWise, CommFun.datatypes.vartypestring) != null)
Command.Parameters.AddWithValue("@WBSWise", CommFun.IsNullCheck(objIMsr.WBSWise, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@WBSWise", "");
//Command.Parameters.AddWithValue("@FNo1", objIMsr.FNo1);
//Command.Parameters.AddWithValue("@FNo2", objIMsr.FNo2);
//Command.Parameters.AddWithValue("@FLength", objIMsr.FLength);
//Command.Parameters.AddWithValue("@FBreadth", objIMsr.FBreadth);
//Command.Parameters.AddWithValue("@FDepth", objIMsr.FDepth);
//Command.Parameters.AddWithValue("@BillType", objIMsr.BillType);
//Command.Parameters.AddWithValue("@SubFormula", objIMsr.SubFormula);
//Command.Parameters.AddWithValue("@WBSWise", objIMsr.WBSWise);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
}
}
}
else if (objWBS.MType == 1)
{
List<BillMSteelSelection> ocheckUpdate = oSteelSel.FindAll(
delegate(BillMSteelSelection sel)
{
return sel.TypeId == objAbs.TypeId && sel.AnalId == objWBS.AnalysisHeadId && sel.TransRowId == objBTrns.RowId;
});
if (ocheckUpdate.Count > 0)
{
foreach (BillMSteelSelection IMsrSel in ocheckUpdate)
{
sSql = "Insert INTO BillMSteelSelection(BillTransId,IOW_ID,IOW_Trans_Id,AnalId,SteelId) " +
" Values(" + iBillTransId + "," + objBTrns.IOW_ID + ",0," + objWBS.AnalysisHeadId + " , " +
" " + CommFun.IsNullCheck(IMsrSel.SteelId, CommFun.datatypes.vartypenumeric) + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose();
Command.Dispose();
}
}
List<BillWBSMeasurementTransBO> wbsMsr = argWBSMsrTrans.FindAll(
delegate(BillWBSMeasurementTransBO del)
{
return (del.TypeId == objAbs.TypeId && del.TransRowId == objBTrns.RowId && del.AHeadId == objWBS.AnalysisHeadId);
});
if (wbsMsr.Count > 0)
{
foreach (BillWBSMeasurementTransBO objIMsr in wbsMsr)
{
Command = new SqlCommand("Insert_BillWBSMeasurment", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@BillTransId", iBillTransId);
Command.Parameters.AddWithValue("@BillIOWTransId", objIMsr.ItemTransId);
Command.Parameters.AddWithValue("@IOWId", objIMsr.AHeadId);
Command.Parameters.AddWithValue("@RowId", objIMsr.RowId);
Command.Parameters.AddWithValue("@SlNo", objIMsr.SlNo);
Command.Parameters.AddWithValue("@Description", objIMsr.Description);
Command.Parameters.AddWithValue("@No1", objIMsr.No1);
Command.Parameters.AddWithValue("@No2", objIMsr.No2);
Command.Parameters.AddWithValue("@Length", objIMsr.Length);
Command.Parameters.AddWithValue("@Breadth", 0);
Command.Parameters.AddWithValue("@Depth", objIMsr.Diameter);
Command.Parameters.AddWithValue("@Qty", objIMsr.Qty);
Command.Parameters.AddWithValue("@Sign", "");
Command.Parameters.AddWithValue("@MBookNO", 0);
Command.Parameters.AddWithValue("@PageNo", 0);
Command.Parameters.AddWithValue("@LevelNo", 0);
Command.Parameters.AddWithValue("@ParentID", 0);
Command.Parameters.AddWithValue("@SteelId", objIMsr.SteelId);
Command.Parameters.AddWithValue("@Factor", objIMsr.Factor);
Command.Parameters.AddWithValue("@FNo1", "");
Command.Parameters.AddWithValue("@FNo2", "");
Command.Parameters.AddWithValue("@FLength", "");
Command.Parameters.AddWithValue("@FBreadth", "");
Command.Parameters.AddWithValue("@FDepth", "");
Command.Parameters.AddWithValue("@BillType", "");
Command.Parameters.AddWithValue("@SubFormula", "");
Command.Parameters.AddWithValue("@WBSWise", objIMsr.WBSWise);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
}
}
}
}
}
}
}
//if (argDPE!=null)
//{
// List<DPERegTrans> oDpe = argDPE.FindAll(delegate(DPERegTrans del) { return (del.TypeId == objAbs.TypeId); });
// if (oDpe.Count > 0)
// {
// foreach (DPERegTrans objDPE in oDpe)
// {
// //Insert BillDPETrans
// Command = new SqlCommand("Insert_BillDPETrans", conn, tran);
// Command.CommandType = CommandType.StoredProcedure;
// Command.Parameters.Clear();
// Command.Parameters.AddWithValue("@BillRegisterID", iBillRegId);
// Command.Parameters.AddWithValue("@DPERegisterID", objDPE.DPERegId);
// Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
// //Update DPERegister Set ConvertBill=1 Where DPERegisterId ="++""
// Command = new SqlCommand("Update DPERegister Set ConvertBill=1 Where DPERegisterId =" + objDPE.DPERegId + "", conn, tran);
// Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
// CommFun.ActualCompUpdate(objDPE.DPERegId, argCCId, "D", false, argrevId, argBReg.ContractorID, argDBName, conn, tran);
// }
// //InsertRateQ()
// }
//}
if (objBTrns.Type.ToString() == "I")
{
//Insert Measurment for BillTYpe =I; List<BIMeasurementBO> argBIMsrment
if (objBTrns.MType == 0 || objBTrns.MType == 2)
{
List<BIMeasurementBO> oSBIMsr = argBIMsrment.FindAll(delegate(BIMeasurementBO del) { return del.TypeId == objAbs.TypeId && del.TransRowId == objBTrns.RowId; });
if (oSBIMsr.Count > 0)
{
foreach (BIMeasurementBO objIMsr in oSBIMsr)
{
Command = new SqlCommand("Insert_BillMeasurement", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@BillTransId", iBillTransId);
Command.Parameters.AddWithValue("@BillIOWTransId", 0);
Command.Parameters.AddWithValue("@IOWId", objIMsr.IOWId);
Command.Parameters.AddWithValue("@RowId", objIMsr.RowId);
Command.Parameters.AddWithValue("@SlNo", objIMsr.SlNo);
Command.Parameters.AddWithValue("@Description", objIMsr.Description);
Command.Parameters.AddWithValue("@No1", objIMsr.No1);
Command.Parameters.AddWithValue("@No2", objIMsr.No2);
Command.Parameters.AddWithValue("@Length", objIMsr.Length);
Command.Parameters.AddWithValue("@Breadth", objIMsr.Breadth);
Command.Parameters.AddWithValue("@Depth", objIMsr.Depth);
Command.Parameters.AddWithValue("@Qty", objIMsr.Qty);
Command.Parameters.AddWithValue("@Sign", objIMsr.Sign);
Command.Parameters.AddWithValue("@MBookNO", objIMsr.MBookNO);
Command.Parameters.AddWithValue("@PageNo", objIMsr.PageNo);
Command.Parameters.AddWithValue("@LevelNo", objIMsr.LevelNo);
Command.Parameters.AddWithValue("@ParentID", objIMsr.ParentID);
Command.Parameters.AddWithValue("@SteelId", objIMsr.SteelId);
Command.Parameters.AddWithValue("@Factor", objIMsr.Factor);
if (CommFun.IsNullCheck(objIMsr.FNo1, CommFun.datatypes.vartypestring) != null)
Command.Parameters.AddWithValue("@FNo1", CommFun.IsNullCheck(objIMsr.FNo1, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@FNo1", "");
if (CommFun.IsNullCheck(objIMsr.FNo2, CommFun.datatypes.vartypestring) != null)
Command.Parameters.AddWithValue("@FNo2", CommFun.IsNullCheck(objIMsr.FNo2, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@FNo2", "");
if (Convert.ToDecimal(CommFun.IsNullCheck(objIMsr.FLength, CommFun.datatypes.vartypenumeric)) != 0)
Command.Parameters.AddWithValue("@FLength", CommFun.IsNullCheck(objIMsr.FLength, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@FLength", 0);
if (Convert.ToDecimal(CommFun.IsNullCheck(objIMsr.FBreadth, CommFun.datatypes.vartypenumeric)) != 0)
Command.Parameters.AddWithValue("@FBreadth", CommFun.IsNullCheck(objIMsr.FBreadth, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@FBreadth", 0);
if (Convert.ToDecimal(CommFun.IsNullCheck(objIMsr.FDepth, CommFun.datatypes.vartypenumeric)) != 0)
Command.Parameters.AddWithValue("@FDepth", CommFun.IsNullCheck(objIMsr.FDepth, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@FDepth", 0);
Command.Parameters.AddWithValue("@BillType", CommFun.IsNullCheck(objBTrns.Type.ToString(), CommFun.datatypes.vartypestring));
if (CommFun.IsNullCheck(objIMsr.SubFormula, CommFun.datatypes.vartypestring) != null)
Command.Parameters.AddWithValue("@SubFormula", CommFun.IsNullCheck(objIMsr.SubFormula, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@SubFormula", "");
//Command.Parameters.AddWithValue("@FNo1", objIMsr.FNo1);
//Command.Parameters.AddWithValue("@FNo2", objIMsr.FNo2);
//Command.Parameters.AddWithValue("@FLength", objIMsr.FLength);
//Command.Parameters.AddWithValue("@FBreadth", objIMsr.FBreadth);
//Command.Parameters.AddWithValue("@FDepth", objIMsr.FDepth);
//Command.Parameters.AddWithValue("@BillType", objIMsr.BillType);
//Command.Parameters.AddWithValue("@SubFormula", objIMsr.SubFormula);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
}
}
}
else if (objBTrns.MType == 1)
{
List<BillMSteelSelection> ocheckUpdate = oSteelSel.FindAll(
delegate(BillMSteelSelection sel)
{
return sel.TypeId == objAbs.TypeId && sel.TransRowId == objBTrns.RowId;
});
if (ocheckUpdate.Count > 0)
{
foreach (BillMSteelSelection IMsrSel in ocheckUpdate)
{
sSql = "Insert INTO BillMSteelSelection(BillTransId,IOW_ID,IOW_Trans_Id,AnalId,SteelId) " +
" Values(" + iBillTransId + "," + IMsrSel.BillResId + ",0,0 , " +
" " + CommFun.IsNullCheck(IMsrSel.SteelId, CommFun.datatypes.vartypenumeric) + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose();
Command.Dispose();
}
}
List<BIMeasurementBO> oSBIMsr = argBIMsrment.FindAll(
delegate(BIMeasurementBO del)
{ return del.TypeId == objAbs.TypeId && del.TransRowId == objBTrns.RowId; });
if (oSBIMsr.Count > 0)
{
foreach (BIMeasurementBO objIMsr in oSBIMsr)
{
Command = new SqlCommand("Insert_BillMeasurement", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@BillTransId", iBillTransId);
Command.Parameters.AddWithValue("@BillIOWTransId", 0);
Command.Parameters.AddWithValue("@IOWId", objIMsr.IOWId);
Command.Parameters.AddWithValue("@RowId", objIMsr.RowId);
Command.Parameters.AddWithValue("@SlNo", objIMsr.SlNo);
Command.Parameters.AddWithValue("@Description", objIMsr.Description);
Command.Parameters.AddWithValue("@No1", objIMsr.No1);
Command.Parameters.AddWithValue("@No2", objIMsr.No2);
Command.Parameters.AddWithValue("@Length", objIMsr.Length);
Command.Parameters.AddWithValue("@Breadth", 0);
Command.Parameters.AddWithValue("@Depth", objIMsr.Diameter);
Command.Parameters.AddWithValue("@Qty", objIMsr.Qty);
Command.Parameters.AddWithValue("@Sign", "");
Command.Parameters.AddWithValue("@MBookNO", 0);
Command.Parameters.AddWithValue("@PageNo", 0);
Command.Parameters.AddWithValue("@LevelNo", 0);
Command.Parameters.AddWithValue("@ParentID", 0);
Command.Parameters.AddWithValue("@SteelId", objIMsr.SteelId);
Command.Parameters.AddWithValue("@Factor", objIMsr.Factor);
Command.Parameters.AddWithValue("@FNo1", "");
Command.Parameters.AddWithValue("@FNo2", "");
Command.Parameters.AddWithValue("@FLength", "");
Command.Parameters.AddWithValue("@FBreadth", "");
Command.Parameters.AddWithValue("@FDepth", "");
Command.Parameters.AddWithValue("@BillType", objIMsr.BillType);
Command.Parameters.AddWithValue("@SubFormula", "");
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
}
}
}
}
else if (objBTrns.Type.ToString() != "I")
{
//Insert BillIOWTrans List<BillIOWTransUpdate> argBIOWTrans
int rsrceId3 = 0;
if (objBTrns.ResourceId == 0) { rsrceId3 = objBTrns.IOW_ID; }
if (objBTrns.IOW_ID == 0) { rsrceId3 = objBTrns.ResourceId; }
int iLbrResTransId = 0;
if (objBTrns.Type.ToString() == "L")
{
List<BillLBRResourceTransBO> oLsel = argLbrRes.FindAll(
delegate(BillLBRResourceTransBO del) { return del.TypeId == objAbs.TypeId && del.RowId == objBTrns.RowId; });
if (oLsel.Count > 0)
{
foreach (BillLBRResourceTransBO objLRes in oLsel)
{
if (objLRes.Qty != 0)
{
decimal dLQty = objLRes.Qty;
decimal dLAmt = (objLRes.Qty * objLRes.Rate);
if (objBTrns.Sign.ToString() == "-") { dLQty = dLQty * (-1); dLAmt = dLAmt * (-1); }
sSql = "INSERT INTO BillLbrResourceTrans (BillTransId,CompId,LbrResourceId,Qty,Rate,AnalysisType,Amount) " +
"VALUES(" + iBillTransId + "," + objLRes.ItemTransId + "," + objLRes.LbrResourceId + "," + dLQty + "," + objLRes.Rate + ",'" + objLRes.RateType + "'," + dLAmt + ") Select SCOPE_IDENTITY();";
Command = new SqlCommand(sSql, conn, tran);
iLbrResTransId = Convert.ToInt32(Command.ExecuteScalar());
Command.Dispose();
// Labour Bill IOW Trans
List<BillIOWTransUpdate> oSIOW = argBIOWTrans.FindAll(
delegate(BillIOWTransUpdate del)
{ return del.TypeId == objAbs.TypeId && del.RowId == objBTrns.RowId && del.LbrResourceId == objLRes.LbrResourceId && del.RateType == objLRes.RateType; });
if (oSIOW.Count > 0)
{
foreach (BillIOWTransUpdate objIOW in oSIOW)
{
if (objIOW.Qty != 0)
{
decimal dIQty = objIOW.Qty;
decimal dIAmt = objIOW.Amount;
if (objBTrns.Sign.ToString() == "-") { dIQty = dIQty * (-1); dIAmt = dIAmt * (-1); }
Command = new SqlCommand("Insert_BillIOWTrans", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@BillIOWTransID", objIOW.BillIOWTransID);
Command.Parameters["@BillIOWTransID"].Direction = ParameterDirection.Output;
Command.Parameters.AddWithValue("@BillTransId", iBillTransId);
Command.Parameters.AddWithValue("@IOW_ID", objIOW.IOW_ID);
Command.Parameters.AddWithValue("@Qty", dIQty);
Command.Parameters.AddWithValue("@ClaimType", objIOW.ClaimType);
Command.Parameters.AddWithValue("@AnalysisHeadId", objIOW.AnalysisHeadID);
Command.Parameters.AddWithValue("@LbrResTransId", iLbrResTransId);
Command.Parameters.AddWithValue("@MType", objIOW.MType);
Command.Parameters.AddWithValue("@MUnitId", objIOW.MUnitId);
Command.Parameters.AddWithValue("@UFactor", objIOW.UFactor);
Command.Parameters.AddWithValue("@Rate", objIOW.Rate);
Command.Parameters.AddWithValue("@Amount", dIAmt);
Command.Parameters.AddWithValue("@SubIOWId", objIOW.SubIOWID);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
iBillIOWTransId = (int)Command.Parameters["@BillIOWTransID"].Value;
//Insert IOWLabourTrans List<BillIOWLabour> argBIOWLbrTrans
//List<BillIOWLbrTrans> oIOWLbr = argBIOWLbrTrans.FindAll(delegate(BillIOWLbrTrans del) { return ((del.IOWRowId == objIOW.RowId) && (del.BEntryRowId == objBTrns.RowId)); });
//if (oIOWLbr.Count > 0)
//{
// foreach (BillIOWLbrTrans objIOWLbr in oIOWLbr)
// {
// Command = new SqlCommand("Insert_BillIOWLabourTrans", conn, tran);
// Command.CommandType = CommandType.StoredProcedure;
// Command.Parameters.Clear();
// Command.Parameters.AddWithValue("@BillIOWTransID", iBillIOWTransId);
// Command.Parameters.AddWithValue("@BillTransId", iBillTransId);
// Command.Parameters.AddWithValue("@LabourTypeId", objIOWLbr.LbrTypeId);
// Command.Parameters.AddWithValue("@Qty", objIOWLbr.Qtty);
// Command.Parameters.AddWithValue("@Rate", objIOWLbr.Rate);
// Command.Parameters.AddWithValue("@Amount", objIOWLbr.Amount);
// Command.Parameters.AddWithValue("@AppRate", objIOWLbr.AppRate);
// Command.Parameters.AddWithValue("@MinRate", objIOWLbr.MinRate);
// Command.Parameters.AddWithValue("@AnalysisId", 0);
// Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
// }
//}
//Insert BillIOWMeasurement List<BillIOWMeasurementBOUpdate> argBIOWMsrment
if (objIOW.MType == 0 || objIOW.MType == 2)
{
List<BillIOWMeasurementBOUpdate> oSIOWMsrMnt = argBIOWMsrment.FindAll(
delegate(BillIOWMeasurementBOUpdate del)
{ return (del.TypeId == objAbs.TypeId && del.RowId == objBTrns.RowId && del.LbrResourceId == objLRes.LbrResourceId && del.RateType == objLRes.RateType && del.AnalysisId == objIOW.AnalysisHeadID && del.IOWId == objIOW.IOW_ID); });
if (oSIOWMsrMnt.Count > 0)
{
foreach (BillIOWMeasurementBOUpdate objIOWMsr in oSIOWMsrMnt)
{
Command = new SqlCommand("Insert_BillMeasurement", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@BillTransId", iBillTransId);
Command.Parameters.AddWithValue("@BillIOWTransId", iBillIOWTransId);
Command.Parameters.AddWithValue("@IOWId", objIOWMsr.IOWId);
Command.Parameters.AddWithValue("@RowId", objIOWMsr.BEntryRowId);
Command.Parameters.AddWithValue("@SlNo", objIOWMsr.SlNo);
Command.Parameters.AddWithValue("@Description", objIOWMsr.Description);
Command.Parameters.AddWithValue("@No1", objIOWMsr.No1);
Command.Parameters.AddWithValue("@No2", objIOWMsr.No2);
Command.Parameters.AddWithValue("@Length", objIOWMsr.Length);
Command.Parameters.AddWithValue("@Breadth", objIOWMsr.Breadth);
Command.Parameters.AddWithValue("@Depth", objIOWMsr.Depth);
Command.Parameters.AddWithValue("@Qty", objIOWMsr.Qty);
Command.Parameters.AddWithValue("@Sign", objIOWMsr.Sign);
Command.Parameters.AddWithValue("@MBookNO", objIOWMsr.MBookNO);
Command.Parameters.AddWithValue("@PageNo", objIOWMsr.PageNo);
Command.Parameters.AddWithValue("@LevelNo", objIOWMsr.LevelNo);
Command.Parameters.AddWithValue("@ParentID", objIOWMsr.ParentID);
Command.Parameters.AddWithValue("@SteelId", objIOWMsr.SteelId);
Command.Parameters.AddWithValue("@Factor", objIOWMsr.Factor);
if (CommFun.IsNullCheck(objIOWMsr.FNo1, CommFun.datatypes.vartypestring) != null)
Command.Parameters.AddWithValue("@FNo1", CommFun.IsNullCheck(objIOWMsr.FNo1, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@FNo1", "");
if (CommFun.IsNullCheck(objIOWMsr.FNo2, CommFun.datatypes.vartypestring) != null)
Command.Parameters.AddWithValue("@FNo2", CommFun.IsNullCheck(objIOWMsr.FNo2, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@FNo2", "");
if (Convert.ToDecimal(CommFun.IsNullCheck(objIOWMsr.FLength, CommFun.datatypes.vartypenumeric)) != 0)
Command.Parameters.AddWithValue("@FLength", CommFun.IsNullCheck(objIOWMsr.FLength, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@FLength", 0);
if (Convert.ToDecimal(CommFun.IsNullCheck(objIOWMsr.FBreadth, CommFun.datatypes.vartypenumeric)) != 0)
Command.Parameters.AddWithValue("@FBreadth", CommFun.IsNullCheck(objIOWMsr.FBreadth, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@FBreadth", 0);
if (Convert.ToDecimal(CommFun.IsNullCheck(objIOWMsr.FDepth, CommFun.datatypes.vartypenumeric)) != 0)
Command.Parameters.AddWithValue("@FDepth", CommFun.IsNullCheck(objIOWMsr.FDepth, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@FDepth", 0);
Command.Parameters.AddWithValue("@BillType", CommFun.IsNullCheck(objBTrns.Type.ToString(), CommFun.datatypes.vartypestring));
if (CommFun.IsNullCheck(objIOWMsr.SubFormula, CommFun.datatypes.vartypestring) != null)
Command.Parameters.AddWithValue("@SubFormula", CommFun.IsNullCheck(objIOWMsr.SubFormula, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@SubFormula", "");
//Command.Parameters.AddWithValue("@FNo1", objIOWMsr.FNo1);
//Command.Parameters.AddWithValue("@FNo2", objIOWMsr.FNo2);
//Command.Parameters.AddWithValue("@FLength", objIOWMsr.FLength);
//Command.Parameters.AddWithValue("@FBreadth", objIOWMsr.FBreadth);
//Command.Parameters.AddWithValue("@FDepth", objIOWMsr.FDepth);
//Command.Parameters.AddWithValue("@BillType", objIOWMsr.BillType);
//Command.Parameters.AddWithValue("@SubFormula", objIOWMsr.SubFormula);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
}
}
}
else if (objIOW.MType == 1)
{
List<BillMSteelSelection> ocheckUpdate = oSteelSel.FindAll(
delegate(BillMSteelSelection del)
{
return (del.TypeId == objAbs.TypeId && del.TransRowId == objBTrns.RowId && del.AnalId == objIOW.AnalysisHeadID && del.IOWId == objIOW.IOW_ID);
//if (objIOW.AnalysisHeadID != 0)
// return sel.BillResId == rsrceId3 && sel.IOWId == objIOW.IOW_ID && sel.AnalId == objIOW.AnalysisHeadID && sel.TransRowId == objBTrns.RowId;
//else
// return sel.BillResId == rsrceId3 && sel.IOWId == objIOW.IOW_ID && sel.TransRowId == objBTrns.RowId;
});
if (ocheckUpdate.Count > 0)
{
foreach (BillMSteelSelection IMsrSel in ocheckUpdate)
{
sSql = "Insert INTO BillMSteelSelection(BillTransId,IOW_ID,IOW_Trans_Id,AnalId,SteelId) " +
" Values(" + iBillTransId + "," + rsrceId3 + "," + objIOW.IOW_ID + "," + objIOW.AnalysisHeadID + " , " +
" " + CommFun.IsNullCheck(IMsrSel.SteelId, CommFun.datatypes.vartypenumeric) + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose();
Command.Dispose();
}
}
List<BillIOWMeasurementBOUpdate> oSIOWMsrMnt = argBIOWMsrment.FindAll(
delegate(BillIOWMeasurementBOUpdate del)
{
return (del.TypeId == objAbs.TypeId && del.RowId == objBTrns.RowId && del.LbrResourceId == objLRes.LbrResourceId && del.RateType == objLRes.RateType && del.AnalysisId == objIOW.AnalysisHeadID && del.IOWId == objIOW.IOW_ID);
});
if (oSIOWMsrMnt.Count > 0)
{
foreach (BillIOWMeasurementBOUpdate objIOWMsr in oSIOWMsrMnt)
{
Command = new SqlCommand("Insert_BillMeasurement", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@BillTransId", iBillTransId);
Command.Parameters.AddWithValue("@BillIOWTransId", iBillIOWTransId);
Command.Parameters.AddWithValue("@IOWId", objIOWMsr.IOWId);
Command.Parameters.AddWithValue("@RowId", objIOWMsr.BEntryRowId);
Command.Parameters.AddWithValue("@SlNo", objIOWMsr.SlNo);
Command.Parameters.AddWithValue("@Description", objIOWMsr.Description);
Command.Parameters.AddWithValue("@No1", objIOWMsr.No1);
Command.Parameters.AddWithValue("@No2", objIOWMsr.No2);
Command.Parameters.AddWithValue("@Length", objIOWMsr.Length);
Command.Parameters.AddWithValue("@Breadth", 0);
Command.Parameters.AddWithValue("@Depth", objIOWMsr.Diameter);
Command.Parameters.AddWithValue("@Qty", objIOWMsr.Qty);
Command.Parameters.AddWithValue("@Sign", "");
Command.Parameters.AddWithValue("@MBookNO", 0);
Command.Parameters.AddWithValue("@PageNo", 0);
Command.Parameters.AddWithValue("@LevelNo", 0);
Command.Parameters.AddWithValue("@ParentID", 0);
Command.Parameters.AddWithValue("@SteelId", objIOWMsr.SteelId);
Command.Parameters.AddWithValue("@Factor", objIOWMsr.Factor);
Command.Parameters.AddWithValue("@FNo1", "");
Command.Parameters.AddWithValue("@FNo2", "");
Command.Parameters.AddWithValue("@FLength", "");
Command.Parameters.AddWithValue("@FBreadth", "");
Command.Parameters.AddWithValue("@FDepth", "");
Command.Parameters.AddWithValue("@BillType", objIOWMsr.BillType);
Command.Parameters.AddWithValue("@SubFormula", "");
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
}
}
}
}
}
}
}
}
}
}
else if (objBTrns.Type.ToString() == "A" || objBTrns.Type.ToString() == "S")
{
List<BillIOWTransUpdate> oSIOW = argBIOWTrans.FindAll(delegate(BillIOWTransUpdate del) { return del.RowId == objBTrns.RowId && del.TypeId == objAbs.TypeId; });
if (oSIOW.Count > 0)
{
foreach (BillIOWTransUpdate objIOW in oSIOW)
{
if (objIOW.Qty != 0)
{
decimal dIQty = objIOW.Qty;
decimal dIAmt = objIOW.Amount;
if (objBTrns.Sign.ToString() == "-") { dIQty = dIQty * (-1); dIAmt = dIAmt * (-1); }
Command = new SqlCommand("Insert_BillIOWTrans", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@BillIOWTransID", objIOW.BillIOWTransID);
Command.Parameters["@BillIOWTransID"].Direction = ParameterDirection.Output;
Command.Parameters.AddWithValue("@BillTransId", iBillTransId);
Command.Parameters.AddWithValue("@IOW_ID", objIOW.IOW_ID);
Command.Parameters.AddWithValue("@Qty", dIQty);
Command.Parameters.AddWithValue("@ClaimType", objIOW.ClaimType);
Command.Parameters.AddWithValue("@AnalysisHeadId", objIOW.AnalysisHeadID);
Command.Parameters.AddWithValue("@LbrResTransId", 0);
Command.Parameters.AddWithValue("@MType", objIOW.MType);
Command.Parameters.AddWithValue("@MUnitId", objIOW.MUnitId);
Command.Parameters.AddWithValue("@UFactor", objIOW.UFactor);
Command.Parameters.AddWithValue("@Rate", objIOW.Rate);
Command.Parameters.AddWithValue("@Amount", dIAmt);
Command.Parameters.AddWithValue("@SubIOWId", objIOW.SubIOWID);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
iBillIOWTransId = (int)Command.Parameters["@BillIOWTransID"].Value;
List<LabourTransBO> lChk = argItemLbr.FindAll(delegate(LabourTransBO del) { return del.AbsTypeId == objAbs.TypeId && del.RowId == objBTrns.RowId && del.AnalysisId == objIOW.AnalysisHeadID && del.IOWId == objIOW.IOW_ID; });
foreach (LabourTransBO objLbr in lChk)
{
sSql = "Insert into BillIOWLabourTrans(BillIOWTransID,BillTransId,LabourTypeId,Qty,Rate,Amount,MinRate,AppRate,AnalysisId,IOWId) " +
"Values(" + iBillIOWTransId + "," + iBillTransId + "," + objLbr.LabourTypeID + "," + objLbr.Qty + "," + objLbr.Rate + "," + objLbr.Amount + "," + objLbr.MinRate + ", " + objLbr.AppRate + "," + objIOW.AnalysisHeadID + "," + objIOW.IOW_ID + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose();
Command.Dispose();
}
//Insert IOWLabourTrans List<BillIOWLabour> argBIOWLbrTrans
//List<BillIOWLbrTrans> oIOWLbr = argBIOWLbrTrans.FindAll(delegate(BillIOWLbrTrans del)
//{
// return ((del.IOWRowId == objIOW.BillTransRowId) && (del.BEntryRowId == objBTrns.RowId));
//});
//List<BillIOWLbrTrans> oIOWLbr = argBIOWLbrTrans.FindAll(delegate(BillIOWLbrTrans del)
//{
// //if (objIOW.AnalysisHeadID!=0)
// // return ((del.DPEItemTransId == rsrceId3) && (del.IOW_ID == objIOW.IOW_ID) && (del.AnalysisId == objIOW.AnalysisHeadID));
// //else
// return ((del.DPEItemTransId == rsrceId3) && (del.IOW_ID == objIOW.IOW_ID));
//});
//if (oIOWLbr.Count > 0)
//{
// foreach (BillIOWLbrTrans objIOWLbr in oIOWLbr)
// {
// Command = new SqlCommand("Insert_BillIOWLabourTrans", conn, tran);
// Command.CommandType = CommandType.StoredProcedure;
// Command.Parameters.Clear();
// Command.Parameters.AddWithValue("@BillIOWTransID", iBillIOWTransId);
// Command.Parameters.AddWithValue("@BillTransId", iBillTransId);
// Command.Parameters.AddWithValue("@LabourTypeId", objIOWLbr.LbrTypeId);
// Command.Parameters.AddWithValue("@Qty", objIOWLbr.Qtty);
// Command.Parameters.AddWithValue("@Rate", objIOWLbr.Rate);
// Command.Parameters.AddWithValue("@Amount", objIOWLbr.Amount);
// Command.Parameters.AddWithValue("@AppRate", objIOWLbr.AppRate);
// Command.Parameters.AddWithValue("@MinRate", objIOWLbr.MinRate);
// Command.Parameters.AddWithValue("@AnalysisId", 0);
// Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
// }
//}
//Insert BillIOWMeasurement List<BillIOWMeasurementBOUpdate> argBIOWMsrment
int rsrceId = 0;
if (objBTrns.ResourceId == 0) { rsrceId = objBTrns.IOW_ID; }
else if (objBTrns.IOW_ID == 0) { rsrceId = objBTrns.ResourceId; }
if (objIOW.MType == 0 || objIOW.MType == 2)
{
List<BillIOWMeasurementBOUpdate> oSIOWMsrMnt = argBIOWMsrment.FindAll(delegate(BillIOWMeasurementBOUpdate del)
{
return del.TypeId == objAbs.TypeId && del.TransRowId == objBTrns.RowId && del.AnalysisId == objIOW.AnalysisHeadID && del.IOWId == objIOW.IOW_ID;
});
if (oSIOWMsrMnt.Count > 0)
{
foreach (BillIOWMeasurementBOUpdate objIOWMsr in oSIOWMsrMnt)
{
Command = new SqlCommand("Insert_BillMeasurement", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@BillTransId", iBillTransId);
Command.Parameters.AddWithValue("@BillIOWTransId", iBillIOWTransId);
Command.Parameters.AddWithValue("@IOWId", objIOWMsr.IOWId);
Command.Parameters.AddWithValue("@RowId", objIOWMsr.BEntryRowId);
Command.Parameters.AddWithValue("@SlNo", objIOWMsr.SlNo);
Command.Parameters.AddWithValue("@Description", objIOWMsr.Description);
Command.Parameters.AddWithValue("@No1", objIOWMsr.No1);
Command.Parameters.AddWithValue("@No2", objIOWMsr.No2);
Command.Parameters.AddWithValue("@Length", objIOWMsr.Length);
Command.Parameters.AddWithValue("@Breadth", objIOWMsr.Breadth);
Command.Parameters.AddWithValue("@Depth", objIOWMsr.Depth);
Command.Parameters.AddWithValue("@Qty", objIOWMsr.Qty);
Command.Parameters.AddWithValue("@Sign", objIOWMsr.Sign);
Command.Parameters.AddWithValue("@MBookNO", objIOWMsr.MBookNO);
Command.Parameters.AddWithValue("@PageNo", objIOWMsr.PageNo);
Command.Parameters.AddWithValue("@LevelNo", objIOWMsr.LevelNo);
Command.Parameters.AddWithValue("@ParentID", objIOWMsr.ParentID);
Command.Parameters.AddWithValue("@SteelId", objIOWMsr.SteelId);
Command.Parameters.AddWithValue("@Factor", objIOWMsr.Factor);
if (CommFun.IsNullCheck(objIOWMsr.FNo1, CommFun.datatypes.vartypestring) != null)
Command.Parameters.AddWithValue("@FNo1", CommFun.IsNullCheck(objIOWMsr.FNo1, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@FNo1", "");
if (CommFun.IsNullCheck(objIOWMsr.FNo2, CommFun.datatypes.vartypestring) != null)
Command.Parameters.AddWithValue("@FNo2", CommFun.IsNullCheck(objIOWMsr.FNo2, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@FNo2", "");
if (Convert.ToDecimal(CommFun.IsNullCheck(objIOWMsr.FLength, CommFun.datatypes.vartypenumeric)) != 0)
Command.Parameters.AddWithValue("@FLength", CommFun.IsNullCheck(objIOWMsr.FLength, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@FLength", 0);
if (Convert.ToDecimal(CommFun.IsNullCheck(objIOWMsr.FBreadth, CommFun.datatypes.vartypenumeric)) != 0)
Command.Parameters.AddWithValue("@FBreadth", CommFun.IsNullCheck(objIOWMsr.FBreadth, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@FBreadth", 0);
if (Convert.ToDecimal(CommFun.IsNullCheck(objIOWMsr.FDepth, CommFun.datatypes.vartypenumeric)) != 0)
Command.Parameters.AddWithValue("@FDepth", CommFun.IsNullCheck(objIOWMsr.FDepth, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@FDepth", 0);
Command.Parameters.AddWithValue("@BillType", CommFun.IsNullCheck(objBTrns.Type.ToString(), CommFun.datatypes.vartypestring));
if (CommFun.IsNullCheck(objIOWMsr.SubFormula, CommFun.datatypes.vartypestring) != null)
Command.Parameters.AddWithValue("@SubFormula", CommFun.IsNullCheck(objIOWMsr.SubFormula, CommFun.datatypes.vartypestring));
else
Command.Parameters.AddWithValue("@SubFormula", "");
//Command.Parameters.AddWithValue("@FNo1", objIOWMsr.FNo1);
//Command.Parameters.AddWithValue("@FNo2", objIOWMsr.FNo2);
//Command.Parameters.AddWithValue("@FLength", objIOWMsr.FLength);
//Command.Parameters.AddWithValue("@FBreadth", objIOWMsr.FBreadth);
//Command.Parameters.AddWithValue("@FDepth", objIOWMsr.FDepth);
//Command.Parameters.AddWithValue("@BillType", objIOWMsr.BillType);
//Command.Parameters.AddWithValue("@SubFormula", objIOWMsr.SubFormula);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
}
}
}
else if (objIOW.MType == 1)
{
List<BillMSteelSelection> ocheckUpdate = oSteelSel.FindAll(
delegate(BillMSteelSelection del)
{
return del.TypeId == objAbs.TypeId && del.TransRowId == objBTrns.RowId && del.AnalId == objIOW.AnalysisHeadID && del.IOWId == objIOW.IOW_ID;
//if (objIOW.AnalysisHeadID != 0)
// return sel.BillResId == rsrceId && sel.IOWId == objIOW.IOW_ID && sel.AnalId == objIOW.AnalysisHeadID && sel.TransRowId == objBTrns.RowId;
//else
// return sel.BillResId == rsrceId && sel.IOWId == objIOW.IOW_ID && sel.TransRowId == objBTrns.RowId;
});
if (ocheckUpdate.Count > 0)
{
foreach (BillMSteelSelection IMsrSel in ocheckUpdate)
{
sSql = "Insert INTO BillMSteelSelection(BillTransId,IOW_ID,IOW_Trans_Id,AnalId,SteelId) " +
" Values(" + iBillTransId + "," + rsrceId + "," + objIOW.IOW_ID + "," + objIOW.AnalysisHeadID + " , " +
" " + CommFun.IsNullCheck(IMsrSel.SteelId, CommFun.datatypes.vartypenumeric) + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose();
Command.Dispose();
}
}
List<BillIOWMeasurementBOUpdate> oSIOWMsrMnt = argBIOWMsrment.FindAll(
delegate(BillIOWMeasurementBOUpdate del)
{
return del.TypeId == objAbs.TypeId && del.TransRowId == objBTrns.RowId && del.AnalysisId == objIOW.AnalysisHeadID && del.IOWId == objIOW.IOW_ID;
//if (objBTrns.Type == "A")
// return del.TypeId == objAbs.TypeId && del.RowId == objBTrns.RowId && del.RateType == objIOW.RateType && del.AnalysisId == objIOW.AnalysisHeadID && del.IOWId == objIOW.IOW_ID;
//else
// return del.TypeId == objAbs.TypeId && del.RowId == objBTrns.RowId && del.AnalysisId == objIOW.AnalysisHeadID && del.IOWId == objIOW.IOW_ID;
});
if (oSIOWMsrMnt.Count > 0)
{
foreach (BillIOWMeasurementBOUpdate objIOWMsr in oSIOWMsrMnt)
{
Command = new SqlCommand("Insert_BillMeasurement", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@BillTransId", iBillTransId);
Command.Parameters.AddWithValue("@BillIOWTransId", iBillIOWTransId);
Command.Parameters.AddWithValue("@IOWId", objIOWMsr.IOWId);
Command.Parameters.AddWithValue("@RowId", objIOWMsr.BEntryRowId);
Command.Parameters.AddWithValue("@SlNo", objIOWMsr.SlNo);
Command.Parameters.AddWithValue("@Description", objIOWMsr.Description);
Command.Parameters.AddWithValue("@No1", objIOWMsr.No1);
Command.Parameters.AddWithValue("@No2", objIOWMsr.No2);
Command.Parameters.AddWithValue("@Length", objIOWMsr.Length);
Command.Parameters.AddWithValue("@Breadth", 0);
Command.Parameters.AddWithValue("@Depth", objIOWMsr.Diameter);
Command.Parameters.AddWithValue("@Qty", objIOWMsr.Qty);
Command.Parameters.AddWithValue("@Sign", "");
Command.Parameters.AddWithValue("@MBookNO", 0);
Command.Parameters.AddWithValue("@PageNo", 0);
Command.Parameters.AddWithValue("@LevelNo", 0);
Command.Parameters.AddWithValue("@ParentID", 0);
Command.Parameters.AddWithValue("@SteelId", objIOWMsr.SteelId);
Command.Parameters.AddWithValue("@Factor", objIOWMsr.Factor);
Command.Parameters.AddWithValue("@FNo1", "");
Command.Parameters.AddWithValue("@FNo2", "");
Command.Parameters.AddWithValue("@FLength", "");
Command.Parameters.AddWithValue("@FBreadth", "");
Command.Parameters.AddWithValue("@FDepth", "");
Command.Parameters.AddWithValue("@BillType", objIOWMsr.BillType);
Command.Parameters.AddWithValue("@SubFormula", "");
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
}
}
}
}
}
}
}
}
//int rsrceId2 = 0;
//if (objBTrns.ResourceId == 0) { rsrceId2 = objBTrns.IOW_ID; }
//else if (objBTrns.IOW_ID == 0) { rsrceId2 = objBTrns.ResourceId; }
//List<BillItemLbrStrnthTransBO> oSItmLbrTrns = argBLbrItem.FindAll(delegate(BillItemLbrStrnthTransBO del) { return del.BAbsTypeId == objBTrns.BAbsTypeId && del.ItemTransId == rsrceId2; });
//if (oSItmLbrTrns.Count > 0)
//{
// foreach (BillItemLbrStrnthTransBO objItem in oSItmLbrTrns)
// {
// Command = new SqlCommand("Insert_BillItemLabourTrans", conn, tran);
// Command.CommandType = CommandType.StoredProcedure;
// Command.Parameters.Clear();
// Command.Parameters.AddWithValue("@BillTransID", iBillTransId);
// Command.Parameters.AddWithValue("@LabourTypeID", objItem.LbrTypeId);
// Command.Parameters.AddWithValue("@Qty", objItem.Qtty);
// Command.Parameters.AddWithValue("@Rate", objItem.Rate);
// Command.Parameters.AddWithValue("@Amount", objItem.Amount);
// Command.Parameters.AddWithValue("@AppRate", objItem.AppRate);
// Command.Parameters.AddWithValue("@MinRate", objItem.MinRate);
// Command.ExecuteNonQuery(); Command.Dispose();
// }
//}
}
}
}
else if (objAbs.FormatTypeId == 5)
{
//Insert TDSDetails
//List<BillFormateTDSBO> argBFTDS
List<BillFormateTDSNewBO> oSL = argBFTDS.FindAll(delegate(BillFormateTDSNewBO del) { return ((del.BAbsTypeId == objAbs.TypeId) && (del.BAbsRowId == objAbs.RowId)); });
if (oSL.Count > 0)
{
int sLId = CommFun.GetSubLedgerId(objAbs.QualMId, 8, conn, tran);
foreach (BillFormateTDSNewBO objTDSF in oSL)
{
Command = new SqlCommand("Insert_TDSBillFormatTrans", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@BillRegisterId", iBillRegId);
Command.Parameters.AddWithValue("@Formula", objTDSF.Formula);
Command.Parameters.AddWithValue("@TypeId", objTDSF.BAbsTypeId);
Command.Parameters.AddWithValue("@GrossAmt", objTDSF.GrossAmt);
Command.Parameters.AddWithValue("@TDSPer", objTDSF.TDSPer);
Command.Parameters.AddWithValue("@TDSAmt", objTDSF.TDSAmt);
Command.Parameters.AddWithValue("@SurPer", objTDSF.SurPer);
Command.Parameters.AddWithValue("@SurAmt", objTDSF.SurAmt);
Command.Parameters.AddWithValue("@EDPer", objTDSF.EDPer);
Command.Parameters.AddWithValue("@EDAmt", objTDSF.EDAmt);
Command.Parameters.AddWithValue("@HEDPer", objTDSF.HEDPer);
Command.Parameters.AddWithValue("@HEDAmt", objTDSF.HEDAmt);
Command.Parameters.AddWithValue("@NetPer", objTDSF.NetPer);
Command.Parameters.AddWithValue("@TDSNetAmt", objTDSF.NetTDSAmt);
Command.Parameters.AddWithValue("@QualMId", objAbs.QualMId);
Command.Parameters.AddWithValue("@AccountId", objAbs.AccountId);
Command.Parameters.AddWithValue("@SubLedgerId", sLId);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
}
}
}
else if (objAbs.FormatTypeId == 6 && objAbs.AddType == 1)
{
//Insert ServiceTaxDetails
//List<BillServiceTaxNewBO> argBFST
List<BillServiceTaxNewBO> oSL = argBFST.FindAll(delegate(BillServiceTaxNewBO del) { return ((del.BAbsTypeId == objAbs.TypeId) && (del.BAbsRowId == objAbs.RowId)); });
if (oSL.Count > 0)
{
int sLId = CommFun.GetSubLedgerId(objAbs.QualMId, 8, conn, tran);
foreach (BillServiceTaxNewBO objServiceT in oSL)
{
sSql = "Insert Into BillServiceTaxTrans(BillRegId,TypeId,Formula,GrossAmt,AccountId,TaxablePer,TaxableValue,ExpPer " +
",ExpValue,SurCharge,SurValue,EDCess,EDValue,HEDCess,HEDValue,NetPer,NetAmt) " +
"Values (" + iBillRegId + "," + objServiceT.BAbsTypeId + ",'" + objServiceT.Formula + "'," + objServiceT.GrossAmt + "," + objServiceT.AccountId + ", " +
" " + objServiceT.TaxablePer + "," + objServiceT.TaxableAmt + "," + objServiceT.ExpPer + ", " +
" " + objServiceT.ExpPerAmt + "," + objServiceT.SurPer + "," + objServiceT.SurAmt + "," + objServiceT.EDPer + "," + objServiceT.EDAmt + " ," +
" " + objServiceT.HEDPer + "," + objServiceT.HEDAmt + "," + objServiceT.NetPer + "," + objServiceT.NetAmt + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
}
}
}
else if (objAbs.FormatTypeId == 3)
{
//Insert Material Recovery
List<BillMaterialRecovery> oSM = argBMatRec.FindAll(delegate(BillMaterialRecovery del) { return ((del.BAbsRowId == objAbs.RowId) && (del.BAbsTypeId == objAbs.TypeId)); });
if (oSM.Count > 0)
{
foreach (BillMaterialRecovery objMat in oSM)
{
Command = new SqlCommand("Insert_BillMaterialRecovery", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@BillRegId", iBillRegId);
Command.Parameters.AddWithValue("@Comp_ID", objMat.Comp_Id);
Command.Parameters.AddWithValue("@Unit", objMat.Unit);
Command.Parameters.AddWithValue("@Qty", objMat.Qty);
Command.Parameters.AddWithValue("@Rate", objMat.Rate);
Command.Parameters.AddWithValue("@Amount", objMat.Amount);
Command.Parameters.AddWithValue("@IssueId", objMat.IssueId);
Command.Parameters.AddWithValue("@TypeId", objMat.TypeId);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
if (BsfGlobal.g_bMMSDB == true)
{
sSql = "Update [" + BsfGlobal.g_sMMSDBName + "].dbo.IssueTrans Set RecQty=(RecQty-" + objMat.PrevQty + ")+Qty " +
"from BillMaterialRecovery WHERE " +
"BillRegId = " + iBillRegId + " And IssueId <> 0 " +
"and [" + BsfGlobal.g_sMMSDBName + "].dbo.IssueTrans.IssueTransId=BillMaterialRecovery.IssueId ";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose();
Command.Dispose();
}
}
}
}
else if (objAbs.FormatTypeId == 9)
{
if ((objAbs.BFormatType == "B" || objAbs.BFormatType == "A") && argInternal == true && (objAbs.AddType == 1 || objAbs.AddType == 0))
{
if (objAbs.BFormatType == "B" && argInternal == true && objAbs.AddType == 0)
{
if (dtLbrAdj.Rows.Count > 0)
{
sSql = "Delete LabourAdvAdjustment Where BillRegisterId=" + iBillRegId + " ";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
foreach (DataRow dr in dtLbrAdj.Rows)
{
decimal AdvAmount = Convert.ToDecimal(CommFun.IsNullCheck(dr["CAmount"], CommFun.datatypes.vartypenumeric));
if (AdvAmount > 0)
{
sSql = "Insert Into LabourAdvAdjustment (BillRegisterId,LabourId,Amount,RegId,TypeId) " +
"Values (" + iBillRegId + "," + dr["LabourId"] + ",'" + AdvAmount + "'," + dr["RegId"] + ","+objAbs.TypeId+") ";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
}
}
}
}
if (objAbs.BFormatType == "A" && argInternal == true && objAbs.AddType == 1)
{
int iSign = 0;
List<BillLabourAdvance> chkAdv = argBLbrAdv.FindAll(
delegate(BillLabourAdvance del)
{
return ((del.bAbsRowId == objAbs.RowId) && (del.bAbsTypeId == objAbs.TypeId));
});
if (chkAdv.Count > 0)
{
foreach (BillLabourAdvance objLAdv in chkAdv)
{
if (objLAdv.Sign == "+")
iSign = 1;
else
iSign = 0;
sSql = "Insert Into BillLabourAdvance(BillRegisterId,BFormatType,LabourId,TypeId,Sign,Amount) values " +
"(" + iBillRegId + ",'" + objLAdv.BFormatType + "'," + objLAdv.LabourId + "," + objLAdv.bAbsTypeId + "," + iSign + "," + objLAdv.Amount + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
}
}
}
}
}
else if (objAbs.FormatTypeId == 4)
{
//Insert LabourTypeTrans
if (argInternal == false && objAbs.BFormatType.ToString() == "B")
{
List<BillLabourTypeTransBO> oSLt = argBLbrType.FindAll(delegate(BillLabourTypeTransBO del) { return (del.TypeId == objAbs.TypeId); });
if (oSLt.Count > 0)
{
foreach (BillLabourTypeTransBO objLType in oSLt)
{
if (objLType.Qty != 0)
{
int iFromLS = 0;
if (objLType.FromLS == true) { iFromLS = 1; }
sSql = "Insert into BillLabourTypeTrans(BillRegisterId,LabourTypeId,TypeId,qty,Rate,Amount,FromLS) " +
"Values(" + iBillRegId + "," + objLType.LabourTypeID + "," + objLType.TypeId + "," + objLType.Qty + "," + objLType.Rate + "," + objLType.Amount + "," + iFromLS + ") SELECT SCOPE_IDENTITY();";
Command = new SqlCommand(sSql, conn, tran);
int iTransId = int.Parse(Command.ExecuteScalar().ToString());
Command.Dispose();
if (argLSWBS.Count > 0)
{
List<BillLSWBSTrans> oSLt2 = argLSWBS.FindAll(delegate(BillLSWBSTrans del) { return ((del.TypeId == objAbs.TypeId) && (del.TransRowId == objLType.RowId)); });
if (oSLt2.Count > 0)
{
foreach (BillLSWBSTrans objLSWBS in oSLt2)
{
if (objLSWBS.Qty != 0)
{
sSql = "Insert Into BillLabourTypeWBSTrans (BillLabourTypeTransId,AnalysisId,Qty) " +
"Values (" + iTransId + "," + objLSWBS.AnalysisId + "," + objLSWBS.Qty + ") SELECT SCOPE_IDENTITY();";
Command = new SqlCommand(sSql, conn, tran);
int iWBSTransId = int.Parse(Command.ExecuteScalar().ToString());
Command.Dispose();
List<BillLSVTrans> oLSV = argBillLSV.FindAll(delegate(BillLSVTrans del) { return ((del.TypeId == objAbs.TypeId) && (del.TransRowId == objLType.RowId) && (del.AnalysisId == objLSWBS.AnalysisId)); });
if (oLSV.Count > 0)
{
foreach (BillLSVTrans objLSV in oLSV)
{
sSql = "Insert into BillLabourTypeWBSLSTrans(BillLSWBSTransId,LSId,Qty) " +
"Values(" + iWBSTransId + "," + objLSV.LSId + ", " + objLSV.BillQty + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery();
Command.Dispose();
}
}
}
}
}
}
else
{
if (argBillLSV.Count > 0)
{
List<BillLSVTrans> oLSV = argBillLSV.FindAll(delegate(BillLSVTrans del) { return ((del.TypeId == objAbs.TypeId) && (del.TransRowId == objLType.RowId)); });
if (oLSV.Count > 0)
{
foreach (BillLSVTrans objLSV in oLSV)
{
sSql = "Insert into BillLabourTypeWBSLSTrans(BillLabourTypeTransId,LSId,Qty) " +
"Values(" + iTransId + "," + objLSV.LSId + ", " + objLSV.BillQty + ")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery();
Command.Dispose();
}
}
}
}
}
}
}
}
else if (argInternal == true && objAbs.BFormatType.ToString() == "B")
{
List<BillLabourTypeTransBO> oSLt = argBLbrType.FindAll(delegate(BillLabourTypeTransBO del) { return ((del.TypeId == objAbs.TypeId)); });
if (oSLt.Count > 0)
{
foreach (BillLabourTypeTransBO objLType in oSLt)
{
sSql = "Insert BillMLabourTrans(BillRegisterId,LabourId,TypeId,Qty,Rate,Amount) " +
"Values(" + iBillRegId + "," + objLType.LabourTypeID + "," + objLType.TypeId + ","+objLType.Qty+","+objLType.Rate+","+objLType.Amount+")";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery(); Command.Dispose(); Command.Dispose();
}
}
}
}
}
#endregion
//if (argBReg.BillType == "I")
//{
// sSql = "Insert into BillItemLabourTrans(BillTransId,LabourTypeId,Qty,Rate,Amount) " +
// "Select BillTransId,LabourTypeId,Sum(Qty) Qty,Case When Sum(isnull(Qty,0)) <>0 then Sum(Amount)/Sum(Qty) else 0 end Rate,Sum(Amount) Amount from BillWBSLabourTrans " +
// "Where BillTransId in (Select BillTransId from BillTrans Where BillRegisterId = " + iBillRegId + ") Group by BillTransId,LabourTypeId";
// Command = new SqlCommand(sSql, conn, tran); Command.ExecuteNonQuery(); Command.Dispose();
//}
//else
//{
// sSql = "Insert into BillItemLabourTrans(BillTransId,LabourTypeId,Qty,Rate,Amount) " +
// "Select BillTransId,LabourTypeId,Sum(Qty) Qty,Case When Sum(isnull(Qty,0)) <>0 then Sum(Amount)/Sum(Qty) else 0 end Rate,Sum(Amount) Amount from BillIOWLabourTrans " +
// "Where BillTransId in (Select BillTransId from BillTrans Where BillRegisterId = " + iBillRegId + ") Group by BillTransId,LabourTypeId";
// Command = new SqlCommand(sSql, conn, tran); Command.ExecuteNonQuery(); Command.Dispose();
//}
//sSql = "Insert into BillLabourTrans(BillREgisterId,LabourTypeId,Qty,Rate,Amount) " +
// "Select " + iBillRegId + ",LabourTypeId,Sum(Qty) Qty,Case When Sum(isnull(Qty,0)) <>0 then Sum(Amount)/Sum(Qty) else 0 end Rate,Sum(Amount) Amount from BillItemLabourTrans " +
// "Where BillTransId in (Select BillTransId from BillTrans Where BillRegisterId = " + iBillRegId + ") Group by LabourTypeId";
//Command = new SqlCommand(sSql, conn, tran); Command.ExecuteNonQuery(); Command.Dispose();
CommFun.ActualCompUpdate(iBillRegId, argCCId, "B", true, argrevId, argBReg.ContractorID,argDBName, conn, tran,argBReg.CostOption);
//BillQty Update in WOTrans Table
if (argBReg.WORegisterID != 0)
CommFun.RefreshWOBilledQty(argBReg.WORegisterID, conn, tran);
sSql = "SELECT A.TypeId,A.Amount,B.Type from BillFormatTrans A " +
"INNER JOIN BillFormatMaster B ON A.TypeId=B.TypeId " +
"WHERE A.BillRegisterId=" + iBillRegId + " and A.AccountId <>0 and A.Amount <>0 AND B.Header=0";
Command = new SqlCommand(sSql, conn, tran);
SqlDataReader dsr = Command.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dsr);
Command.Dispose();
dsr.Close();
DataTable dtt;
decimal dFAmt = 0;
decimal dIOWAmt = 0;
bool bAmtOK = true;
int iTypeId=0;
for (int j = 0; j < dt.Rows.Count; j++)
{
dFAmt = Math.Round(Convert.ToDecimal(CommFun.IsNullCheck(dt.Rows[j]["Amount"], CommFun.datatypes.vartypenumeric)), 0);
iTypeId=Convert.ToInt32(CommFun.IsNullCheck(dt.Rows[j]["TypeId"], CommFun.datatypes.vartypenumeric));
dIOWAmt = 0;
// Refer T-Tracker Point No 1786, Function to post subledger details-Workgroup wise posting to FA
if (dt.Rows[j]["Type"].ToString() == "I")
{
if (argBReg.BillType == "I")
{
sSql = "Select Sum(A.Amount) Amt From BillTrans A " +
"Where A.BillRegisterId=" + iBillRegId + " and A.TypeId = " + iTypeId;
}
else
{
sSql = "Select Sum(A.Qty*B.Rate) Amt From BillIOWTrans A " +
"Inner Join BillTrans B on A.BillTransId=B.BillTransId " +
"Where B.BillRegisterId=" + iBillRegId + " and B.TypeId = " + iTypeId;
}
Command = new SqlCommand(sSql, conn, tran);
dsr = Command.ExecuteReader();
dtt = new DataTable();
dtt.Load(dsr);
Command.Dispose();
dsr.Close();
if (dtt.Rows.Count > 0) { dIOWAmt = Math.Round(Convert.ToDecimal(CommFun.IsNullCheck(dtt.Rows[0]["Amt"], CommFun.datatypes.vartypenumeric)), 0); }
dtt.Dispose();
if (dIOWAmt != 0)
{
if (dFAmt != dIOWAmt)
{
bAmtOK = false;
break;
}
}
}
}
if (bAmtOK == false)
{
tran.Rollback();
MessageBox.Show("Amount Difference, Do Not Update");
bUpdate = false;
}
else
{
tran.Commit();
bUpdate = true;
}
}
catch (SqlException ex)
{
tran.Rollback();
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
conn.Close();
}
return bUpdate;
}
