QR-Code DNCN/SR
Doubts
Doubts
- rate not asking to user taking from doc table in existing we are asking
Dncn1Index
- Need to hide the existing Create button if QR Enable sysparameter is ON and IsUpdateStock == “Y”
- Need to add Import button if QR Enable sysparameter is ON and IsUpdateStock == “Y”
Dncn1Details
- Need to hide the existing Create button if QR Enable sysparameter is ON and IsUpdateStock == “Y”
- Need to add Import button if QR Enable sysparameter is ON and IsUpdateStock == “Y”
Dncn1Import New Page
- It will have Dncn No, Dncn Date , Party Name, Dncn Type as readonly
- It will have Import, Download Model and Pending Order Report Button
- Use of every feature is as follow
Import - For Importing the Model
Download Model - It will download the model with 3 columns IssueQty, docs1id, QrDescription
Pending Report
DncnController Changes
- Need to add download model Dncn1ImportDownloadModel
[HttpGet("{dbname}")]
public ActionResult Dncn1ImportDownloadModel(string dbname, string dncnType)
{
Random rnd = new();
var randomnumber = rnd.Next(1000000000).ToString().Trim();
var randomfilename = $"{randomnumber}.xlsx";
// for generating system generated excel
string pathdirname1 = $"\\MFGReports\\Reports\\{dbname}";
var pathname1 = $"{pathdirname1}\\{randomfilename}";
var pathfilename1 = Path.GetFullPath(pathname1);
var idName = dncnType = "S" ? "Invoice1Id" : "Bill1Id";
using var WorkbookXL = new XLWorkbook();
var worksheet = WorkbookXL.Worksheets.Add("Import_Invoice");
worksheet.Cell(1, 1).Value = "Issue Qty";
worksheet.Cell(1, 2).Value = $"{idName};
worksheet.Cell(1, 3).Value = "QR Description";
worksheet.Range(1, 1, 1, 3).Style.Fill.BackgroundColor = XLColor.LightSteelBlue;
worksheet.Range(1, 1, 1, 3).Style.Font.Bold = true;
worksheet.Range(1, 1, 1, 3).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Range(1, 1, 1, 3).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Range(1, 1, 1, 3).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Range(1, 1, 1, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column(1).Style.NumberFormat.Format = "##0.000;[Red](##0.000)";
worksheet.Columns().AdjustToContents(1);
worksheet.Column(3).Width = 40;
worksheet.Column(3).Style.Alignment.WrapText = true;
var worksheet1 = WorkbookXL.Worksheets.Add("Instructions");
worksheet1.SheetView.FreezeRows(1);
worksheet1.Cell(1, 1).Value = "Column";
worksheet1.Cell(1, 2).Value = "Description";
worksheet1.Cell(2, 1).Value = "Issue Qty";
worksheet1.Cell(2, 2).Value = "Issue Qty must be a numeric value. If you want to issue the full quantity, leave it blank.";
worksheet1.Cell(3, 1).Value = $"{idName}";
worksheet1.Cell(3, 2).Value = $"{idName} must be a numeric value";
worksheet1.Cell(4, 1).Value = "QR Description";
worksheet1.Cell(4, 2).Value = "QR Description must be filled and cannot exceed 500 characters. This contains the scanned QR code data.";
worksheet1.Range(1, 1, 1, 2).Style.Fill.BackgroundColor = XLColor.LightSteelBlue;
worksheet1.Range(1, 1, 1, 2).Style.Font.Bold = true;
worksheet1.Range(1, 1, 1, 2).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet1.Range(1, 1, 1, 2).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet1.Range(1, 1, 1, 2).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet1.Range(1, 1, 1, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet1.Columns().AdjustToContents(1);
worksheet1.Column(2).Width = 40;
worksheet1.Column(2).Style.Alignment.WrapText = true;
WorkbookXL.SaveAs(pathfilename1);
return Ok(randomfilename);
}
- Need to add PendingDocReport
[HttpGet("{dbname},{mainId}")]
public async Task<ActionResult> PendingDocReport(string dbname, int mainId)
{
var companyData = await _IUtilityMethodsRepository.GetCompanyMstInfo(dbname);
Random rnd = new();
var randomnumber = rnd.Next(1000000000).ToString().Trim();
var randomfilename = $"{randomnumber}.xlsx";
string pathdirname1 = $"\\MFGReports\\Reports\\{dbname}";
if (!Directory.Exists(pathdirname1))
{
Directory.CreateDirectory(pathdirname1);
}
var pathname1 = $"{pathdirname1}\\{randomfilename}";
var pathfilename1 = Path.GetFullPath(pathname1);
using var WorkbookXL = new XLWorkbook();
var worksheet = WorkbookXL.Worksheets.Add("Pending_Doc_Report");
var data = _IDncnRepository.GetDncnInfo(mainId, dbname);
var idName = data.DncnType[..1] = "S" ? "Invoice1Id" : "Bill1Id";
worksheet.Cell(1, 1).Value = $"Company Name : {companyData.CompanyName}";
worksheet.Cell(2, 1).Value = "Report Name : Pending Document Report";
worksheet.Cell(3, 1).Value = $"Report Date : {DateTime.Now.Date:dd-MMM-yy}";
worksheet.Cell(4, 1).Value = $"Order no : {orderSearch}";
worksheet.Range(1, 1, 4, 1).Style.Font.SetBold();
// Column headers
worksheet.Cell(5, 1).Value = "Item Short Name";
worksheet.Cell(5, 2).Value = $"{idName}";
worksheet.Cell(5, 3).Value = "Pending Qty";
worksheet.Range(5, 1, 5, 3).Style.Fill.BackgroundColor = XLColor.LightSteelBlue;
worksheet.Range(5, 1, 5, 3).Style.Font.SetBold();
worksheet.Range(5, 1, 5, 3).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Range(5, 1, 5, 3).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Range(5, 1, 5, 3).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Range(5, 1, 5, 3).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Range(5, 1, 5, 3).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
// Get data using dncn1CreateIndex
var data = await _IDncnRepository.Dncn1CreateIndex(dbname, data.YearRefNo, data.DncnType[..1]);
int row = 6;
foreach (var item in data)
{
worksheet.Cell(row, 1).Value = item.ItemShortName;
worksheet.Cell(row, 2).Value = item.doc1Id;
worksheet.Cell(row, 3).Value = item.PendingQty;
row++;
}
worksheet.Column(3).Style.NumberFormat.Format = "##0.000;";
worksheet.Columns().AdjustToContents();
WorkbookXL.SaveAs(pathfilename1);
return Ok(randomfilename);
}
- Need to add Dncn1Import
[HttpPost("{dbname},{xlfile},{yearlabel},{userName},{orderSearch}")]
public async Task<ActionResult> Invoice1Import(string dbname, string xlfile, string yearlabel, string userName, int mainId)
{
var xlfileresult = " ";
Random rnd = new();
var randomnumber = rnd.Next(1000000000).ToString().Trim();
var randomfilename = $"{randomnumber}.xlsx";
// for generating system generated excel
string pathdirname1 = $"\\MFGReports\\Reports\\{dbname}";
var pathname1 = $"{pathdirname1}\\{randomfilename}";
var pathfilename1 = Path.GetFullPath(pathname1);
// for reading user excel data
var pathname2 = $"{pathdirname1}\\{xlfile}";
var pathfilename2 = Path.GetFullPath(pathname2);
using var WorkbookXL = new XLWorkbook(pathfilename2);
var worksheet = WorkbookXL.Worksheets.Worksheet(1);
var rowcount = worksheet.LastRowUsed().RowNumber();
var columncount = worksheet.LastColumnUsed().ColumnNumber();
var firstcolname = worksheet.Cell(1, 1).GetString().ToLower();
var lastcolname = worksheet.Cell(1, 3).GetString().ToLower();
var mainData = _IInvoiceRepository.GetDncnInfo(mainId, dbname);
// 3 define total no. of columns hardcoded
if (columncount != 3 || firstcolname != "issue qty" || lastcolname != "qr description")
{
xlfileresult = "1"; //file is not in correct format
}
else if (rowcount == 1)
{
xlfileresult = "2"; // file does not contain any line
}
else
{
int row = 2; // skip 1st header
var error = " ";
List<InvoiceLine> listdata = new();
while (row <= rowcount)
{
// hardcoded columns are passed
var issueQtyCol = worksheet.Cell(row, 1);
var doc1IdCol = worksheet.Cell(row, 2);
var qrDescCol = worksheet.Cell(row, 3);
if(issueQtyCol.GetString() != "D")
{
if (qrDescCol.GetString().Length > 500)
{
error = "Z1";
}
if (!string.IsNullOrEmpty(issueQtyCol) && (Convert.ToString(issueQtyCol.DataType) != "Number"))
{
error = $"Z2 {error}";
}
if (Convert.ToString(doc1IdCol.DataType) != "Number")
{
error = $"Z3 {error}";
}
}
}
if (error != " ")
{
// define last column for error code
worksheet.Cell(row, 4).Value = error;
error = "";
}
else
{
decimal qty = 0;
if (decimal.TryParse(issueQtyStr, out var parsedQty))
{
qty = parsedQty;
}
listdata.Add(new InvoiceLine
{
QrDescription = qrDesCol.GetString(),
Qty = qty,
doc1Id = (int)doc1Id.GetDouble()
});
listdata = listdata.Where(x => x.Qty.ToString() != "D").ToList();
}
row++;
}
worksheet.SheetView.FreezeRows(1);
worksheet.Cell(1, 4).Value = "Error Code";
worksheet.Cell(row + 1, 1).Value = "Import could not succeed";
worksheet.Range(1, 1, 1, 4).Style.Fill.BackgroundColor = XLColor.LightSteelBlue;
worksheet.Range(1, 1, 1, 4).Style.Font.Bold = true;
worksheet.Range(6, 1, 6, 1).Style.Fill.BackgroundColor = XLColor.Yellow;
worksheet.Range(1, 1, 1, 4).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Range(1, 1, 1, 4).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Range(1, 1, 1, 4).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Range(1, 1, 1, 4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column(1).Style.NumberFormat.Format = "##0.000";
worksheet.Columns().AdjustToContents(1);
worksheet.Column(3).Width = 40;
worksheet.Column(3).Style.Alignment.WrapText = true;
// ---------------------------------------------------------------------------
var worksheet1 = WorkbookXL.Worksheets.Add("Error_Code");
worksheet1.Cell(1, 1).Value = "Error Code";
worksheet1.Cell(1, 2).Value = "Description";
worksheet1.Cell(2, 1).Value = "Z1";
worksheet1.Cell(2, 2).Value = "QR Description cannot exceed 500 characters.";
worksheet1.Cell(3, 1).Value = "Z2";
worksheet1.Cell(3, 2).Value = "Issue Qty must be in proper number format";
worksheet1.Cell(4, 1).Value = "Z3";
worksheet1.Cell(4, 2).Value = "doc1Id must be a number";
worksheet1.Range(1, 1, 1, 2).Style.Fill.BackgroundColor = XLColor.LightSteelBlue;
worksheet1.Range(1, 1, 1, 2).Style.Font.Bold = true;
worksheet1.Range(1, 1, 1, 2).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet1.Range(1, 1, 1, 2).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet1.Range(1, 1, 1, 2).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet1.Range(1, 1, 1, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet1.Columns().AdjustToContents(1);
worksheet1.Column(2).Width = 40;
worksheet1.Column(2).Style.Alignment.WrapText = true;
if (error == " ")
{
var companydata = await _IUtilityMethodsRepository.GetCompanyMstInfo(dbname);
var xldata = await _IInvoiceRepository.Dncn1Import(listdata, dbname, userName, yearlabel, mainId);
using var WorkbookXL1 = new XLWorkbook();
var worksheet2 = WorkbookXL1.Worksheets.Add("Dncn_Import");
worksheet2.SheetView.FreezeRows(7);
worksheet2.Cell(1, 1).Value = $"Company Name : {companydata.CompanyName}";
worksheet2.Cell(2, 1).Value = "Report Name : Dncn QR Import";
worksheet2.Cell(3, 1).Value = $"Report Date : {DateTime.Now.Date:dd-MMM-yy}";
worksheet2.Cell(4, 1).Value = $"Dncn No : {mainData.DncnNo}";
worksheet2.Cell(5, 1).Value = xldata.Item2;
worksheet2.Cell(6, 1).Value = "Error Code";
worksheet2.Cell(6, 2).Value = "Description";
worksheet2.Cell(6, 3).Value = "Qty";
worksheet2.Range(6, 1, 6, 3).Style.Fill.BackgroundColor = XLColor.LightSteelBlue;
worksheet2.Range(1, 1, 6, 3).Style.Font.Bold = true;
worksheet2.Range(5, 1, 5, 2).Style.Fill.BackgroundColor = XLColor.Yellow;
worksheet2.Range(6, 1, 6, 3).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet2.Range(6, 1, 6, 3).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet2.Range(6, 1, 6, 3).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet2.Range(6, 1, 6, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
int i = 6;
foreach (var row1 in xldata.Item1)
{
i++;
worksheet2.Cell(i, 1).Value = row1.ErrorCode;
worksheet2.Cell(i, 2).Value = row1.QrDescription;
worksheet2.Cell(i, 3).Value = row1.Qty;
}
worksheet2.Column(3).Style.NumberFormat.Format = "##0.000";
worksheet2.Columns().AdjustToContents(6);
int[] columnsToFormat = [2];
foreach (int columnNumber in columnsToFormat)
{
worksheet2.Column(columnNumber).Width = 40;
worksheet2.Column(columnNumber).Style.Alignment.WrapText = true;
}
// -------------------------------------------------------------------------------
var worksheet3 = WorkbookXL1.Worksheets.Add("Error_Code");
worksheet3.Cell(1, 1).Value = "Error Code";
worksheet3.Cell(1, 2).Value = "Description";
worksheet3.Cell(2, 1).Value = "A";
worksheet3.Cell(2, 2).Value = "Only GRN, JobWork, Production, and Stock Trn Id can be imported";
worksheet3.Cell(3, 1).Value = "B";
worksheet3.Cell(3, 2).Value = "Invalid Trn Id / Sub Trn Id";
worksheet3.Cell(4, 1).Value = "C";
worksheet3.Cell(4, 2).Value = "Balance is 0 in subtrn for this TrnId";
worksheet3.Cell(5, 1).Value = "D";
worksheet3.Cell(5, 2).Value = "Entered Qty exceeds available balance";
worksheet3.Cell(6, 1).Value = "E";
worksheet3.Cell(6, 2).Value = "Issue Qty must be between 0.001 to 999999.999";
worksheet3.Cell(7, 1).Value = "F";
worksheet3.Cell(7, 2).Value = "QR Item and Doc Item are mismatching";
worksheet3.Cell(8, 1).Value = "G";
worksheet3.Cell(8, 2).Value = " Quantity may not be more than Billed / Invoiced Qty";
worksheet3.Cell(9, 1).Value = "H";
worksheet3.Cell(9, 2).Value = "Qty * rate should not exceed 999999999.99 ";
worksheet3.Cell(10, 1).Value = "I";
worksheet3.Cell(10, 2).Value = "Trnid and Subtrnid must be unique throghout the excel";
worksheet3.Cell(11, 1).Value = "J";
worksheet3.Cell(11, 2).Value = "Doc Id doesn't exists in Bill";
worksheet3.Cell(12, 1).Value = "K";
worksheet3.Cell(12, 2).Value = "Doc Id doesn't exists in Invoice";
worksheet3.Cell(13, 1).Value = "L";
worksheet3.Cell(13, 2).Value = "Party, Branch and Unit of the scanned TrnId and DNCN is not matching";
worksheet3.Range(1, 1, 1, 2).Style.Fill.BackgroundColor = XLColor.LightSteelBlue;
worksheet3.Range(1, 1, 1, 2).Style.Font.Bold = true;
worksheet3.Range(1, 1, 1, 2).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet3.Range(1, 1, 1, 2).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet3.Range(1, 1, 1, 2).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet3.Range(1, 1, 1, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet3.Columns().AdjustToContents(1);
worksheet3.Column(2).Width = 40;
worksheet3.Column(2).Style.Alignment.WrapText = true;
WorkbookXL1.SaveAs(pathfilename1);
xlfileresult = randomfilename;
}
else
{ //data type error sheet
WorkbookXL.SaveAs(pathfilename1);
xlfileresult = randomfilename;
}
}
System.IO.File.Delete(pathfilename2); // deleting file in specific path after reading
var result = new InvoiceLine
{
XlFileName = xlfileresult
};
return Ok(result);
Changes in DncnRepository
- Need to add Dncn1Import
public async Task<Tuple<List<Dncn1Import>, string>> Dncn1ImportXl(List<Dncn1Import> listdata, string dbname, string userName, string yearLabel, Dncn1Import _Dncn1Import)
{
var tempcreation = @"CREATE TABLE #dncn
(
id INT PRIMARY KEY IDENTITY(1,1),
QrDescription NVARCHAR(500) COLLATE DATABASE_DEFAULT,
qty NUMERIC(13,3),
value NUMERIC(13,2),
trnid NVARCHAR(7) COLLATE DATABASE_DEFAULT,
subtrnid NVARCHAR(3) COLLATE DATABASE_DEFAULT,
itemid NVARCHAR(6) COLLATE DATABASE_DEFAULT,
errorcode NVARCHAR(20) COLLATE DATABASE_DEFAULT,
stage NVARCHAR(2) COLLATE DATABASE_DEFAULT,
gstcode NVARCHAR(2) COLLATE DATABASE_DEFAULT,
storecode NVARCHAR(2) COLLATE DATABASE_DEFAULT,
brandid NVARCHAR(3) COLLATE DATABASE_DEFAULT,
partyid NVARCHAR(6) COLLATE DATABASE_DEFAULT,
branchid NVARCHAR(3) COLLATE DATABASE_DEFAULT,
unitcode NVARCHAR(2) COLLATE DATABASE_DEFAULT,
rate NUMERIC(12,4),
docId INT
)";
var inserttemp = @"INSERT INTO #dncn(QrDescription, qty, trnid, subtrnid,docid)
VALUES(@QrDescription, @qty, @trnid, @subtrnid, @docid)";
var updateQty = @"UPDATE #dncn SET qty = Q.balanceqty
FROM QrSubTrn Q
WHERE #dncn.trnid = Q.trnid AND #dncn.subtrnid = Q.subtrnid AND COALESCE(#dncn.qty, 0) = 0";
var updateItemDetails = @"WITH A(trnid , itemid, stage, storecode, brandid, partyid, unitcode, branchid) AS
(
SELECT Z.TrnId, G1.ItemId,
I1.stage AS stagecode, storecode, I1.brandid, G.partyid, G.unitcode, G.branchid
FROM #dncn Z
LEFT JOIN Grn1 G1 ON Z.TrnId = G1.TrnId
LEFT JOIN indent1 I1 ON G1.indent1id = I1.id
LEFT JOIN Grn G ON G1.yeargrnno = G.YearGrnNo
WHERE LEFT(Z.trnid,1) = 'G'
UNION ALL
SELECT Z.TrnId, J1.ItemId,
J1.stage AS stagecode, storecode, J1.brandid, J.partyid, J.unitcode, J.branchid
FROM #dncn Z
LEFT JOIN Jobwork1 J1 ON Z.TrnId = J1.TrnId
LEFT JOIN Jobwork J ON J1.yearjobno = J.YearJobNo
WHERE LEFT(Z.trnid,1) = 'J'
UNION ALL
SELECT Z.TrnId, M1.ItemId,
M1.stage AS stagecode, storecode, M1.brandid, '' AS partyid, '' AS unitcode, '' AS branchid
FROM #dncn Z
LEFT JOIN Mirs1 M1 ON Z.TrnId = M1.TrnId
WHERE LEFT(Z.trnid,1) = 'M'
UNION ALL
SELECT Z.TrnId, S.ItemId,
S.stagecode, S.storecode, S.brandid, '' AS partyid, '' AS unitcode, '' AS branchid
FROM #dncn Z
LEFT JOIN QrStock S ON Z.TrnId = S.TrnId
WHERE LEFT(Z.trnid,1) = 'S'
)
UPDATE #dncn SET itemid = A.itemid, stage = A.stage, storecode = A.storecode,
brandid = A.brandid, partyid = A.partyid, unitcode = A.unitcode, branchid = A.branchid
FROM A
WHERE #dncn.trnid = A.trnid";
var updateRateGstCode = @"WITH A (rate,gstcode, docid) AS
(
SELECT rate, gstcode, D.docid
FROM #dncn D
LEFT JOIN Invoice2 I2 ON D.docid = I2.id
WHERE @dncntype = 'S'
UNION ALL
SELECT rate, gstcode, id
FROM #dncn D
LEFT JOIN Bill2 B2 ON D.docid = B2.id
WHERE @dncntype = 'P'
)
UPDATE #dncn SET rate = A.rate, gstcode = A.gstcode
FROM A
WHERE #dncn.docid = A.docid";
var calculateValue = @"UPDATE #dncn SET value = CAST(ROUND(qty * rate,2) AS NUMERIC(12,2))";
var errorcodeA = @"UPDATE #dncn SET errorcode = COALESCE(RTRIM(errorcode),'')+ ' A'
WHERE LEFT(trnid, 1) NOT IN ('G','J','M','S')";
var errorcodeB = @"UPDATE #dncn SET errorcode = COALESCE(RTRIM(ERRORCODE), '') + ' B'
WHERE NOT EXISTS (SELECT trnid + subtrnid FROM QrSubTrn WHERE #dncn.trnid + #dncn.subtrnid = QrSubTrn.trnid + QrSubTrn.subtrnid)";
var errorcodeC = @"UPDATE #dncn
SET errorcode = COALESCE(RTRIM(errorcode), '') + ' C'
FROM QrSubTrn Q
WHERE #dncn.trnid = Q.trnid
AND #dncn.subtrnid = Q.subtrnid
AND Q.balanceqty = 0";
var errorcodeD = @"UPDATE #dncn
SET errorcode = COALESCE(RTRIM(errorcode), '') + ' D'
FROM QrSubTrn Q
WHERE #dncn.trnid = Q.trnid
AND #dncn.subtrnid = Q.subtrnid
AND #dncn.qty > Q.balanceqty";
var errorcodeE = @" WITH A (docid, qty) AS
(
SELECT docid, SUM(qty) AS qty
FROM #dncn
GROUP BY docid
)UPDATE #dncn SET errorcode = COALESCE(RTRIM(errorcode), '') + ' E'
FROM A
WHERE A.qty NOT BETWEEN 0.001 AND 999999.999 AND #dncn.docid = A.docid";
var errorcodeF = @"WITH A(docid, tempItemid, docitemid, tempstore, docstore, tempstage, docstage, tempbrandid, docbrandid) AS
(
SELECT D.docid, D.itemid, I2.itemid, D.storecode, I2.storecode, D.stage, I2.stage, D.brandid, O1.brandid
FROM #dncn D
LEFT JOIN Invoice2 I2 ON D.docid = I2.id
LEFT JOIN orders1 O1 ON I2.orders1id = O1.id
WHERE @dncntype = 'S'
UNION ALL
SELECT D.docid, D.itemid, B2.itemid, D.storecode, G1.storecode, D.stage, B2.stage, D.brandid, I1.brandid
FROM #dncn D
LEFT JOIN Bill2 B2 ON D.docid = B2.id
LEFT JOIN GRN1 G1 ON B2.grn1id = G1.id
LEFT JOIN indent1 I1 ON G1.indent1id = I1.id
WHERE @dncntype = 'P'
)
UPDATE #dncn SET errorcode = COALESCE(RTRIM(ERRORCODE), '') + ' F'
FROM A
WHERE #dncn.docid = A.docid
AND (tempItemid != docitemid OR tempstore != docstore OR tempstage != docstage AND tempbrandid != docbrandid)";
var errorcodeG = @"WITH A(docid, tempQty) AS
(
SELECT D.docid, SUM(D.qty) AS tempqty
FROM #dncn D
WHERE @dncntype = 'S'
GROUP BY docId
UNION ALL
SELECT D.docid, SUM(D.qty) AS tempqty
FROM #dncn D
WHERE @dncntype = 'P'
GROUP BY docId
),
B(docid, tempQty, docQty) AS
(
SELECT A.docid, A.tempQty, I2.qty
FROM A
LEFT JOIN Invoice2 I2 ON A.docid = I2.id
WHERE @dncntype = 'S'
UNION ALL
SELECT A.docid, A.tempQty, B2.qty
FROM A
LEFT JOIN Bill2 B2 ON A.docid = B2.id
WHERE @dncntype = 'P'
)
UPDATE #dncn SET errorcode = COALESCE(RTRIM(ERRORCODE), '') + ' G'
FROM B
WHERE #dncn.docid = B.docid AND tempqty > docqty";
var errorcodeH = @" WITH A (docid, value) AS
(
SELECT docid, SUM(value) AS value
FROM #dncn
GROUP BY docid
)UPDATE #dncn SET errorcode = COALESCE(RTRIM(errorcode), '') + ' H'
FROM A
WHERE A.value NOT BETWEEN 0.01 AND 999999999.99 AND #dncn.docid = A.docid";
var errorcodeI = @"WITH A(trnid,subtrnid) AS
(
SELECT trnid,subtrnid
FROM #dncn
GROUP BY trnid,subtrnid
HAVING COUNT(DISTINCT trnid,Subtrnid) > 1
)
UPDATE #dncn
SET errorcode = COALESCE(RTRIM(errorcode), '') + ' I'
FROM A
WHERE #dncn.trnid = A.trnid AND #dncn.subtrnid = A.subtrnid";
var errorcodeJ = @"UPDATE #dncn SET errorcode = COALESCE(RTRIM(ERRORCODE), '') + ' J'
WHERE NOT EXISTS (SELECT id FROM bill2 WHERE #dncn.docid = bill2.id AND @dncntype = 'P')";
var errorcodeK = @"UPDATE #dncn SET errorcode = COALESCE(RTRIM(ERRORCODE), '') + ' K'
WHERE NOT EXISTS (SELECT id FROM invoice2 WHERE #dncn.docid = invoice2.id AND @dncntype = 'S')";
var errorcodeL = @"UPDATE #dncn SET errorcode = COALESCE(RTRIM(ERRORCODE), '') + ' L'
WHERE (partyid != @partyid OR branchid != @branchid OR unitcode != @unitcode) AND LEFT(trnid,1) IN ('G', 'J')";
var cnterror = @"SELECT COUNT(Errorcode) AS CNT FROM #dncn
WHERE COALESCE(ERRORCODE,'') != ''";
var data = @"SELECT JW.QrDescription, JW.qty,JW.value,
COALESCE(JW.errorcode,'') AS errorcode
FROM #dncn JW
ORDER BY JW.id ASC";
// values for dncn
// 1. ItemId = of trnid and of document must be same
// 2. qty = from user as issueqty
// 3. rate = from Bill/Invoice as per the dncntype
// 4. amount = qty * rate
// 5. ismanualvalue = 'N' --> alway N because we are calculating amount
// 6. gstcode = from Bill/Invoice as per the dncntype
// 8. referid = id of Bill/Invoice
// 9. brandid = of trnid and of document must be same
// 10. ItemState = G
// 11. store = of trnid and of document must be same
// 12. taxableamount = same as value
// 13. stage = trnid and of document must be same
// 14. party, branch, units of trnids and respective documents should be same
var dncn1insert = @"WITH A (qty, itemid, stage, storecode,brandid, rate, docid) AS
(
SELECT SUM(qty) AS qty, itemid, stage, storecode,brandid, rate, docid
FROM #dncn
GROUP BY itemid, stage, storecode,brandid, rate, docid
)
INSERT INTO DebitCredit1
(
itemid,qty,newrate,stage,value,
ismanualvalue,yeardncnno,gstcode,referid,brandid,Itemstate,Storecode,taxablevalue
)
SELECT @dncnno, itemid, qty, rate, stage, value, 'N',
@yeardncnno,gstcode, referid, brandid, 'G' AS Itemstate, storecode, value
FROM A";
var scannedsubtrninsert = @"WITH A (trnid, subtrnid, qty, yeardocno, dncntype, docid, isrolledback) AS
(
SELECT D.trnid, D.subtrnid, D.qty, @yeardncnno AS yeardocno, @dncntype AS dncntype, DC1.id AS docid, 'N' AS isrolledback
FROM #dncn D
LEFT JOIN DebitCredit1 DC1 ON DC1.referid = D.docid
)
INSERT INTO ScannedSubTrn (trnid, subtrnid, qty, yeardocno, dncntype, docid, isrolledback)
SELECT trnid, subtrnid, qty, @yeardncnno AS yeardocno, @dncntype AS dncntype, docid, isrolledback
FROM A";
var updateQrSubTrnQty = @"UPDATE QrSubTrn SET BalanceQty = BalanceQty - A.qty FROM #dncn A
WHERE QrSubTrn.TrnId = A.TrnId AND QrSubTrn.SubTrnId = A.SubTrnId";
using var connection = _DapperContext.SetClientConnection(dbname);
connection.Open();
connection.Execute(tempcreation);
connection.Execute(inserttemp,
listdata.Select(dataLine => new
{
dataLine.QrDescription,
SubTrnId = dataLine.QrDescription.Trim().Substring(8, 3),
TrnId = dataLine.QrDescription.Trim()[..7],
qty = dataLine.Qty
})
);
connection.Execute(updateQty);
connection.Execute(updateItemDetails);
connection.Execute(updateRateAndDocId, new { docid = _Dncn1Import.DocId, dncntype = _Dncn1Import.DncnType });
connection.Execute(calculateValue);
connection.Execute(errorcodeA);
connection.Execute(errorcodeB);
connection.Execute(errorcodeC);
connection.Execute(errorcodeD);
connection.Execute(errorcodeE);
connection.Execute(errorcodeF);
connection.Execute(errorcodeG, new { docid = _Dncn1Import.DocId, dncntype = _Dncn1Import.DncnType });
connection.Execute(errorcodeH);
connection.Execute(errorcodeI);
connection.Execute(errorcodeJ);
connection.Execute(errorcodeK, new { docid = _Dncn1Import.DocId, dncntype = _Dncn1Import.DncnType, yeardncnno = _Dncn1Import.YearDncnNo });
var errorcount = connection.QuerySingleOrDefault<int>(cnterror);
string? msg;
msg = "Import could not succeed because of errors in input";
if (errorcount == 0)
{
connection.Execute(dncn1insert,
new
{
dncnno = _Dncn1Import.DncnNo,
yeardncnno = _Dncn1Import.YearDncnNo,
dncntype = _Dncn1Import.DncnType
});
connection.Execute(scannedsubtrninsert, new
{
yeardncnno = _Dncn1Import.YearDncnNo,
dncntype = _Dncn1Import.DncnType
});
connection.Execute(updateQrSubTrnQty);
// Assuming utility method exists
// _IUtilityMethodsRepository.InsertMFGLog(dbname, userName, _Dncn1Import.DncnNo, "Dncn", "Import", yearLabel, "");
msg = "DN/CN import succeeded.";
}
var finaldata = await connection.QueryAsync<Dncn1Import>(data);
var data1 = finaldata.ToList();
return new Tuple<List<Dncn1Import>, string>(data1, msg);
}