public IHttpActionResult shitTable(String oldTableId, String newTableId, String shifType)
{
Boolean valid = false;
var oldTableKOTs = new List <PrintEntity>();
SqlConnection connection = new SqlConnection(connectionString);
//get all active KOTIDs
String query = "select KOTNO from TrnHdrKOT where TblID = '" + oldTableId + "' and Billed ='N' order by KOTNO asc";
try
{
SqlCommand cmd = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var kotData = new PrintEntity();
kotData.kotNumber = reader.GetInt32(0);
oldTableKOTs.Add(kotData);
}
valid = true;
}
catch (Exception ex)
{
valid = false;
}
finally
{
connection.Close();
}
// Update new table Id in TrnHdrKot table
for (var i = 0; i < oldTableKOTs.ToArray().Length; i++)
{
String query2 = "update TrnHdrKOT set TblID = " + newTableId + " where KOTNO= '" + oldTableKOTs[i].kotNumber + "' ";
try
{
SqlCommand cmd = new SqlCommand(query2, connection);
connection.Open();
cmd.ExecuteNonQuery();
valid = true;
}
catch (Exception ex)
{
valid = false;
}
finally
{
connection.Close();
}
var itemList = new List <ItemRate>();
// get ItemId and PCATID for updating item rate in TrnDtlKOT
String query3 = " select d.ItemID,d.KOTQty, i.PCatID from TrnDtlKOT as d, MstItemRate as i where d.KOTNO = '" + oldTableKOTs[i].kotNumber + "' and d.ItemID = i.ItemID";
try
{
SqlCommand cmd = new SqlCommand(query3, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var itemData = new ItemRate();
itemData.itemId = reader.GetInt32(0);
itemData.KOTQuantity = reader.GetDecimal(1);
itemData.PCATID = reader.GetInt32(2);
itemList.Add(itemData);
}
valid = true;
}
catch (Exception ex)
{
valid = false;
}
finally
{
connection.Close();
}
for (var j = 0; j < itemList.Count; j++)
{
// update item rates in TrnDltKOT table using KOTNO
String query4 = " update TrnDtlKOT set KOTRate = (select tr.ItemRate from MstItemRate as tr where tr.ItemID = '" + itemList[i].itemId + "' and tr.PCatID ='" + itemList[i].PCATID + "'), KOTAmt = " + itemList[i].KOTQuantity + " * (select tr.ItemRate from MstItemRate as tr where tr.ItemID = '" + itemList[i].itemId + "' and tr.PCatID ='" + itemList[i].PCATID + "') where KOTNO = '" + oldTableKOTs[i].kotNumber + "' and ItemID = '" + itemList[i].itemId + "' ";
try
{
SqlCommand cmd = new SqlCommand(query4, connection);
connection.Open();
cmd.ExecuteNonQuery();
valid = true;
}
catch (Exception ex)
{
valid = false;
}
finally
{
connection.Close();
}
}
}
// update table status after shift
String query5 = "";
if (shifType == "merge")
{
query5 = " update MstTBL set TblStatus='V' where TBLID= '" + oldTableId + "'; ";
}
if (shifType == "shift")
{
query5 = " update MstTBL set TblStatus='V' where TBLID= '" + oldTableId + "'; update MstTBL set TblStatus= 'O' where TBLID='" + newTableId + "'; ";
}
try
{
SqlCommand cmd = new SqlCommand(query5, connection);
connection.Open();
cmd.ExecuteNonQuery();
valid = true;
}
catch (Exception ex)
{
valid = false;
}
finally
{
connection.Close();
}
if (valid)
{
if (shifType == "merge")
{
return(Content(HttpStatusCode.OK, "Table Merged Successfully"));
}
if (shifType == "shift")
{
return(Content(HttpStatusCode.OK, "Table Shifted Successfully"));
}
}
else
{
if (shifType == "merge")
{
return(Content(HttpStatusCode.NotFound, "Error Merging Table"));
}
if (shifType == "shift")
{
return(Content(HttpStatusCode.NotFound, "Error Shifting Table"));
}
}
return(Content(HttpStatusCode.NotFound, "Error, Try again"));
}
public String printAllKOT(String tableId, String print, String reprint, String cancelled, String cap)
{
isprint = print;
isreprint = reprint;
iscancelled = cancelled;
caps = cap;
SqlConnection connection = new SqlConnection(connectionString);
// get print format
String queryFormat = "select * from MstKOTPrintFormat";
SqlCommand cmd01 = new SqlCommand(queryFormat, connection);
try
{
connection.Open();
SqlDataReader reader = cmd01.ExecuteReader();
while (reader.Read())
{
printFormat.companyName = reader.GetString(1);
printFormat.normalPrint = reader.GetString(2);
printFormat.rePrint = reader.GetString(3);
printFormat.cancelledPrint = reader.GetString(4);
printFormat.showCompanyName = reader.GetString(5);
printFormat.lineBreakOnTop = Convert.ToInt32(reader.GetString(6));
printFormat.lineBreakOnBottom = Convert.ToInt32(reader.GetString(7));
printFormat.counterPrint = reader.GetString(8);
printFormat.counterPrintPath = reader.GetString(9);
}
}
catch (Exception ex)
{
}
finally
{
connection.Close();
}
//get table Name
String querytable = "select TBLName from MstTBL where TBLID = '" + tableId + "' ";
try
{
SqlCommand cmd = new SqlCommand(querytable, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
tableName = reader.GetString(0);
}
}
catch (Exception ex)
{
}
finally
{
connection.Close();
}
String query = "";
// get data from HdtKOT
if (print == "Y")
{
query = "select top 1 KOTNO, TblID, WtrID, PAX from TrnHdrKOT where TblID = '" + tableId + "' and Billed ='N' order by KOTNO desc";
}
else
{
query = "select KOTNO, TblID, WtrID, PAX from TrnHdrKOT where TblID = '" + tableId + "' and Billed ='N' order by KOTNO asc";
}
try
{
SqlCommand cmd = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var tempData = new PrintEntity();
printerPrintData.kotNumber = reader.GetInt32(0);
tempData.kotNumber = reader.GetInt32(0);
printerPrintData.tableNumber = reader.GetInt32(1);
printerPrintData.waiterId = reader.GetInt32(2);
printerPrintData.PAX = reader.GetInt32(3);
printerPrintDataList.Add(tempData);
}
}
catch (Exception ex)
{
}
finally
{
connection.Close();
}
// get data from DtlKOT
var PrintList = new List <PrintData>();
for (int i = 0; i < printerPrintDataList.ToArray().Length; i++)
{
String query2 = "select i.ItemName,i.KCATID,d.KOTQty,d.AdnlInst, d.SlNo, i.ITEMORDER, (select k.KCatOrder from MstKCAT as k where i.KCATID = k.KCATID) as kOTOrder from TrnDtlKOT as d, MstItem as i where d.KOTNO = '" + printerPrintDataList[i].kotNumber + "' and d.ItemID = i.ItemID order by kOTOrder, i.ITEMORDER";
try
{
SqlCommand cmd = new SqlCommand(query2, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var printData = new PrintData();
printData.ItemName = reader.GetString(0);
printData.KOTCATID = reader.GetInt32(1);
printData.KOTQuantity = reader.GetDecimal(2);
printData.AdditionalInstructions = reader.GetString(3);
printData.SlNo = reader.GetInt32(4);
PrintList.Add(printData);
}
}
catch (Exception ex)
{
}
finally
{
connection.Close();
printerPrintData.printData = PrintList.ToArray();
}
}
// separte item into array before printing
PrintData[] temp = printerPrintData.printData;
for (int i = 0; i < temp.Length; i++)
{
int tempKOTID = temp[i].KOTCATID;
var printingData = new List <PrintData>();
for (int j = 0; j < temp.Length; j++)
{
var extractData = new PrintData();
if (printerPrintData.printData[j].KOTCATID == tempKOTID && tempKOTID != 0)
{
extractData = printerPrintData.printData[j];
printingData.Add(extractData);
temp[j].KOTCATID = 0;
}
}
if (tempKOTID != 0)
{
printObject.Add(tempKOTID, printingData.ToArray());
}
}
for (int i = 0; i < printObject.Count; i++)
{
try
{
String query3 = "select PrntPath1,PrntCopy, PrntPath2, PrntCopy2, kotTitle, kotTitle2 from MstKCatPrnTablet where KCATID = " + printObject.Keys.ElementAt(i) + "";
SqlCommand cmd = new SqlCommand(query3, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
printPath = reader.GetString(0);
printCopy = reader.GetInt32(1);
printPath2 = reader.GetString(2);
printCopy2 = reader.GetInt32(3);
kotTitle = reader.GetString(4);
kotTitle2 = reader.GetString(5);
}
try
{
isCounterCopy = false;
printingIndex = i;
printerNumber = 1;
printFont = new Font("Century Gothic", 10);
PrintDocument pd = new PrintDocument();
pd.PrintPage += new PrintPageEventHandler(pd_PrintPage);
pd.PrinterSettings.PrinterName = printPath;
// Print the document.
for (int j = 0; j < printCopy; j++)
{
pd.Print();
}
printingIndex = i;
printerNumber = 2;
printFont = new Font("Century Gothic", 10);
PrintDocument pdd = new PrintDocument();
pdd.PrintPage += new PrintPageEventHandler(pd_PrintPage);
pdd.PrinterSettings.PrinterName = printPath2;
// Print the document.
for (int j = 0; j < printCopy2; j++)
{
pdd.Print();
}
}
finally
{
connection.Close();
}
}
catch (Exception ex)
{
}
}
if (printFormat.counterPrint == "Y")
{
isCounterCopy = true;
counterPrint = PrintList.OrderBy(order => order.SlNo).ToList();
PrintDocument pcd = new PrintDocument();
pcd.PrintPage += new PrintPageEventHandler(pd_PrintPage);
pcd.PrinterSettings.PrinterName = printFormat.counterPrintPath;
pcd.Print();
}
return("true");
}
